Access converting text field from a csv into currency

[iojbr]

I'm using Docmd.transfertext to import in a .csv file. For one of my columns in the test csv file, the entries were S1, S2, S3, etc... After I imported the file into a database table, access converted this column of data into a currency field $1.00,$2.00,$3.00, etc... I didn't use a specification file in my docmd.transfertext command b/c I wanted to keep the column names flexible. After the file gets imported in, I have a subroutine to check for the existence of the correct fields that it needs. Is there a way to prevent Access from converting this text field to currency.

 

[SkipVought]

Yes. Use a specification file.

 

[iojbr]

Thanks for your help Skip. Unfortunately I can't use a specification file b/c my company doesn't want Access to throw out an error everytime there are some additional columns in addition to the ones that the database actually uses. As far as I know, this can't be achieved if you use a specification file in the import. I decided to use cell formatting to put quotes around entries in that column for the .csv file, so access will treat it as a text column, and then use a string handling function to delete the quotes once the data is imported into the database.

 

[strongm]

>Unfortunately I can't use a specification file

You can create and execute a specifications file on the fly, though. At least you can in Access 2007 and later.

 

[lameid]

You can also import it not delimited with one column and use code to parse it out.

I like strongm's thought better but I haven't gone down that path... you should be able to read the first couple of lines to make decisions for your own specification.