Bulk Maintenance of Metadata Values using Excel Import / Export – Fusion Data Mapper
For bulk changes, a dataset’s metadata mappings can be exported to Excel, the spreadsheet modified offline to make the necessary changes, and imported.
Maintaining Metadata Values using Excel – Prerequisites
- One or more mapped datasets have been added (see Section Add a Mapped Dataset).
- For each dataset, one or more series have been added (see Section Add Series to a Dataset).
Maintaining Metadata Values using Excel – Required Roles and Privileges
To maintain metadata values on a series, users must be a member of the Agency that owns the SDMX Structure Set and Dataflow Map that represents the dataset, or a member of a parent Agency if a hierarchical agency structure is in place.
Maintaining Metadata Values using Excel – Procedures
Using the Fusion Data Mapper:
Exporting Existing Mappings to Excel
Choose the dataset and select the Manage Metadata page from the left-hand menu.
Use the column filters to select the series to export – only the series shown in the table will be included in the spreadsheet.
Use the 
button to start the export and save the Excel file when prompted.
Modifying Exported Mappings
The Excel spreadsheet follows the layout shown in the Figure 4 example.
Series can be added by adding rows, and the metadata values set and modified.
Note that the Excel import will only remove series from a dataset when the “Full Replace” option is used. See below for more information on difference between the “Append” and “Full Replace” options. For most purposes, the interactive series removal function detailed in [[Removing Series from a Dataset – Fusion Data Mapper|Removing Series from a Dataset] is recommended unless the number of series to be removed is large.
The following rules should be observed when modifying exported Excel metadata sheets:
- Columns must not be added to or deleted from the spreadsheet. The Excel import function expects to find a column for each Dimension and Attribute in the dataset’s DSD.
- Every row must have the code of a “registered series” in the SERIES_CODE column. See Registering a Series for information on how to register a series.
- Each series must appear only once.
- The Row A header must not be modified
- For coded Dimensions or Attributes, only valid codes must be used – valid codes are those in the relevant Codelist for the Dimension or Attribute
Blank metadata value cells are allowed and behave as follows when the spreadsheet is imported:
- For coded Dimensions and coded mandatory Attributes, the default value will be used.
- For un-coded mandatory Attributes, the metadata value with be set to an empty string or zero depending on whether the representation is string or numeric.
- For un-coded non-mandatory Attributes, no mapping will be created meaning that the Attribute will not appear in a data query result.
Importing Mappings from Excel
Choose the dataset and select the Manage Metadata page from the left-hand menu.
The 
button starts the import.
In the Excel Import dialogue, choose Browse to search for the Excel file to import. Drag-and-drop can also be used by dragging a file from Windows Explorer onto the Browse box.
Two Import Modes are provided: Append and Full Replace.
“Append” Behaviour:
• New series that appear in the Excel spreadsheet but not in the existing dataset are added with the values specified for each dimension. Blank cells follow the rules set out above. • For existing series, the values are updated to those specified in the spreadsheet. Blank cells follow the rules set out above meaning that existing values will be reset to the default if the cell is blank. • Existing series that do not appear in the spreadsheet will not be altered.
“Full Replace” Behaviour:
- The mappings for the dataset will be replaced exactly with those in the spreadsheet.
Specifically:
- The values for existing series will be changed to those in the spreadsheet with blank cells following the rules set out above.
- Existing series that do not appear in the spreadsheet will be removed.
- New series that appear in the spreadsheet but not in the dataset will be added with the values specified for each dimension. Blank cells follow the rules set out above.
Take care when using Full Replace to avoid unintentionally removing series from the dataset – any series not explicitly specified in the spreadsheet will be removed.
Use the 
button to import the Excel spreadsheet.
If the import is successful, the dataset metadata display will show the revised mappings.
The Excel mapping import function is an “atomic transaction” meaning that the system guarantees either all of the mappings in the spreadsheet will be imported or none at all. If an import error occurs, no changes will be made to the dataset.
Any import errors are displayed in an error window – an example is shown below.
Troubleshooting Excel Import
| Error | Solution |
|---|---|
| The supplied file is not recognised as a valid xlsx file. | The file must be an Excel 2007+ spreadsheet |
| The code specified ‘<code-in-error>’ does not exist in the codelist | The spreadsheet contains an invalid code in a coded dimension or attribute. An invalid code is one that cannot be found in the Codelist for the component in the dataset’s DSD.
Correct the code in the spreadsheet or add the code to the relevant Codelist |
| Excel file contains unknown components | The spreadsheet contains one or more columns with headers that do not match components in the dataset’s DSD.
Remove the invalid column(s) from the spreadsheet, or correct the name of the column in the header. If you need to change the dimensionality of the dataset, refer to Section Changing the Dimensionality of a Dataset for guidance. |
| The series ‘<series-code-in-error>’ is not mappable as the value does not appear in the SERIES_CODE codelist | The spreadsheet contains one or more unregistered series. The series codes in the SERIES_CODE column must be in the CL_SERIES_CODE or equivalent codelist.
Correct the invalid series codes in the spreadsheet; or Remove the invalid series rows; or Register the series. See section Registering a Series for more guidance on how to register series |
