Creating SharePoint Folders from a File Directory Structure
This guide will cover how to add documents to a SharePoint Document Library whilst simultaneously adding these files to their corresponding folders by replicating your file system structure.
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.
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 onto Connect Datasource in the Datasource A window, expand the File System folder and select the Files provider. Locate the parent folder that contains the folders and documents you want to add 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 so we need to connect to the SharePoint Document Library as the Target to synchronise the file to. To do this click onto the Connect Datasource link in the Target (datasource B) window and go to the SharePoint section.
Then select SharePoint Online for connecting to a SharePoint Online site or SharePoint ClientAPI to connect to an On-Premise SharePoint site. More details on connecting to SharePoint On-Prem can be found here.
As we will need to write to the Modified column in SharePoint we need to use the Custom Azure App OAuth method to connect. Make sure to configure the permissions in Azure AD so that you can write to the modified column. Alternatively you can create the application registration in SharePoint using ACS. We have our full documentation on how to connect using ACS here.
You need to enter in the URL to the SharePoint site that the Document Library is located. In this example the Document Library is found in a subsite called Test, so our URL will look similar to: https://company.sharepoint.com/Test.
- To connect to the Root site the URL would look similar to:
https://company.sharepoint.com - To connect to a subsite of a subsite the URL would look similar to:
https://company.sharepoint.com/subsite/subsite2
Follow the details from the documentation to connect to SharePoint using your preferred method, and click Authorize Connection to connect.
Now you can select your Document Library from the dropdown list and set the delete behaviour.
We recommend saving the connection to the connection library so that future projects can be quickly set up, to do this click onto Connect and Create Library Connection and enter in a name for the connection. This will add a connection to your SharePoint site/sub-site in the connection library so that you can quickly access all the lists and libraries in the future. You only need to add the connection to each site/subsite once, as you can connect to any of the lists or libraries within that site from the Connection Library window.
Once you have connected, Data Sync will present you with the option to apply a mapping rule which will automatically link the minimum columns you need to perform the sync.
The mapping rule also sets WebFriendlyPaths to true on the source connection so that the local file paths use a forward slash (/) rather than a backslash (\).
If you need to re-run the mapping rule you can click on the Apply Mapping Rule button in the toolbar menu.
Your schema map should now look like this:
Now at this point, Data Sync is configured to import the documents to SharePoint, but does not include any metadata.
Create the Lookup to the Metadata
Most of the time you will also want to update the metadata associated with the files being uploaded. Such as whether the document has been approved, a user friendly title and any comments. If you don't need to decorate your data then you can move onto the next step.
In this example the additional metadata is contained within an excel spreadsheet. However, you might have your metadata in a SQL table or a CSV file for example. You can follow the same process described below just change the connector to match the one you need.
We need to select the column that will link the document to the record in the metadata sheet, in this case it is the FileName.
Click onto the Add Lookup button to open the connection window.
As we are connecting to an Excel file we need to expand the Excel folder and select OpenXML Excel Spreadsheet.
Then browse to the file by clicking on the ellipsis (...) in the FileName field.
Alternatively you can drag and drop the file onto the column in Data Sync from the file system.
This will open the Lookup Configuration Window where we can change the name of the lookup and need to select the target lookup column and data type. Once you are satisfied with the configuration click OK to add the lookup.
This will add the columns from the file to the source column list under FileName. To view these simply expand the tree view.
We are now ready to move onto the next step, adding the columns to the schema map.
Map the Source and Target Columns
To add the columns you want to be included in the synchronisation you can either drag and drop them into the schema window or you can select the checkbox in the source window.
Add the columns you want to be included and map these to their corresponding column in SharePoint. You want to also ensure that the data type is correct, for example approved is a Boolean column so the data type must be set to System.Boolean.
Make sure that a key column has been selected, this is a column that can be used to identify each document and needs to be unique. In this scenario we use FullFileName.
Run the Compare and Sync
We can now run the comparison to see the files that need adding to SharePoint. To do this click onto the Run Compare button in the main toolbar.
We are then presented with the comparison results where we can see any additions, updates or detentions to be made. Updates will highlight changes wherever they exist.
Clicking into the results 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.
In this example we're going to specify the change of location in the metadata document, however you can simply move the file within your file system, if your metadata document does not track the file location (just the file name), and the same result will occur.
Move the File
Move the file in your file directory and make sure to update your metadata document to include this folder change.
Run the Compare
Run the comparison by clicking Run Compare. You will now see the results showing that there is a file to be deleted and a file to be added.
Please note that by default delete is disabled, however for this you will need to enable deletes otherwise the file will be left in folder 3 as well as created in folder 4.
To do this 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
To finish 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.
Automate and Schedule
Now that your project is configured you can save it and then schedule it to run via the Data Sync Run Tool, Windows Task Scheduler, or Ouvvi Automation Server.
You may want to automate your project so that it runs on a regular basis, to do this you can use either the Run Tool or Ouvvi.
Ouvvi gives more options for scheduling, with both time and event based triggers. So you could configure a trigger to start your project whenever a change is detected in your SQL table or SharePoint List. Note that this requires using a modified date time stamp in your SQL table.
Alternatively you can use the Run Tool to schedule the project to run using Windows Task Scheduler.
Run via the Run Tool
The Run Tool is an additional program that comes linked to Data Sync and enables you to build out your data integration jobs.
You can use this to group Data Sync projects that need to run in order and add additional step types such as adding an status report to email you when the project has run and if there were any failures. Each step is run in the order it appears in the list and you can apply conditional rules so that the next step can run if the previous one succeeded or had data changes for example. To find out more please see our Run Tool Documentation.
To open your project in the Run Tool, in your Data Sync project open the Tools menu and select Open in Run Tool.
You can then click onto the green Run button to check it runs as expected. If there are data changes you will be provided with a count of how many items were added updated or deleted
Run via the Command Line
Another option is to Run the Run Tool project or single Data Sync project from the command line. To do this pass the path to the project file like this:
Run Tool Project
%DATASYNCINSTALLPATH%\Simego.DataSync.Run.exe -execute "D:\DSProjects\myproject.dsrun"
Data Sync Project
%DATASYNCINSTALLPATH%\Simego.DataSync.Run.exe -execute "D:\DSProjects\myproject.dsprj"
Automate with Windows Task Scheduler
You can then schedule your Run Tool project to run when you need it to using Windows Task Scheduler. For the full details on how to do this see our Task Scheduler Documentation.
Automate with Ouvvi
An alternative option, with more scheduling capabilities is to use Ouvvi Automation Server. Ouvvi enables you to fully schedule and manage all of your Data Integration Projects, from Data Sync projects to SQL Statements to Powershell Scripts.
It provides full logging and documentation capabilities to fully manage your integration operations.
To find out more see our Ouvvi Documentation or send us an email.
