I am working on a solution with a table that will be regularly updated by importing data from excel spreadsheets. It contains records of every product purchased by members of an organization, with all the relevant specs, including the latest prices. I have script which performs the import and several other tasks that must be done in a particular sequence each month, when they receive updated spreadsheets. The import is set to update records that match the values of two fields (vendor name and item number), then add new records for the incoming records that don't match.
I imported a test record from a spreadsheet with a single row. Then I set up the import script as described above and imported another test, from a spreadsheet with just a few rows, using my import script. This second spreadsheet had three rows which matched the existing data's match field but had different data in other fields, and one new record which did not match the second key field. The results were as I expected: Row 1 of the new spreadsheet update the first record, Rows 2 and 3 updated it again, and Row 4 was added as a new record. In other words, I was left with two records in the database table, which matched Rows 3 and 4 of the last import and represented the most recent data (exactly what the client wanted).
Unfortunately, subsequent imports have not gone as well. They seem to be adding new records for every row in the source spreadsheets, regardless of whether there is a match on the key fields.
Any ideas about what may be going on? Any suggestions for a work around?