Articles in this section

See more

Export metadata and import and copy with Excel or CSV

Updated
Follow

You can export your migration data as an XSLX or CSV, modify your content's metadata in Excel, and apply your changes during the migration.

The Export metadata and Import and copy features are available for Copy content only, Import from file share, Import from Google Drive, and Bulk edit content.

Index

How-to

  1. Follow the steps from the operation you want to perform below until you get to the point where you can see your content.
  2. Select the content you want to copy or modify in the source pane.
  3. Click Mappings button to apply mappings before you export your file.
  4. Adjust your copy options.
    Note: Your mappings and copy options will affect the metadata in your file. For example, if you map a user, the original user will be replaced by the mapped user in the Created by column for a document he created.
  5. Click Export metadata button under the source list to export your data.
  6. Click Export button. You can save your file as an XLSX or CSV.
  7. Click Open the file to access it and apply your changes as needed. To learn how you can edit the file, see the Edit the file section below.
  8. Save and close the file.
  9. Back in the migration tool, click Import and edit button.
    If you closed the migration tool or are not ready to make the changes, just reaccess the session from Recent sessions.
  10. If you used mceclip3.png in step (9), skip this step. Select Import and edit button to import your data onto the destination.
  11. Once the action is completed, the migration report will show any successes, warnings, or errors. For more information, see Walkthrough - Migration report.

Edit the file

The exported file will contain columns that match your source and destination. For instance, if you have a Contract number column at the destination but not at the source, you will find an empty Contract number column that you can edit for each one of your items in the exported file.

Copy options

If you select the copy option to preserve version history, the file will contain a Version column when you export it.

If you select the copy option to preserve authors and timestamps, the file will contain values in the Created by, Created, Modified by, and Modified columns when you export it.

Filename and folder structure

DestinationPath is relative to the library. If your item is at the root of your library (not in a folder), you will only see the item's name with its extension. You can modify this field to change your folder structure or rename a file during a migration. For example, if you wish to migrate MyItem1.xslx at the root of your source library to a new folder called AllMyItems at the destination, change [MyItem1.xslx] to [AllMyItems/MyItem1.xlsx].

Multiple values

If you have some columns that can contain multiple values, you can separate your values with semi-colons (';').

Content types

If you modify the values in the ContentType column, you will need to have a matching content type at the destination, or you can use a content type mapping.

Managed metadata

You can separate your terms with ">" to associate them with the correct subterms if you have a term hierarchy.

For example, let's say you have Term A and Term B in your term store, and both have a subterm called Subterm 1. To apply Subterm 1 under Term B:

Enter Term B>Subterm 1.

Versions

It is possible to import multiple file versions from a file share into SharePoint.

Windows does not allow two files with the same name in the same folder; you'll have to follow the ShareGate migration tool naming convention to import these files correctly as different versions of the same file in SharePoint. The naming convention is as follows:

Filename.[version number].extension

In this example, you should save version 1.0 of the file SomeFileName.txt as SomeFileName.1.0.txt. You can choose the version numbering to your preference.

In the excel file, the SourcePath column should contain the complete path to the file without the version number. The version column includes the version number on the file system. During the import, the ShareGate migration tool will look for the file using the convention above with the version number (it will look for "SomeFileName.1.0.txt" when looking for version 1 of the file "SomeFileName.txt"). If the migration tool cannot find the file, it will try to find it directly without the version number ("SomeFileName.txt").

Metadata from file and folder names

You might want to use your file and folder names as metadata at the destination.

The migration tool automatically extracts values from the path and file name to make this task easier for you. These values appear at the far right of your file in columns that do not possess a header.

You will find the data in the order below (We edited the example to show the file path with the file and folder names):

  1. The item's name: The filename with an extension, list item, or folder name.
  2. The parent folder's name: The name of the direct folder that contains your document. This field is not populated when the item is located at the library's root.
  3. The names of all the folders in the file path: Each column contains a folder's name in order from the library's root (including the parent folder described at point (2)).

mceclip0.png

Tip: You can use Excel formulas to move the content of these fields to other metadata fields in your file.

Considerations

  • If you modify values in the SourcePath, ID, or Version column, the migration tool will not be able to identify your items correctly during the import.
  • You cannot rename files with Bulk edit content.
  • Dates need to be in en-US (09/12/2020 12:12) or the equivalent in ISO UTC with Z (2020-12-01T16:21:44Z) or the offset (2020-12-01T16:21:44+00:00).
  • The migration tool will not adjust your date and time if you migrate to a different time zone. This is due to an Excel limitation. Dates are stored as sequential serial numbers and do not consider timezone data. For example, the date 2020-04-08 8:21 is stored as 43929.34821, which is not a timezone format.
  • Ensure that the selected source and destination lists and libraries are the same as those used when exporting the Excel file to avoid errors. If you import the file you exported with a different feature; you could lose some data. For example, using a file exported with Bulk edit content in Copy content can make you lose your versions.
  • If you modified the column names in the Excel file or added some new columns, you will have to map those properties.
  • You can select an Excel or a CSV file format. Please see this Microsoft article on CSV support if you have unexpected behaviors when opening the CSV with Excel.

Note: You cannot export the metadata file with PowerShell. It can be created within the application and then imported into your PowerShell script.

Related articles