Import Files into SharePoint Document Library
In this post we are going to show you how you can use Data Synchronisation Studio to load files into a SharePoint Document Library. We will also touch on the options available to automate and schedule your projects so then they run when you need them to.
Requirements
Before getting started you will need to ensure that you have the following:
- Windows 10 or Windows Server
- Downloaded & Installed Data Synchronisation Studio
- Have a Document Library created in SharePoint Online
If you do not have Data Synchronisation Studio you can get a free evaluation edition. To test the automation using Ouvvi please contact us to upgrade your trial key.
Connect to your File System
To get started open Data Sync and click onto the Connect Datasource link in the Source Window (Data Source A). This opens the connection window where we need to select the File System provider.
We then need to enter in the path to the folder/s we want to add to our document library. You can either type this in manually or browse for the file by clicking onto the ellipsis (...). Once the path has been added click Connect to connect.
Connect to your SharePoint Document Library
In the Target Window (Data Source B) click onto the Connect Datasource link to open the connection Window. Then locate SharePoint and select the SharePoint Online provider.
As we will need to write to the Modified column in SharePoint we need to use the Custom Azure App OAuth method to connect. Alternatively if legacy authentication is still enabled on your site and you are an admin in SharePoint, you can connect using the legacy authentication method. Another alternative is to create the application registration in SharePoint, we have our full documentation on how to connect using ACS here.
We need to enter in the URL to the SharePoint site that our 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.
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 (\).
Your schema map will now look like this:
Add 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 go to Excel > OpenXML Excel Spreadsheet.
Browse to the file by clicking on the ellipsis (...) in the FileName field.
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 & 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.
Compare & Preview the Results
We can now run the comparison to see the files that need adding to SharePoint. To do this click onto the Compare A>B 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.
By default deletes are disabled but will still show in the results. To enable them go to the target connection properties and set EnableDelete to True.
After checking the data is looking as expected we can move onto the next step, synchronising the data.
Synchronise
The next step is to upload the files to SharePoint. Click onto the Synchronise button in the compare window and then click Start to begin the sync.
Once the sync has run your documents will now be visible to view in SharePoint. We can see here that the relevant folders have been created and the files added.
Make sure to save your Data Sync project so that you can use it again in the future.
Automate & Schedule
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 go to Tools >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
Data Sync Project
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.
If you want to synchronise SharePoint Documents to a File System check out our other blog here.