Update table from data in csv file 1

[SMHSleepy]

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

 

[MajP]

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.

 

[SMHSleepy]

Thanks for the speedy reply. The csv filename changes every time though.
I can code the exact path and filename if that helps.

 

[MajP]

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

 

[SMHSleepy]

Sounding better. Can I update the link to the new file using code or do I have to do it manually through the menu?

 

[SMHSleepy]

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!

 

[MajP]

Should work, unless you have to do some clean up inside the file.

https://support.office.com/en-us/ar...ext-file-d6973101-9547-4315-a8f8-02911b549306

 

[SMHSleepy]

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!