This guide will cover how to add documents to a SharePoint Document Library whilst simultaneously adding these files to their corresponding folders by using calculated columns and a metadata column.
You can add files to pre-existing folders in SharePoint. Follow the same instructions as either of the methods below and so long as the name of the folders match, the file/s will be added to that folder without creating a new one.
If you do not want your files to be added in a folder, then map the FileName to the URL Path of SharePoint and the files will just be added to your document library.
Before we start going through this guide please make sure that you have all the data you require. We assume you have a directory of files, an excel spreadsheet containing the metadata for the files to be added, and a Document Library in SharePoint with the metadata columns created.
Note: We are using an excel spreadsheet for our metadata however you can use other sources such as SQL Tables, XML and CSVs.
To add files to SharePoint but contain them into defined folders, we first need to make sure that our excel metadata document contains a column with the corresponding folder for each file listed.
As you can see from the image below there is a column titled
Folder, the data in that column is the folder each file is to be contained in. The names in this column will determine the names of the folders we will be creating in our SharePoint Document Library.
You can call these anything you like, but in our example they are simply
Folder 3 and
To configure this project we first need to connect to our source. In this example it is the file directory on our machine.
Connect Datasource in the Datasource A window and choose the
File System >
Locate the folder that contains your documents by clicking on the ellipsis (...) in the Path field. Once you have done so, click
Connect to load the data source.
Our Document Library is currently empty, with the columns ready for the metadata to be added.
Now we need to connect the target to our document library in SharePoint. As we are using SharePoint online in this example, choose the SharePoint Online Provider from the connection menu. Enter the site URL and your credentials.
You will then be able to choose the document Library from the drop down menu in the ListName field.
Once you have done this click
Connect & Create Library Connection to save your connection to the connection library. Doing this opens up the connection library window where you are asked to type in a name for your connection. Click
OK once you have typed in a title.
Data Sync's automatic mapping rule system will prompt you to select a rule to apply, press
Apply to apply this rule.
If you need to re-run the mapping rule you can click on the
Apply Mapping Rulebutton in the toolbar menu.
Now at this point, Data Sync is configured to import the documents to SharePoint. However, we are not yet ready to sync as we need to add the meta data and configure the calculated column.
FullFileName column in Data Source A and press the
Add Lookup button in the data source toolbar to create a Lookup.
This will open up the connection window. Find and select the Excel Provider (
OpenXML Excel Spreadsheet) and locate the excel spreadsheet you wish to be used in your lookup by clicking on the ellipsis (...) in the FileName field.
In this case it is our metadata document.
Now we need to configure the lookup. For this example choose FileName as the target column. Select
FileName from the drop down menu so that Data Sync matches based on the filename in the Excel file.
We now need to create our URL Path so that the files can be added to the correct folder. To do this click on the create calculated column
fx button in the data source toolbar. For more information on calculated columns, please see our documentation here.
In this example we will be using the following expression:
CONCAT(Lookup1_Folder, "/", FileName)
This will create a new column that will join the
folder name with a
/ followed by the
Make sure to add a title that will be displayed as the column title.
Now we need to configure the schema map. Add all the metadata columns you require and map these to the corresponding columns from SharePoint.
Drag and drop the calculated column onto
FullFileName to replace this with the calculated column value. This will be mapped to URL Path from SharePoint, and is what will determine the folders and file locations.
You can see what our schema map looks like once it is complete here.
We can now run the comparison by clicking
Compare A -> B.
We can see that we have 5 new records to be added to our document library.
Synchronise and then
Start to begin the sync.
Once this has successfully run, if we open our document library we can see that the folders have all been created and the files with their meta data added to the corresponding folders.
If you need to change a file's location after synchronising to your document library, then follow these steps to make sure it all runs smoothly.
For this example we are going to move the document file4.docx from Folder 3 to Folder 4. The screen-capture below shows the file currently residing in Folder 3 in SharePoint.
Update your metadata document to include the folder change.
Run the comparison by clicking
Compare A -> B. You will now see the results showing that there is a file to be deleted and a file to be added.
By default delete is disabled, however if you do wish to enable deletes go to the connection properties in your target column and scroll to
EnableDelete change this from
Now go back to your comparison results and check the checkbox next to the delete.
Run the Sync to apply the changes.
If we now go to our document library we can see that the file has been deleted from Folder 3 and is now visible in Folder 4.
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.