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.
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.
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’.
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:
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
In the connection properties go to the section
Settings.Writer and complete the following fields:
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.
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.
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.
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.