You can export your data to Excel, then modify it in the Excel file and import it back with the changes applied.
For more information, see Microsoft's article Introduction to managed metadata.
- Importing multiple versions of a file?
- Editing the file
- Select the action you want to perform from the Migration menu or from Explorer. If you are returning to a Recent session to run an Import and copy, skip to step (11).
- Select your source, and click Connect.
- Select your target.
- Click Next.
- Select your destination, and click Connect.
- Select your target.
- Click Next.
Note: With Bulk edit content you have to select your target and then click on Bulk edit content in Explorer to access the Bulk edit content window.
- Select from the source list to export your data.
- You can create or select a template for the export, or keep the default template, which will copy all the values from the source while preserving version history, authors, and timestamps. The mappings and the available templates are the same as the ones used for the copy.
- Click . You can save the file format as XLSX or CSV. For more information, see the note on exports below.
- Click Open the file to access it and apply your changes as needed.
- Save and close the file.
- Back on the app, click . If you closed the app or are not ready to make the changes, just access the session again from Recent sessions.
- If you used Import and Edit in step 10, skip this step. Select to import your data onto the destination.
- You can create or select a template for the import, or keep the default template, which will copy all the values from the source while preserving version history, authors, and timestamps. For more information on mappings, see below.
- Once the action is completed, the migration report will show any successes, warnings, or errors. For more information, see Walkthrough - Migration report.
Please note the following when using Export metadata.
- You can choose the location where you want to save the Excel file.
- You can select an Excel or a CSV file format. If you have unexpected behaviors when opening the CSV with Excel, please see this Microsoft article on CSV support.
- 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 the Copy content tool can make you lose your versions.
Note: The file cannot be exported with PowerShell. It can be created within the application and then imported in a PowerShell script.
Please note the following when using Import and copy.
- Make sure that the selected source and destination lists and libraries are the same as the ones you used when exporting the Excel file.
- If you modified the column names in the Excel file or added some new columns, you will have to map those properties.
- If you modified the values in the Content Type column in the Excel file, you might have to map those content types.
Importing multiple versions of a file?
It is possible to import multiple versions of a file from the file system to SharePoint.
As Windows doesn't allow two files with the same name in the same folder, you'll have to follow the ShareGate Desktop naming convention to import these files correctly as different versions of the same file in SharePoint. The naming convention is as follows:
For example, version 1.0 of the file "SomeFileName.txt" should be saved as "SomeFileName.1.0.txt". The version numbering can be done 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 contains the version number to look for on the file system. During the import, ShareGate Desktop 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 file cannot be found this way, ShareGate Desktop will try to find the file directly without the version number ("SomeFileName.txt").
Editing the file
The Excel file created by the export contains all the editable columns that the destination list or library contains. It also contains an identifier column, which is the DestinationPath in the case of documents or the ID in the case of list items (the DestinationPath also appears for list items), as well as a SourcePath column which indicates the location of the source document. If the option to preserve the version history was selected, the file will contain a Version column. Finally, if the option to preserve authors and timestamps was selected, the file will contain the columns Created by, Created, Modified by, and Modified.
The SourcePath, ID, and Version columns should not be modified, as these are used to identify the items when importing. They must match what exists in the source list or library so that information such as permissions, attachments, or file content can be pulled from the source. You cannot add new items or files that do not exist in your source list or library.
The DestinationPath column will be filled during the export with the path that the document would have in the target library if you had simply copied the file and folder structure as-is. However, you can modify it to change the folder structure or rename files during the migration. The file paths entered in this column must always be relative to the library. For example, if there is a library at http://mySite/myLibrary and you want MyFile.docx to be uploaded in a folder called MyFolder, you would need to enter "MyFolder/MyFile.docx" and not "http://mySite/myLibrary/MyFolder/MyFile.docx".
- You cannot rename a file with Excel if you are using Bulk edit.
- 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).
All the other values can be modified. If you have some columns that can contain multiple values, you can separate those values with semi-colons (';'). If you modify the values in the ContentType column, please note that you must either have a matching content type in your destination library or set a mapping when you will import the Excel file back.
The column names "SourcePath", "DestinationPath", "ID", "Version", "ContentType", "Author", "Created", "Editor", "Modified", "ApprovalStatus", "ApprovalComments" and "CheckinComments" should not be modified as these represent system columns that are used for special purposes. All the other column names can be modified and you can also add new columns, but they will have to be mapped later when importing in order to be used while copying to the destination.
When you are modifying a cell value you can use formulas to quickly populate a column with useful metadata. In order to make this task easier for you, ShareGate Desktop will extract some values from the path and the file name. These values are located after all the other columns and don't have any header.
They represent (in order):
Parent folder name
All folders in the file path (one folder per column)
Here is an example on how to use this metadata:
If you have a term hierarchy, you can separate your terms with > to associate them with the correct subterms.
For example, let's say you have Term A and Term B in your term store, and both have a sub term called Subterm 1. To apply Subterm 1 under Term B:
Enter Term B>Subterm 1.
When importing metadata from an Excel CSV or Excel spreadsheet, your date and time will not be adjusted if you are migrating to a different timezone.
This is due to a limitation with Excel data storage. 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.