Importing Data to table

[Murray]

Hi All
I have been asked to import data from a csv file into the table of a
database. The table is called Plant and the fields to be imported are
ID,PDate, PPrice,YTDD,WDU,Asset. The ID should match what is in the table
currently and the other fields should be either empty or overwritten. Is it
possible to do this ? and how?
I would guess to somehow line up the ID and then import the data. Hope
someone can help here as I think I will need a lot of help

Thanks in advance

 

[Jeff Boyce]

Murray

One approach would be to import the data 'as is', then use queries (append,
update) to "line up the IDs".

Regards

Jeff Boyce
Microsoft Office/Accesss MVP

 

[Graham Mandeno]

Hi Murray

The easiest way is to link your CSV file to your database as a linked table.
Then you can write an update query based on the two tables (your Plant table
and the linked text file) with the tables joined by the ID field. Your
update query can update the fields in your Plant table from the fields in
linked table.

To link the text file, go to File > Get external data > Link tables, choose
the file type "Text files", and select the CSV file. Then go through the
steps in the Link Text Wizard to set names and data types for all the
fields. If this is going to be an often-repeated process, then you can
click "Advanced..." and save the options you have chosen as a "link
specification". Then you can use it again later by clicking on
"Advanced..." then "Specs..." and choosing your saved specification.

Even better, you can write some code like this:

DoCmd.TransferText acLinkDelim, _
<name of link specification>, _
<name of linked table>, _
<full path to CVS file>

This will automate the linking of the table for you. In the same code you
could (a) browse for the CSV file and (b) run your update query. Then the
whole job will be done at the click of a button!