How do you bulk upload data to a SharePoint Online list? I am sure for many businesses this is a common task. For the last few weeks, I had a very effective albeit manual solution:
- Have a list of the data I wish to upload in an Excel file, structured to match my columns on my SharePoint list, in a table
- Using a custom “bulk upload” view on my list, go to Quick Edit mode
- Make sure I am using Internet Explorer (Quick Edit mode does not allow pasting when using Edge; not tried other non-MS browsers)
- Copy data – Select All from the Excel Table
- Paste into the SharePoint list
- Wait whilst the data is ‘committed’ to the list
As you can see, it’s step intensive and manual. I have spent a good few hours on how to semi-automate the process: by this I mean, there is still a requirement to capture the data you want to upload, but the actual uploading itself is now automated.
You will need the following:
- Visual Studio (I am using 2012) with the Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2012 installed
- The SharePoint List Source and Destination service, which can be installed from CodePlex
- For connecting to SharePoint Online, you will need to also install the SSIS SharePoint List Adapter for SharePoint Online, courtesy of ioi.solutions
- An infinite amount of patience
(the above order is the order I installed everything)
First things first – the documentation around this is sparse and disparate to say the least. The purpose of this blog, in part, is to address this to spare people the hair-tearing and corridor pacing that I went through 🙂
SharePoint List In Visual Studio – A WORD OF CAUTION!!
If you search for “ssis data toolbox not visible vs 2012” you will find a ton of results. FORGET THAT! The SharePoint List options WILL ONLY show up once you add a Data Flow Task and then edit that Data Flow Task. Under Common, you should see four options listed – if not, right-click on Common | Refresh Toolbox
Adding SharePoint Online As A Destination
Nick has done a great job of referencing this here
My recommendation for getting:
- SiteListName – Under List Information
- SiteListViewName – Under Views
- SiteURL – Everything BEFORE /Lists/YOURLISTNAME/AllItems.aspx
Is to go to the list you wish to update, go to list settings and all the pertinent information will be there.
From here on in, it is the same as any other source/destination within an SSIS package. You will need to map and tweak to your needs, but overall this is pretty simple.
Drop Down Lists In Your Destination? A Word Of Caution!
If you have drop-down lists in your destination, you will likely hit an error along the lines of:
You will have to implement a workaround – my solution will be to use a SharePoint Workflow to update that field as I have a need for it.
Excel – Spurious Error Messages
Like all of these things, you will probably encounter some meaningless error messages….
If you are using Excel as a source (which I am), you may get an error about truncation. The workaround for this is as follows:
- Go to Advanced Editor > Input and Output Properties
- Select Output from the Inputs and Outputs box > Output Columns
- Change the Data Type Properties – in my case I chose Unicode string
Additionally, I knew that my source data would not get truncated, so using the regular Editor, go to Error output and for Truncation I chose Ignore Failure.
You will see a warning, but you can safely ignore it.
As of 1/18/18 – this is an ongoing project I am working on – as I implement new functionality, I will update this post accordingly.