locked
Create list with data from Excel files located in document library RRS feed

  • Question

  • I use SP 2013 and have a requirement to capture values from all the Excel files in SharePoint. All the files have different file names, but have the same sheets and need to refer to same cell in each file.

    I want to create a SharePoint list which will give me a list of all the Excel files in the first column and list the cell values for each file in the next columns. Whenever a new file is loaded to SP, the list should refresh automatically.

    Please let me know if this is possible, or is there another alternate solution?

    Regards,

    Pallavi NC

    Tuesday, September 29, 2015 12:26 PM

All replies

  • Hi PallaviNC,

    Per my knowledge, there is no OOTB feature will achieve the requirement.

    Meanwhile, we can create an event receiver,when excel uploaded to the library, the event receiver will be triggered , fetch the excel cell value and then add the value to SharePoint list field.

    About how to create an event receiver in SharePoint, you can refer the demo below:

    Create event receiver

    Read excel cell value programmatically:

    How to: Retrieve the values of cells in a spreadsheet document (Open XML SDK)

    Insert value to SharePoint field using C#:

    Add, Update and Delete List Items Programmatically in Sharepoint

    Thanks

    Best Regards


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Friday, October 2, 2015 6:56 AM
  • Hello,

    Could you clarify your needs?

    So you have a library with excel files:

    File1

    File2

    ...

    FileP

    Every file has columns:

    ColA, ColB,...., ColN

    You need to create list with following values:

    File1 ColA-Val-1 ColB-Val-1 ... ColN-Val-1

    File1 ColA-Val-2 ColB-Val-2 ... ColN-Val-2

    ...

    File1 ColA-Val-M ColB-Val-M ... ColN-Val-M

    File2 ColA-Val-1 ColB-Val-1 ... ColN-Val-1

    ...

    FileP ColA-Val-1 ColB-Val-1 ... ColN-Val-1

    ....

    Am I right?


    If my suggestion helped you to solve your problem, please don't forget to mark it as Answer

    Friday, October 2, 2015 7:53 AM
  • Yes, you are absolutely correct. I have a library with number of files. as you mentioned the files have different names, but the sheets within the file are named exactly the same. I need to pick up value from the same cell from that specific sheet and display it as a column in list.

    File1- Sheet"AB"- cell A1 value needs to be displayed in a row 1 column2 in list.

    File2- Sheet"AB"- Cell A1 value needs to be displayed in a row 2 column3 in the same list

    ............ so on all the files data needs to be picked up.

    Regards,

    Pallavi NC

    Tuesday, October 6, 2015 6:01 AM
  • Yes, you are absolutely correct. I have a library with number of files. as you mentioned the files have different names, but the sheets within the file are named exactly the same. I need to pick up value from the same cell from that specific sheet and display it as a column in list.

    File1- Sheet"AB"- cell A1 value needs to be displayed in a row 1 column2 in list.

    File2- Sheet"AB"- Cell A1 value needs to be displayed in a row 2 column3 in the same list

    ............ so on all the files data needs to be picked up.

    Regards,

    Pallavi NC

    Are you sure you are correct with column2 and column3? Looks like mistake and should be File2- Sheet"AB"- Cell A1 value needs to be displayed in a row 2 column2

    If my suggestion helped you to solve your problem, please don't forget to mark it as Answer

    Tuesday, October 6, 2015 9:42 AM
  • Yes, you are absolutely correct. I have a library with number of files. as you mentioned the files have different names, but the sheets within the file are named exactly the same. I need to pick up value from the same cell from that specific sheet and display it as a column in list.

    File1- Sheet"AB"- cell A1 value needs to be displayed in a row 1 column2 in list.

    File2- Sheet"AB"- Cell A1 value needs to be displayed in a row 2 column3 in the same list

    ............ so on all the files data needs to be picked up.

    Regards,

    Pallavi NC

    Are you sure you are correct with column2 and column3? Looks like mistake and should be File2- Sheet"AB"- Cell A1 value needs to be displayed in a row 2 column2

    If my suggestion helped you to solve your problem, please don't forget to mark it as Answer

    Hi IvanBil,

    Yes you are absolutely right, it was error from my end. Please let me know, if a SharePoint list can be created with this functionality.

    Regards,

    Pallavi NC

    Tuesday, October 13, 2015 10:24 AM
  • There's no out of the box functionality in SharePoint like that. As Jerry said, you need to have code to do so, and it depends when you want to trigger this functionality, if once the document is uploaded, then you need event receiver to do so.

    Mohamed Derhalli

    SharePoint Consultant

    Blog: SharePoint Thoughts    Twitter:    LinkedIn:   

    Tuesday, October 13, 2015 12:16 PM
  • For similar task I used Visual Studio workflow (SharePoint 2010 platform) and ClosedXML library to read the values from Excel file in C#. The benefit of workflow over event receiver is you are able to run it many times if needed for specific item without item remove/re-add. Also you may create some settings for the workflow using workflow association form and hence reuse the workflow for different similar tasks.

    In your case you may user either workflow or event receiver. To read Excel values if you have SharePoint Sever and Excel Services running you may use Excel Services API, or the ClosedXML library or any other way. 

    Hope that helps.


    If my suggestion helped you to solve your problem, please don't forget to mark it as Answer

    Tuesday, October 13, 2015 12:25 PM