Creating SharePoint Folders from Metadata Document

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.

Preparation

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.

Create Folders from a Metadata Column.

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 1, Folder 2, Folder 3 and Folder 4.

Connect to the File Directory

To configure this project we first need to connect to our source. In this example it is the file directory on our machine.

Click on Connect Datasource in the Datasource A window and choose the File System > Files provider. 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.

Connect to the Document Library

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

Create the Lookup to the Metadata

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

Add a Calculated Column

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

Make sure to add a title that will be displayed as the column title.

Configure the Schema Map

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.

Run the Compare and Sync

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.

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

Moving a File to another Folder

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 the Metadata

Update your metadata document to include the folder change.

Run the Compare

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 False to True. Now go back to your comparison results and check the checkbox next to the delete.

Run the Sync

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.