Import and export from SharePoint to Excel: Tips and tricks

By now, if you haven’t seen Sharegate’s import & export from SharePoint to Excel feature, get ready to be impressed. Not only is it profitable to use Excel as a bulk metadata editor but it also brings some cool integrations with Windows Explorer. Let’s take a look at a few tips which can sometimes be life savers.

1. Format your Excel Data as a Table

 This might be an obvious trick for most power users but for anyone who isn’t aware of this Excel feature, it can be of great help for handling large quantities of data. 

Once you have your data presented as a formatted table, filtering and sorting becomes a cinch.  For example, if I want to change all the “Pending” documents created by Simon, I can use the table headers as follows:

Now I can easily change the “Approval Status” column to “Approved”, import it using Sharegate and mass approve documents!

2. Create a Template Excel File

When copying to your destination library, you can leave your source documents unselected and Export an empty Excel file.  Why would you want to do this?

1. If you plan on copying a large selection of documents, you don’t need to wait to Sharegate to process all your files to create the Excel file.  Create your empty template, and enter the file paths and metadata yourself!  Save time and have full control of your metadata.
2. Copy from multiple local sources at the same time.  If your documents are dispersed in a network drive, it’s now easy to find and import them.  Let’s see an example of this in the following tip.

3. Master your Windows Explorer

Say for example that you wish to copy specific documents from your file system to a SharePoint library.  To be more specific, say I wanted to copy all the Word documents which I have authored.  To add some complexity, these files are scattered throughout my drive.

Using the Windows Search, I can refine my search to:

authors:=”Yohan Belval” type:=”Microsoft Word Document”

I’m then presented with my search results. 

Now, how do I get files into my SharePoint library? Easy! First, copy all the file paths with this magic command: 
Shift + Right-Click –> Copy as Path

You can then paste the results in the “Path” column of a template Excel file created by Sharegate and modify the metadata to your liking:

Once I import this back into Sharegate, I can see all my files are now in my Office 365 SharePoint library:

But wait… notice that I also have the “Sharegate – Large” folder?  What if I wanted all my files to be at exported to the same level? Sharegate can do that.  Let’s see how to flatten the folder structure.

4. Flatten your Folder Structure

When copying files through the Sharegate UI or using the SharePoint import from Excel feature, users get the chance to create their own custom property template:

When creating a custom template, there is a very handy option that allows you to flatten your folder hierarchy:

Once this has been checked, I can save my template and proceed to reimport my files with the Excel feature. Let’s see the new results in my SharePoint library:

Just the way I wanted it.  Flattened and fully tagged with all my metadata.

5. Leave your Version Number as Text Format

You may spot a slight complain made by Excel about your “Version” column saying that the data should be formatted as a number.

Makes sense you say?  Well, not quite.   The version number should always stay as text because if it’s formatted as a number, it will truncate trailing zeros.  For example, typing version “1.10” will automatically format it as version “1.1”.  Just a simple trick that can save you some headaches. This is how you can use the export from SharePoint to Excel feature with Sharegate.