Updating excel information to a list matching a custom ID column

Question

I'm on SharePoint 2010.

An excel file is uploaded to a document library.This excel file has a custom ID column(alphanumeric) and many more columns.

There is a list which contains many columns along with the same Id column.

This excel sheet gets updated for each ID on a weekly basis and hence the list data also has to be updated for the corresponding ID column

I am looking for options on how to import the data from the excel into the SP list matching the ID columns(ID columns in excel and list should match for updating the data).

What are the ways and how to achieve this?

Answer 1

There are two ways I can think of.

1. Create a timer job. Schedule a SharePoint TimerJob to run daily or for every n number of hours and populate a list which will contain the excel file content.

2. Design a Business Connectivity Service through Visual Studio and let it read data from the excel file every time it is loaded. This does not have any issue from the time perspective as the data will be available real time. But you need to have BCS service application up and running.