csv import data

[nemi1983]

Dear all,

I am looking to a friendly solution to import a csv file (csv files are imported randomly, many times per month) with 5 columns into a new table in access that has more columns that the file I am importing).

Problem description:
1. two columns have meta data information and need to be split in pieces. Not all the pieces of the metadata are used (just 2 codes are relevant).
2. from the metadata information, I extract country code, look it up in the table and add a column to the table that data is imported to.
3. there are also some import data conditions added to the import file.

A piece of code that AI have hard time understanding (and have not programmed in access yes):
DoCmd.TransferText acImportDelim, "CSV_comments_specification", "tbl (A01) MIR comments", _
FileToImport, True, ""

 

[smedvid]

Since your ultimate Target table of the import process has more columns than the actual import text file; I would recommend using a temporary staging table that matches the import file exactly. Then you can use TransferText to bring the data into the temp area. Once in the temp area you can perform some QA and other data integrity checks to ensure that the data is good before loading it into the final destination table.

If your import files are variable and do not have a consistent format; that is much mroe difficult to manage. It appears your example is using an Import Specficiation which may fit the bill in that regard. But, it is best if the import file process follows a pre-defined set of fields...

I hope this was helpful... Good Luck.



Steve Medvid
IT Consultant & Web Master

 

[nemi1983]

Thank you for the reply. It took some time, but I understood the concept and the import method (as well as the statement that I posted).

My next problem is execution of the desired properties. Have been looking for code samples and more or less put the pieces together. However, when data arrives as a .csv the field "comment" contains commas in the text itself. That is why the "CSV_comments_specification" is included in the import.

What have I improvised is this statement:
SQLCreateTempTable = "CREATE TABLE tblTempPartSearch (check_name TEXT(22), series_name TEXT(35), " & _
" Reference_date TEXT(15), Comments TEXT(9), field TEXT(10))"

But the field Comments is the one containing comma in the record and I do not think I could bypass it with the "specification".

Next question will be data manipulation of a temp table or at least see a concrete example from which I can build foreword.