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.
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.
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.