VBA - Importing a CSV file into Access table errors

[leroy_brown]

Hello all!!!
I have a form in Access which imports a CSV file into a table as follows:

DoCmd.TransferText acImportDelim, , tblName, InputDir & ImportFile, True

I am having a very strange problem though. There are certain fields in the table of type text but when the imported data appears to be numeric (the first line has data like '34' but subsequent are like '34b') the textual data is not imported. A "TYPE CONVERSION FAILURE" occurs.

Does anyone have any ideas how to overcome this. The data is from large extracts so resorting to begin with text records manually is not really an option!!

Any help is appreciated.

Cheers

[Muddy]

works fine for me. what field types are you using in your mdb?

[leroy_brown]

They are text which really baffles me. When i rearrange the order of records in the csv file to a text looking record appears first the import works correctly. It is just when a numeric data occurs first it gets confused. I was thinking about writing a function that somehow cast all the fileds in the CSV that are supposed to be text (according to the table) into strings. My VBA is VERY rusty so don't have a clue how i'd do that???

Any suggestions?

[Muddy]

yah, I just duplicated the error. not sure how to fix that ... sorry

(memo field gives same error as text field in case you were wondering)

[ashish ojha]

stuck with the same problem ... Any solution on this ?....urgent

[Hack]

Access VBA question moved to Office Development

[salvelinus]

Use an Import Specification (under Advanced when you do a manual import). Save that, then use the Import Specification name as a parameter in your code. As far as I know, you first have to do the manual import to create the spec, but can then use it in code. I think offhand it comes in the empty space in your string. You can set data types when you create it.
Another hackish option would be to swap lines in the csv file so that 34b, the text value, was on the first line. Presumably it would then treat 34 as text. The order of records in a table doesn't matter.

DoCmd.TransferText acImportDelim, "MyImportSpec", tblName, InputDir & ImportFile, True

[Al42]

I've noticed the same problem in connecting to an .xls file with ODBC - the data type of the column is determined by the data in the first row. It looks like a bug in the way Microsoft connects to Excel files. (And there's no way to use an import specification in an ODBC connection that I know of.)

[salvelinus]

Or, open the csv file in Excel and convert the column to Text format.

[NeoDan]

Hi there...

I'm a novice at VBA compared to some of the other users onboard but if you are having problems with datatype would it be easy to parse the data through set variables of the datatype required? eg.

source data X is a number

import it into variable 'a' (which has been dim'ed as a string)

then pass 'a' into the table?

Not sure if this will help...

NeoDan