Migrate Data from Excel to SharePoint List without Out of Memory Error

Objective: Migrate Data from Excel to SharePoint List.

I found lots of resources online about how to import an excel file into a SharePoint list but what they don’t say is that when you try and import a large amount of records Excel will give you an Out of Memory Error.  Not very handy!  This happens with 32-bit and 64-bit versions of Excel.

Well the way to get around this is to use Access.

Basically in the next steps I will show you how to link Access to your Excel file and SharePoint and use this to transfer the data.

1. The file needs to be an Excel Workbook *.xlsx file, if it isn’t open it and re-save it as this type

2. Now open Microsoft Access and create a blank database

3. Click on the External Data tab, click on New Data Source > From File > Excel

4. Select your Excel File and select the Link to the data source by creating a linked table option

5. Your first row should contain headings, if this is the case select First Row Contains Column Headings and then select Next

6. Press finish to link the Excel file to Access

7. Now to import this data to a SharePoint list go to the External Data Tab > More > SharePoint List

8. Paste in your SharePoint site address and the name of the list you want to create then press OK

9. Wait for the export to occur

10. That is it, now you have migrated all of your Excel data into the SharePoint list.

Hope this helps!

Any questions or comments just leave a message!

Cheers

Leave a Reply

Your email address will not be published. Required fields are marked *