Importing CSV files to Access 2007 - 15+ digit issue!

Hi,

Every month I receive multiple csv files that I need to import into an access database. Once I receive them, I have to run a macro which changes some detail in the csv files and then saves and closes it.

My problem is that in one of the columns there are numbers which are longer than 15 digits which are perfectly formatted when I receive the CSV's but after running the macro (which opens, changes some numbers and then closes and saves as a csv) the digits are formatted with 0's after the 5th digit! ANNOYING!

I want to know the best way to avoid this. Any ideas???

NB: I need to keep the files in CSV as access likes to chuck out lots of data when importing a normal xls file.

Originally Posted by Kaper

Not seeing the macro it's not easy to give exact cure for the problem, but diagnosis stage is easy. Numbers, if not explicite given type, are generally stored as double precision floating point numbers. This means that only some 15-16 meaningful digits can be stored. And this is what you probably face.

Yes, it is annoying for instance in case of bank account numbers - they are often pretty long (in Poland - 26 digits).

First general idea of treatment: try to keep data as text type (string). If numbers manipulation is needed, convert to number for the time of te operation only this part of data, but store the result back as text.

That shall do the trick.

Thanks for the reply.

I was told when you change it to text and import it into access some of the queries in the database don't work and I get "expression match type" errors when running some of the queries.