Importing data to SharePoint list

Question

I have a spreadsheet that I would like to import to a SharePoint list daily. Data changes on it everyday and I need to be able to reflect that. Once an item is added to the SharePoint list, there are additional fields that are changed as well.

When I import the next days data, I get duplicate records and can't just delete them because there may be changes that were made to both records. I had looked into using MERGE directly with the SQL DB, but found it is not supported.

Does any one have any recommendations on how I can merge records via Access when I do the daily import of data?

Answer 1

You'll probably need to define a BCS connection. With that, you can tell SharePoint what the key of each item is, to avoid duplicates. BCS connections can be set up to WCF services, SQL databases and/or custom .NET code.

Advantage of BCS is that the data is realtime, so no need for importing it daily.