How to convert Atlas Taxonomy backup XML files into CSV

Atlas backs up your taxonomy and global navigation (main menu) options each night from the SharePoint Term Store. This setting should be on by default but if you need to switch this on please read this article - How to enable/disable Atlas taxonomy backup service.

You can also export the Atlas taxonomy and global navigation into a CSV file by PowerShell Script if you wanted a more regular export into a CSV.

Access and Download Atlas Daily Term Store Backups 

The Term Store back ups can be found in the Atlas Configuration Site.  This is normally found in one of the following URLs: 

https://<tenant>.sharepoint.com/sites/atlasconfiguration
or
https://<tenant>.sharepoint.com/teams/atlasconfiguration

Once you are in your Atlas Configuration site, go to Settings – Site Contents.

From the Site Contents page click on the Atlas Configuration Files folder. It's usually the second document library in the view.

Then click into the folder Term store backups.

Select the Zip file of the Term Store Backup for the day you wish to restore and click Download. It will have the most recently modified date (column = Modified), but you may need to re-order to find the most recent back up.

This will download a .zip package which you can extract the XML file from. The XML file, although searchable, may not be properly readable and is made for import/export, not for manual readership. Converting this XML file into a CSV will enable you to properly review the term store tags and structure, should regular or ad-hoc reviews be needed

How to convert the downloaded XML into CSV

Disclaimer: Please note that the following instructions are on Microsoft Excel, and therefore we are unable to provide support or solutions if there are issues utilising any of the functions and features mentioned (i.e if your IT department to not allow the Developer Tab to be switched on)

A prerequisite for converting the XML into a CSV is to ensure that Excel has the developer menu option available

• Go to the File tab in Excel.
• Select Options.
• Select Customize Ribbon.
• Under Customize the Ribbon and under Main Tabs, select the Developer check box.

• Click OK. You will now see the 'Developer' tab - shown below.

Access the downloaded XML file

Extract the .xml file from the .zip folder. Right click on the XML, select open with and open with in Notepad or Notepad++ (please do not use Word or WordPad).

XML file will display similar to the below image:

Within this Notepad document there are two rows (one at the beginning and one at the end) containing the text “pnp:Provisioning”. This text needs to be replaced with the text “data-set”

The first change is at the top, and the change looks like the following:

From:

To:

For the second change scroll down to the end of the Notepad document and on the last line replace </pnp:Provisioning> in the same way.

From:

To:

</data-set>

Please ensure there are no character returns at the end of the row

The file now needs to be saved. Select File, Save As, and amend the Encoding button to UTF-8:

Once saved the file is ready to be imported into Excel.

In Excel, on the Developer tab, click on Import (usually in the top-right)

Import the updated XML file

Click ok on the following prompt:

As we would like the data to be in the first cell, click ok to next prompt:

Data is imported:

Data is imported:

The imported files shows various columns all with the title Name:

Column E – Name, denotes the term groups Atlas and Atlas Navigation

Column H - Name 2 denotes the Atlas term sets (Activity, Department, Entity, Information Type Location, Subject etc)

Column K – Name 5 – Atlas Term sets (all ) (*Activities, *Departments, etc)

Column O – Name 9 – Atlas terms (level 2)

Column S – Name 12  – Atlas terms (level 3)

Column W – Name 16 – Atlas terms (level 4)

Column AA – Name 20 – Atlas terms (level 5)

Column AE – Name 24 – Atlas terms (level 6)

Column AI – Name 28 – Atlas terms (level 7)

To view the results in more user friendly way you can manually delete each of the columns which are not needed, or, a pivot table can be used:

Select all the data in the excel workbook so it displays as highlighted

Then on the menu under Insert click on Pivot

The following window opens:

Click OK

Filter the fields by Name:

Drag Name and Name 3 to the filters area:

Drag Name 8 to Rows:

Using the drop-down option in the field Name3 (cell B9- this could differ in your Excel version), select an Atlas term set, for example Activity:

Data illustrating level 2 of the term store is returned:

To see other levels drag add more fields to the rows sections:

To see other term sets amend the field Name3:

There may be otherwise to display the data using the pivot table function depending on what you are looking to achieve.

We hope you have found this article helpful. If you would ever like assistance when following this process please reach out to your Atlas representiative now.