Import CSV Type Conversion Failure

[Dale]

I am importing about 14 csv files into tables using the docmd.transfertext.
The code is like this.

docmd.transfertext acimportdelim, tablename, filename, -1

I have some columns that are mainly numbers but sometimes include text. I
have the field defined in the table to import into as text but i get a type
conversion on the records that are text. What am I doing wrong?

An example is a postal code field. I have the table set up with the field as
a text field. Most rows have a US zip code in them but a couple have Canadian
zip codes like T6H 4N7. The row gets put into an importerror table with the
type conversion failure.

 

[Piet Linden]

I am importing about 14 csv files into tables using the docmd.transfertext.
The code is like this.

docmd.transfertext acimportdelim, tablename, filename, -1

I have some columns that are mainly numbers but sometimes include text. I
have the field defined in the table to import into as text but i get a type
conversion on the records that are text. What am I doing wrong?

An example is a postal code field. I have the table set up with the fieldas
a text field. Most rows have a US zip code in them but a couple have Canadian
zip codes like T6H 4N7. The row gets put into an importerror table with the
type conversion failure.

Try creating and using an Import Specification. That should fix it.

 

[Dale]

Piet,

I have tried to create a import specification by using the import wizard and
then using it in my code but it still is not working. I can see the spec when
I go back into the import wizard but I must be doing something wrong. Any
ideas?

Dale

 

[Andy Hull]

Hi

If you go into the Import Specification and click on the Advanced... button
you will see the fields listed and the data types that the Import
Specification is expecting.

These data types will not always default to the data types you have in your
table.

If a field is mostly numeric but some (further down the file) contain
letters you will probably find that the Import Specification chooses a
numeric data type.

If this is the case with your postal code field then change the data type
within the Import Specification to be Text, then save the Import
Specification and try the import again.

hth

Andy Hull