CSV import error only with code

[gg]

When I import a csv file into an existing table manually ( file-import etc.)
all is well. When I import the same file into the same table using code, I
get a type conversion error for one field. This field is typically contains
numbers, but some numbers in some fields are preceeded by alpha characters.
USA 12345 for example. Any field with the preceeding alphas gets rejected
and shows as an import error. The recieving table field is text with zero
length allowed. Code for importing is DoCmd.TransferText acImportDelim,
"ROSspec", "99js", mypath & "Myfile.csv", True, "". Using AC 2000 with
W2000
Help-driving me nuts-thanks in advance

 

[Piet Linden]

When I import a csv file into an existing table manually ( file-import etc.)
all is well.  When I import the same file into the same table using code, I
get a type conversion error for one field.  This field is typically contains
numbers, but some numbers in some fields are preceeded by alpha characters.  
USA 12345 for example.  Any field with the preceeding alphas gets rejected
and  shows as an import error.  The recieving table field is text with zero
length allowed.  Code for importing is  DoCmd.TransferText acImportDelim,
"ROSspec", "99js", mypath & "Myfile.csv", True, "".    Using AC 2000 with
W2000
 Help-driving me nuts-thanks in advance  

Do an import manually once, and create an import specification and
save it. Then use the ImportSpec with the TransferText command, and
it should work fine. The problem is that Access looks at the first ?
rows in the table and uses that to determine the field type, and it's
guessing wrong. So if you "tell it" what kind of data is in the field
(by using the Import Spec), it will work fine every time.

 

[Chris O'C via AccessMonster.com]

Or gg can modify the field's data type in the existing spec named ROSspec.
Change it from a number data type to text data type.

Chris

When I import a csv file into an existing table manually ( file-import etc.)
all is well.  When I import the same file into the same table using code, I

[quoted text clipped - 6 lines]

W2000
 Help-driving me nuts-thanks in advance  

Do an import manually once, and create an import specification and
save it. Then use the ImportSpec with the TransferText command, and
it should work fine. The problem is that Access looks at the first ?
rows in the table and uses that to determine the field type, and it's
guessing wrong. So if you "tell it" what kind of data is in the field
(by using the Import Spec), it will work fine every time.

 

[gg]

I had created a spec as you and Piet suggested (that was the ROSspec in my
transfer text statement). After reading your two replies I cancelled the old
spec and just made a new one the same way-now it works-very mysterious.
Thanks to both of you. I needed to know that I was doing the right thing

Or gg can modify the field's data type in the existing spec named ROSspec.
Change it from a number data type to text data type.

Chris

When I import a csv file into an existing table manually ( file-import etc.)
all is well. When I import the same file into the same table using code, I

[quoted text clipped - 6 lines]

W2000
Help-driving me nuts-thanks in advance

Do an import manually once, and create an import specification and
save it. Then use the ImportSpec with the TransferText command, and
it should work fine. The problem is that Access looks at the first ?
rows in the table and uses that to determine the field type, and it's
guessing wrong. So if you "tell it" what kind of data is in the field
(by using the Import Spec), it will work fine every time.