none
Importing Excel 2007 data into a SharePoint list

    Question

  •  

    I have 20,000 items in Excel spreadsheet that I would like to import into SharePoint list. Can I automate this process? If yes, how? What is the correct way to automate this process?

     

    Can I use Open Office XML to read the excel file and import it 'on the server'? Does licensing model of OOXML allow us to use it on the server? I am ignorant about the open office xml.

     

    I tried editing the data in "Edit in datasheet view" but it says that the selected cell is read only. I am the owner of the site, list and content does not require approval.

     

    Thanks!

    Friday, May 02, 2008 9:07 PM

Answers

  • As Paul Galvin suggested, importing 20,000 items to SPList means that you are possibly exceeding the software boundary of SharePoint. SharePoint is not designed to handle that many items. MS whitepapers suggest that you can store upto 5 million records in a SPList or document library with proper folder structures, but these whitepapers are based on the assumption that you're NEVER going to use any custom SPQuery by treating SPList as a transactional database.

    I think the poor performance and other unstability issues related to the SharePoint software boundary is a major problem with SharePoint architecture.

    There are couple of options.
    1. Import 20,000 items anyways to a single SPList. If you use SPWeb.ProcessBatchData API, you can import 20,000 items programmatically in less than a minute. If you're to use the SPList only through SharePoint UI, the performance would be ok. If you're to use this more like a transactional database, it's where you will start to have problems.

    2. Breakdown items to small chunks and categorize by folders. The folder solution would not resolve the problem entirely but at least is a good option.

    3. Import items to a custom database instead of SharePoint list, and use a DataView webpart or BDC to gain the access to the custom database. This is more work, but at least this option is more suited to handling 20,000 items.



    Saturday, May 03, 2008 4:02 PM

All replies

  • The datatype of those cells is Multiple lines of text with support for enhanced text. Only these cells are not allowing me to update and the error pops up saying "Cell is read only"

     

    Friday, May 02, 2008 9:13 PM
  • I'm assuming that the plain vanilla excel import function doesn't work well.

     

    You can definitely automate it and 20k items seems to justify the effort.  Although, 20k items in one sharepoint list might be a problem.  There's a general guideline of keeping items down to 2k per list, though it's just a guideline/recommendation.

     

    Depending on how often you need to do the import, a relatively quick way would be to write a console application designed to run onthe server.  It uses the sharepoint object model and .net to read data from the excel doc and create list items.

     

    Another non-coding approach might be a two stage process where you get the data into access and then sync the access data with a sharepoint list.  I don't know if this will be better, but you could probably figure that out one way or the other in an hour or so.

     

     

     

    Saturday, May 03, 2008 12:47 PM
  • As Paul Galvin suggested, importing 20,000 items to SPList means that you are possibly exceeding the software boundary of SharePoint. SharePoint is not designed to handle that many items. MS whitepapers suggest that you can store upto 5 million records in a SPList or document library with proper folder structures, but these whitepapers are based on the assumption that you're NEVER going to use any custom SPQuery by treating SPList as a transactional database.

    I think the poor performance and other unstability issues related to the SharePoint software boundary is a major problem with SharePoint architecture.

    There are couple of options.
    1. Import 20,000 items anyways to a single SPList. If you use SPWeb.ProcessBatchData API, you can import 20,000 items programmatically in less than a minute. If you're to use the SPList only through SharePoint UI, the performance would be ok. If you're to use this more like a transactional database, it's where you will start to have problems.

    2. Breakdown items to small chunks and categorize by folders. The folder solution would not resolve the problem entirely but at least is a good option.

    3. Import items to a custom database instead of SharePoint list, and use a DataView webpart or BDC to gain the access to the custom database. This is more work, but at least this option is more suited to handling 20,000 items.



    Saturday, May 03, 2008 4:02 PM