Error in importing .txt files

[merry_fay]

Hi,

I've set up my vba to import some .txt files on the click of a button.
That's all fine, except, while importing I get the message:

Microsoft Access was not able to import all the data etc etc
The contents of fields in x records was deleted etc

When I look at these tables in excel, I can't see any reason why these particular records are causing a problem. They're all numbers, none are negative, they're not the longest numbers & as far as I can see there's no random spaces before or after them (certainly not when I open them in excel).
Has anyone else had the same problem? Are there any suggestions for how I can get around it? I need these numbers to be imported for the monthly calculations & outputs!

Thanks
merry_fay

 

[Trevor G]

You say there are no extra spaces you can see in Excel have you looked at using Len as a formula to see the amount of characters per cell in each column, if a problem then in Excel you can look to use a Global Trim command or data to text options.

Then all you would need to do is transfer the text file into Excel first then into Access.

 

[merry_fay]

I've done the len & they're all the length the number of characters purport them to be.

I'm trying to keep manual intervention down to a minimum, hence importing them as .txt (there's about 20 different txt files each month plus another 10 already in excel, all from different sources & systems!!).
Converting them to excel would be a last ditch solution if there's no better options...
Thanks for the suggestion

 

[spikepl]

Separator problems? If "," is the decimal separator, it can sometimes cause problems in that a real number like 1,2 then is perceived as two numbers.

Also, Access normally dumps some errors in a table - have you had a look?

How do you do the import? Is there an import specification?

Which fields fail to import? Can you give an example of the input - one record, and show what HAS been accepted?

Are you importing to a table defined with columns as strings or numbers of the appropriate type?

 

[merry_fay]

Here's one of the inputs:

Node,Total KB,Current KB
apprsltsbdc1025,3531951674,0 -here Total
bkserv7,957424516,564475044
bkserv8,984112250,494073715
cd4mlive,3690862764,0 -here Total

colprdmis,3032896594,0 -here Total

colprdops,4864941638,0 -here Total

devhris0,12884992,0
livetibrtweb01,237948,0
seqx40,20621846,20621846
sqlrsltsbdc1015,11162211794,7955848525 -here Total & Current

sqlrsltsbdc1038,685629876,613067208
starsltsbdc1003,6051840,0
wadrsltsbdc1003,1253376,0
{bkserv8}/nsr/reports$

I've noted which one's aren't importing -mostly in the Total GB field, but also one in the Current GB field.

Yes, I'm using a specification & the fields in the diestination table are: text, number, number.


Thanks

 

[spikepl]

Number but what type ? The things seems to be blowing up the LONG limit of 2,147,483,647 so you may have to use some other type such as Currency or Double : http://bytes.com/topic/access/insights/601315-vba-data-types

 

[merry_fay]

Number but what type ? The things seems to be blowing up the LONG limit of 2,147,483,647 so you may have to use some other type such as Currency or Double : http://bytes.com/topic/access/insights/601315-vba-data-types

Haha, yep, it was a long integer, changed to double & all is fine

Thankyou!!!!!!!!!!!

I'll try & remember that long limit too (or at least that there is one!! )