Importing .csv file

[nirapreet]

Hi all,
I am trying to upload a .csv file in a table using the tRansfertext method in vba. I have atext field having alphanumeric values. The upload is excluding the some values and generating a new error table. How do I get rid of this error.

Do I have to use a specification name. I have new .csv file to be uploaded every week, How do I work on the spec file ?

Any answers please..
Thanks

Nira.

 

[FoFa]

What is/are the errors?

 

[nirapreet]

The upload is missing some fields
and the error table says
type conversion err

Thanks

Nira

 

[FoFa]

Ok, type conversion error means it is trying to do some thing like import text into a number field, or text into a date field, something like that. This is more a data issue than anything else. You can usually work around this if cleaning the data is not realistic, by defining an import table with generic text columns (usually, but each import is unique) and then using queries or VBA to do data conversion and load it to a "proper" table.

 

[nirapreet]

The field in question is of type text.
What's happening is that the in one field the value being uploaded is Alphanumeric.
The rows whcih are numeric value for this field , works fine & are being uploade corect in this text field. But when the value is alphabetical, it causess the above said errors only to the rows which has alphabetical data. The calue in the fields are ignored and not uploaded.

 

[FoFa]

Are you creating the table during the import, or importing to an existing table?
I ask because it sounds like it is thinking it is trying to import text data into a numeric field. If you are creating the table during the import process, it may be trying to setup the column as numeric.

 

[hmicky]

Problem importing csv into sql-created table

Hallo Nirapreet and Fofa, and all others who read this,

I have the same problem exactly, only I create the table with an sql statement before I import the data. No matter which type of data I predefine in the alphanumeric column (text, varchar, char, general...), it just wont import the data and leave the field empty.

Part of my code looks like this:


Tablename = "Lim" ' varying table names
sqlCreate = "CREATE TABLE " & Tablename & "today" & "(A Long,B Text,C Text,D Text);"
DoCmd.RunSQL sqlCreate
DoCmd.TransferText acImportDelim, , _
Tablename & "today", CSVFileName, True


where my csv file looks something like this:

A; B; C; D;;
1000009; Kat; C1; mcs;;
1000009; Fld; K500;mcs;
1000009; KuGru; 8; mcs;

Problem does not occur while importing other csv files or in other columns of the current csv , besides column C. Though defined as text, only the numeric expressions are imported. So my table looks the almost the same, only that in column C, I get empty entries instead of "C1" and K500" and only the numeric expressions like "8" are imported properly


If you have an idea it would be great.

 

[renenger]

Conversion Error

I have an existing table in Access. I am using the DoCmd.TransferText to import it. It seemed to be working ok. I added a DoCmd.OpenQuery before it that will delete all the data from the table. Then the DoCmd.TransferText runs. However, I get a type conversion error on two of my fields. In the Access table the two fields are setup as text fields. So I am not sure why the data conversion error is popping up.

I have the frmMenu set to open on Startup. The code below is loaded on the form.

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenQuery "qryClearTblCSVImport"
DoCmd.TransferText acImportDelim, "", "tblCSVload", "C:\DATABASE\FRONTIERIMPORT.CSV", False

End Sub

 

[FoFa]

Without using an import specification, Access assumes the data type not from the table as one would think, but from the importing data. So if you had a number or date (as it thinks) without having your text delimiter around it, it could think there was a conversion problem. I would create an import spec and use that to clear that error up.