Importing csv file into existing table

[Dreamcatcher]

I have an access table (tblSales) recording sales records. Each week i want to import new sales via a csv file into the tblSales table (automaically appending the records to the end of the table)

What is the easiest way of achieving this??

Tried importing using the Get External Data > Import route but dont know what to put in the first column of my csv file (which will be the recordID in the tblSales table). Tried leaving it blank (,,) or 0 (,0,) but no luck.
Obviously user wont easily know the next record id so is there an easy way of achieving this?

Regards
JD

 

[Atomic Shrimp]

It might be easiest to handle it in stages:

1. Import the file into a temporary table structure that matches the file's native layout
2. Use an append query to copy the contents of the temporary table into their rightfiul places in tblSales
3. Empty the temporary table

 

[Dreamcatcher]

Thanks.

Is there some standard code, examples etc to append the data and empty the temp table?

JD

 

[nharrison]

You can do what Mike said, and then package the entire thing as a macro, which can be used to do this once a week.

Attached is a sample database I did you can take a look at. Be sure and modify the import source directory in the macro to wherever you extracted the file to.

 

[Dreamcatcher]

Running 2003 so could you please send me the example again saved as mdb.

Many Many thanks

JD

 

[RandiR]

For this task, do look at the sample scripts posted at http://www.biterscripting.com/samples_data.html . You can run these scripts from the task scheduler periodically, so you wouldn't have to do anything manually.

Randi