Bulk Upload Documents into SharePoint from Excel

Lets take a scenario that most of you come across on a frequent basis. You have a directory of documents that need to be added to SharePoint. With Data Sync there are a few ways to do this, and the one we are going to cover today makes use of a simple Excel Spreadsheet. Your other options can be seen in our documentation here.

The example shown below uploads only a small sample of documents, you can use this method to upload many thousands of documents.

Before you begin

The Document Library

You want to make sure you have a Document Library ready in SharePoint to take your documents and define any metadata columns you want to be included.

The Spreadsheet

In your Excel spreadsheet you need to specify the name of the document and any metadata you want to be uploaded alongside it. In our example we have a range of ten documents with different modified dates and different approval status’.

Documents in SubFolders

If your documents are contained within folders in your directory, you want to include the sub-directory folder name with the file name for example Folder1\doc1.docx. This will ensure that the document can be found, and your directory structure is replicated in the Document Library. An example spreadsheet can be seen here:

The Project

Open Data Sync and connect to your spreadsheet as the Source choose the Open XML provider from the list, then locate your spreadsheet containing your metadata and click Connect.

In the connection properties go to the section Settings.Writer and complete the following fields:

• BlobBasePath : Enter in the file path to your directory of documents in our example this is C:\Users\Rebecca\Documents\Demo Documents.
• BlobFileName : Choose from the drop down the column that returns the name of the file. In this example it is the column FileName.

Connect to your SharePoint Document Library as the Target by choosing the SharePoint Online or SharePoint ClientAPI (if using an on-prem version of SharePoint) provider from the list.

Schema Mapping

Now map your source columns to your target columns. You need to map one column to the URLPath in SharePoint. For this example we are adding a Title Property to each document, setting whether it is approved or not and a modified date. The full schema mapping of the example can be seen below:

As of version 3.0.1276 you can map a different filename to the one you have specified in the BlobFileName field, and the BlobFileName column does not need to be included in the schema mapping. So if you want your files to go to different folders or have a different name, map the other Filename column (such as TargetFileName in the example spreadsheet) to the URL path in the schema map.

Run the Compare and Sync

Now run the comparison by clicking Compare A -> B and you can preview the results to make sure that they are appearing as expected.

Once you are happy synchronise the results. You can now go to the Document Library in SharePoint and you should see all the documents added with their corresponding metadata.

Results with Folders

If you had defined folders for the documents to be in your results in SharePoint should be like this:

Clicking inside a folder shows the documents.