Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update table from data in csv file 1

Status
Not open for further replies.

SMHSleepy

Technical User
Sep 8, 2009
174
CA
Hello all,

I have a table with client transaction information and a check box to mark whether it has been sent to a different department for processing. Once processed, the other department sends me a csv file with all the transactions it has processed successfully (some don't get processed due to errors). This csv file contains a unique identifier for each transaction. For now, I've been manually checking off each successful transaction but I'd like to be able to do it automatically. Can someone provide code to read the csv file line by line and update my table accordingly? Thank you.

Sleepy
 
You should able to link to the CSV file. At the top select External data tab and "Import/link". Pick text file. You can then run an update query from the linked or imported csv file.
 
Thanks for the speedy reply. The csv filename changes every time though.
I can code the exact path and filename if that helps.
 
If you link the CSV file it appears as a table. You can give the table any name, and use that in a query. When the next file comes in. Update the link to the new file, and keep the same name. That way the query will use the new file without modification
 
Sounding better. Can I update the link to the new file using code or do I have to do it manually through the menu?
 
Actually, I have a better idea. I will write code to rename the csv file so the link doesn't need to be updated. Of course, I will copy the original filename to an archive folder first. Will let you know how it turns out, thanks!
 
Hey MajP, forgot to get back to you earlier but just wanted to let you know it's done and works like a charm. Thanks very much for your help!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top