Using transfertext to import to csv's to Access. Data Type conversion problems

AngusT
AngusT used Ask the Experts™
on
I have a problem where I have to import various CSV's into tables automatically from a folder into temporary table in an Access database. The name and numbers can vary from day to day, and different types of files have different column formates, but if they are located in a particular folder they get imported. I've been using transfertext quite successfully...for most.

Among other information, these records contain credit card numbers. Some of the csvs have these credit card numbers in quotes, and others don't have the quotes on those cards. At this stage I don't have any control over the format over the csv that arrives to be imported. Where the credit card field has quotes, the import goes fine as the temporary table has the data type of the credit card as a field. However, when credit card field in the CSV doesn't have quotes, the number is imported as a double. and in the table displayed as something along the lines of 5.31300008663285E+15, when it should be treated as a string of 5313000086632851.

The ultimate goal is to take the file, import it into a temporary table, update some data into the temporary table, and re-export it as a csv (using transfertext again), deleting the temporary table after. Everything works well, except that the credit card number is converted to something like the above.

Really urgent so attaching maximum points. Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
MIS Liason
Most Valuable Expert 2012
Commented:
Basically the credit card numbers in the csv file should be Text (with quotes around them)
Then they will be treated as Text and not be converted to scientific notation.

However, since this is out of your control, you may need to create two export specifications.
On the file that has the text in quotes you probably don't have to do anything special except make sure the wizard sets the CC field as Text.
But for the file with no quotes you will have to select the CC field and explicitly set the datatype as "Text', ...then save the import Spec.
Doing that works just fine for me.

JeffCoachman

Author

Commented:
I needed to use a spec on import since some credit cards are 16 characters and automatically converted them to a double in scientific notation, but I was able to use the same spec on both import and export, and it worked a treat for me.

Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial