How to import a file to update records

[Guest]

Here's what I want to do. I have a file (CSV) to import but what I need it
to do check each record and field while the file is importing. If there are
any changes in the record would like for the information to update the
record. Plus if there are any new records to be added to add them in and
finally if the record isn’t in the import file to move that record to another
table. Can this be done and if so how?

Thanks

 

[Guest]

You might be able to do this with come queries, but I think the best method
would be VBA code. I would import the csv file into an existing table, then
write some code to read each record and do the compares you list in your
post, and append or update as required.

 

[Guest]

Thanks Klatuu,

Now where do I found out about obtain info on VBA?

 

[Guest]

If you do not know how to code in VBA (Visual Basic for Applications), then
you need someone who does. What you want to do requires moderate to advanced
programming skills.

 

[Guest]

Thank Klatuu,
For pointing me in the right directions. I had some coding and probably can
figure it with a little playing around. At least I was on the right track.

 

[Guest]

Here is some logic flow that might help the process:
To match records between your import table and your current table, you will
need a unique field or key to match them on.

First, create a table to import you csv file into
Then when you are ready to run your process
Delete the data in the import table
Import the csv file into the import table

open your Current Table
open your Import Table
open your NoMatch Table (This is the one to move rows to that are not in the
import table)

Now some air code: (air code = totally off the top of my head with not
syntax checking or testing)

Do while Not rstImport.Eof
See if there is a match in rstCurrent
If match, then check each field and update if not equal
else add a new record
rstImport.Movenext
Loop

rstCurrent.MoveFirst
do while not rstCurrent.eof
See if there is a match in rstImport
if no match, then
rstNoMatch.Addnew
copy fields from rstCurrent
update
end if
rstCurrent.MoveNext
Loop

Close it all down and you're done

 

[Guest]

Thanks again Klatuu I'll give it a twirl.

 

[Guest]

Good Luck, Post back if you have any questions

Thanks again Klatuu I'll give it a twirl.