How to use Data Sync to upload documents to a Document Library from a single Excel Spreadsheet

This guide will walk you through how to import documents and their corresponding metadata into a new SharePoint document library from an Excel spreadsheet. This guide assumes that you have a file directory containing your documents to be added to SharePoint and the metadata is contained in an excel spreadsheet.

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.

Can't find the answers you're looking for? Contact our support team and we'll personally get back to you as quickly as we can.