Importing Multi-line CSV's into MS Access Table

[udamnit]

Hi All,

I have been trying to import multi-line .CSV's into an access table. (I have attached sample .CSV data)

Looking at the attached .CSV a single record consists of a 200 and 300 line (ie rows 2 & 3 = 1 record - comma de-limited files), as per the attached file there are 3 records - first and last lines are not needed. I need to be able to code the loading of multiple files each day from a directory where the files will be located.

I have had some success with an add-on called import wizard v9, however the cost is rather high and i think there might be a simple enuff alternative ? any suggestions...

Cheers

 

[gemma-the-husky]

its relatively simple in access, but it needs code

you would have to import the whole file using a spec. If there is a constant start to the line, then use that, but otherwise everything would have to come in as a single column. (looking at your data, you could use column A)

you could then write a select query to distinguish the 200/300 char lines (even if its just based on the char count

you would then have to process each query a line at a time with a sub/function iterating the record set, and manually process the record using split, or other functions, and sql insert/update statements.

once done, you could use this on successive imports.

============
having inspected the data, there may be some complications - you would need to know that line 3 related to line 2, in order to build up the foreign keys in access - and it doesnt appear there is anything in a 300 line to specifically identify the 200 line (other than the sequence) - if that is the case, then you may have to process the whole recordset in a single pass, so that the realtionship is apparent.

still doable - just a different method.