Export metadata and import and copy with Excel or CSV

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
• Edit the file
• Considerations

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.
   
   • Copy content only
   • Import from file share
   • Import from Google Drive
   • Bulk edit content
2. Select the content you want to copy or modify in the source pane.
3. Click 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 they created.
5. Click under the source list to export your data.
6. Click . 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 .
   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  in step (9), skip this step. Select  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.

Note: When you copy files from one server to another, Windows updates their Created date to the date of the copy.

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.

Since file shares do not permit a folder to contain multiple files with the same name, you should already have a naming convention to help you identify the versions of your file.

In the example below, a version number is appended to the file name, SomeFileName.

When you export metadata for these files, the resulting Excel document will show the original file names as-is in the SourcePath and DestinationPath columns.

To import these files as versions of the same file in SharePoint, follow the steps below:

1. Write your new version numbers in the Version column.
2. Rename the files in the DestinationPath column so they all match.
   • In this example, we remove the version numbers, so the DestinationPath value becomes SomeFileName.txt for all the versions of the document.
3. Save the file.

The resulting Excel file should look like this:

When you import the file for your migration, it will tell the migration tool where to look for versions of the file from the SourcePath values, and the migration tool will recognize that your files are different versions of the same file when it sees that they all have the same file name in the DestinationPath column and different version numbers in the Version column.

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

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.