Docmd.TransferText

[azmirnordin]

Hi all Guru's

I'm using
DoCmd.TransferText acImportDelim, , "tblRawAssay", "D:\IMPORT CSV FILE\RS20110309aE.csv", True to import data from .csv file into access table. I create one column with data type Text to stored number and string. I can only import number but not string(Text/letters). I got an error "Type conversion Failure". What data type to use to store both number and text?

Example of Data to be import in column named ValueStored
20.8
RS
0.5
TD

Please Help!
regards
azmirnordin

 

[John Big Booty]

I suspect that you will need to look Data Type of the destination fields in the table you are importing the data to. Ensure that they all match the type of data you are pushing into them, in this case Text.

 

[azmirnordin]

Hi John

Thanks for the quick reply John, really appreciate that!
The data type for the column is already set to Text.
I try to import data into that particular table by right clicking on the table name and choose Import->TextFile and point to the csv file location. Guest what, it works with no errors at all! I wonder why I cant get the same result by using VBA when user click on ImportFile button on my form? Any idea?

regards,
azmirnordin

I suspect that you will need to look Data Type of the destination fields in the table you are importing the data to. Ensure that they all match the type of data you are pushing into them, in this case Text.

 

[John Big Booty]

You have set the Has Field Names property to True, I'm not sure if this might be causing you problems

Is your table tblRawAssay the final destination for this data or simply a temporary holding point for the data, so you can manipulate a cleanse it prior to transferring it to it's final destination?

 

[azmirnordin]

Hi John ,

Thanks again for replying.
I did try your suggestion.
(1) Remove the True for the Has Field Names , run the code and it produced Runtime Error :2391 which is Field 'F1' doesnt exist in destination table, I need the field name in CSV file to match the destination column in destination Table so that's why I set the value True

(2)tblRawAssay table is actually my TEMPORARY table because I need to change any Letters imported to certain fixed number before all this data goes to the Final table. I will manipulate that using case statement later.

any more ideas?
regards,
azmirnordin

You have set the Has Field Names property to True, I'm not sure if this might be causing you problems

Is your table tblRawAssay the final destination for this data or simply a temporary holding point for the data, so you can manipulate a cleanse it prior to transferring it to it's final destination?

 

[John Big Booty]

I'm not sure if the attached DB and Excel file will help Copy both files to your desktop or other common location, and have a look at the code behind the Continue button on the form FRM_Import, that might give you some pointers.

One thing that comes to mind as I write this, is that my Excel file is initially a CSV when I download it. Now it's been a long time since I wrote this DB, and I can vaguely recall that I had some problems dealing with the raw CSV file, as downloaded, and found that it was easier to save that file as an Excel file and deal with the Excel file instead.

 

[azmirnordin]

Hi John

Thanks for allowing me to use your db.
I did some testing on the import.xls file by adding a vessel with voyage "ND".
It does add the new record in. I need sometimes to study your code
to clearly understand how u do it. I will get back to you soon once my
problem solve..

Thanks again John

regards,
azmirnordin

I'm not sure if the attached DB and Excel file will help Copy both files to your desktop or other common location, and have a look at the code behind the Continue button on the form FRM_Import, that might give you some pointers.

One thing that comes to mind as I write this, is that my Excel file is initially a CSV when I download it. Now it's been a long time since I wrote this DB, and I can vaguely recall that I had some problems dealing with the raw CSV file, as downloaded, and found that it was easier to save that file as an Excel file and deal with the Excel file instead.

 

[azmirnordin]

Hi John

Its me again.
I use your example.
At first the code works well for me.
But then I discovered It only works to 1 column only.
Unfortunately I got 2 columns with data type Text, only one column get all text and numeric data stored together, the other column still produced the "Data Type Conversion Failure " .

So I look closely how the manual import steps worked really well for my .csv file. Then I found that in Docmd.Transfertext method there is one parameter call SpecificationName .So I create a specification named ImportCsv and there include that specification name in the Docmd.TransferText method in my vba coding. Thank god it works .

Thanks for your help mate, Really appreciate that

regards,
azmirnordin

I'm not sure if the attached DB and Excel file will help Copy both files to your desktop or other common location, and have a look at the code behind the Continue button on the form FRM_Import, that might give you some pointers.

One thing that comes to mind as I write this, is that my Excel file is initially a CSV when I download it. Now it's been a long time since I wrote this DB, and I can vaguely recall that I had some problems dealing with the raw CSV file, as downloaded, and found that it was easier to save that file as an Excel file and deal with the Excel file instead.