Importing data from an (awkward!) CSV text file

[retro]

I've had a look through the many topics on text importing, but can't find anything specific to my problem.

One of our suppliers has started offering their catalogue as a CSV file via e-mail. For now, I am saving the file to my computer, and wanting to import it into a database.

the table is a bit awkward, as it has "useless" data in the first field. Here is the beginning of one as an example:

VIP Computer Centre Ltd.  Trade Price List. 22 August 2006 3:37 PM
ORDER CODE,PROD GROUP,DESCRIPTION,WTY,BOXED IN,1 OFF,5 OFF,20 OFF,UNIT

7719-C,BAREBONE SYSTEMS,JW MINIQ 430AV INSTANT-ON B/B,0,1,50,49.75,49.25,EACH

I imported the data via the Get External Data feature into a new table. This worked fine. I then changed a couple of values, and re-imported the data, this time selecting to import it to the new table. It came up with an error, saying 295 records were lost due to key violations. I then realised that this only appended the data on the end of the table, which isn't what I want.

Here is what I am trying to accomplish:

1. The field names are created from the titles in row 2 of the CSV file
2. The table is updated by importing the latest CSV file, so that any price alterations are changed, and any new products added (but identical data is ignored). If one field is needed as a constant, this would be the ORDER CODE field
3. Field 2 (PROD GROUP) becomes a combo list box (probably based on another table - so the table contains the categories, e.g. BAREBONE SYSTEMS, and the text from the CSV file is converted to the appropriate ID number)

Is any of this possible? If so, how?

Thanks in advance for any advice you can give!

 

[FoFa]

Truthfully, I would setup a work table, wipe it and import the the new data each time into the work table (reading headers and everything into the work table).
Then have how ever many queries to clean up the data (remove empty rows, the header rows, etc). than run an update query to update values already there (from the work table into your live data). And an append query to add new records (from the work table into your live data).

 

[retro]

Thanks for the reply.

I was hoping to avoid having to wipe it each time, as our supplier constantly removes products that we still have in stock! Still, I'm sure you're right in that it is the best way, so I'll have to think of a compromise!

Cheers!

 

[RCurtin]

It doesn't matter about deleting everything in your work table as its just a temporary table.
-You import from the CSV file into temporary work table
-clean it up as FoFa says
-run an APPEND query to add new products to your main table where you store product info
-run an UPDATE query to modify the records that are different in the new CSV file.

You won't lose any info on old products as you are deleting the data in the temp work table not the data in your main tables