How to update an open Excel in C# using OLEDB?

How can I update a master CSV file with a daily update file using Excel?

• We have a vendor that sends us a CSV file at the start of every month. It contains a download of all parts in their system with columns and columns of every piece of info you can imagine about the parts. We use that file as our starting point, and then each day there is an email that comes from the vendor that has and pricing changes or new parts that were added during the previous day. The files are identical as far as formatting and having the same columns of data. For years, we had someone manually updating each line one at a time. After I found out about it , I set up an Access database where I saved the monthly file, and then each day the user could import the daily file and export the new data to the network for everyone to view. Lately, we've been having a lot of problems with import errors. I can't determine if the vendor is doing something different or if it is user error. People here aren't the most familiar with Access. Is it possible to do these type of work in Excel by chance? The people here are more familiar with Excel, and I like it better than Access. Basically, there is a file (the monthly) with 20,000 lines each line having a unique part number. What I want, is something that looks at the daily file, and when it finds a match it updates the 40 columns of info for that part.

• Answer:

  The major problem is getting a list of unique part numbers from the combined lists: http://www.get-digital-help.com/2009/06/29/merge-two-columns-into-one-list-in-excel/ http://www.techrepublic.com/blog/msoffice/two-ways-to-return-a-unique-list-in-excel/1080 The Import External Data command can help retrieve the two lists. Once you have the list, you can get the remaining columns in a way that checks the update list first, then pulls missing values from the monthly (or updated monthly). VLOOKUP() or INDEX(MATCH()) methods will work, perhaps along with OFFSET() to reduce the number of calculations. So long as you can avoid doing any cut/pasting, or at least make it a template, the workbook should be pretty durable. Another method that came to mind was to take advantage of VLOOKUP's tendency to pick only the first match: You open the update list, append the monthly list, and use VLOOKUP with a list of unique part numbers to pull from the combined list. The older records would be ignored as they are lower on the list. Good luck!