In SP 2010 there is an option to create a list from importing an excel sheet. I have a requirement to automate this process but I was not sure how to start or even if my proposed solution is the correct fit.
I have a sql database which dumps 2 excel sheets into a file location every night, with graded sales stock data and quantities. The data in the excel sheets changes every night as new stock is made ready for purchase. This excel sheet is manually copied and edited into an html editor and copied into an html page with the data for display on a website. This data allows users to see what graded stock is available for purchase. The main requirement is to automate this process with minimal human effort and store the table of information in a list in a SharePoint 2010 Intranet site.The fields of the excel sheet are item number, description, quantity and price.
My initial thoughts are to have the 2 files dumped into 2 seperate document libraries and then have workflows initiate to ‘export’ the contents into 2 seperate lists (One for each file) which will be written over everytime a new file is added to the libraries. there are no workflow actions out of the box to export an excel sheet to a sharepoint list. The reason I would like to use a list to display the data is so that I can create an order form in InfoPath to allow users to order items from the list of stock, which would involve the quantities being reduced everytime and order is submitted.
Perhaps there is a way to directly dump the excel file into a SharePoint list and negate the need for a document library inbetween?
I am using SharePoint Designer for my workflows due to time constraints and My Intranet is SharePoint 2010, I posted this to a Microsoft forum and this was my reply:
SharePoint Designer workflow can’t work here, because SharePoint workflow can start manually or a change occurs in SharePoint list/library automatically, but Excel data changes can’t start the workflow. So instead, we can create a custom timer job and set it start daily to import data from the excel file.
Here is a blog about creating custom timer job.
Then we need to read data from excel file and import them to SharePoint list in the custom timer job. We can use the code from codeplex.
I will be blogging about my experience with working with custom timer jobs to fulfil this request!