Here is my assignment today: I need to import files from my machine to SharePoint. I usually use the Sharegate PowerShell module to run regular imports, and the graphical user interface when I want to make changes to the structure and the metadata along the way. However, since version 4.7, Sharegate now allows me to import based on an Excel spreadsheet, so I don’t need to use the GUI to do so anymore.
In order to nail it the first time, I decided to generate my first Excel spreadsheet directly from the GUI. This way, I have a model that I can use in the future, and also have a base to work with.
Generate an Excel Spreadsheet directly from Sharegate’s GUI
The format is very straight forward, it features the source and destination paths, version number, content type and the different metadata columns from the destination.
Replace your Folder Structure with Metadata directly through Excel
The great thing is that I can leverage the different features available in Excel to modify the structure of the content. For instance, I can use find and replace to change the content type or use the concatenate function in order to add a folder to my file paths (in order to import my files into a new folder). It is a level of granularity that up to now was not available through the PowerShell module. Even better, you can mix the Sharegate commandlets and the SharePoint Management Shell ones to script even bigger changes (script the creation of columns in your list for instance).
Back to my assignment, my goal is to import files, but I would like to take advantage of the new functionality so I will replace the folder structure for metadata fields. My destination document library already had the columns needed in place (Company, Division and Sector) so I was able to use the information already in the Excel file to manage the replacement, I now have the Excel all set.
Import to SharePoint Using PowerShell
Once the Excel file is to my liking, I can simply use one of the walk-through examples available in the Sharegate PowerShell documentation. Here is my script:
After the migration is done, the report will be generated and exported to my desktop so I can review it. On top of that, I can go back to my Excel file to make additional changes and rerun the migration whenever needed, in a few seconds.
