Maintaining Codelists with Microsoft Excel

Writing raw SDMX is difficult and this is why we created the Maintenance Tool as part of Fusion Registry. It allows for users to create SDMX without knowing exactly how to create SDMX. A user can use the Maintenance Tool to easily create structures sich as Concept Schemes, Data Structures and Codelists and then ask the Registry to output valid SDMX representing that structure. We think that the Maintenance Tool is straight-forward and easy to use.

However sometimes creating very large structures, for example Codelists, can be very time consuming with the Maintenance Tool. A Codelists with thousands of codes would take an extremely long time to input, so what are the alternatives?

Well a user could go and create the raw SDMX XML and import it. Unless you are very familiar with SDMX this will be very time-consuming and liable to be error-prone. The Fusion Registry also supports CSV input for Codelists. This is a bit fiddly as you will need to supply a properties file explaining the Codelist or use the importer within the Maintenance Tool. But now a user can use Microsoft Excel to create or maintain a Codelist.

Since a lot of our users use Microsoft Excel as part of their daily work-lives we looked as to how best we could interface Microsoft Excel and the Registry. We hope we've created a simple method, which basically involves creating a template and then filling it in. It's easiest to demonstrate by example, so let's walk through the steps of creating a new Codelist in the Fusion Registry.

Creating a Codelist in Microsoft Excel

You can simply open a new Worksheet in Microsoft Excel and start typing, but since the Registry will only accept a Codelist conforming to a particular structure, it is easiest to use the "Create Excel Template" option within Fusion Registry. Select the "Excel Template" option that can be found under the settings icon at the top-right of the Fusion Registry and is shown in the following image:

This will display a dialog where you can select the Agency, ID, Name and Version of your codelist:

Then click the "Create" button. Depending on your browser settings you may be prompted to save an Excel Spreadsheet or it may simply be stored on your hard-drive. It is recommended to save the XLSX file to a known location so now you can edit it within Microsoft Excel.

Opening this file will show an Excel spreadsheet showing the Codelist template with the values from the previous dialog filled in. Of course, like any spreadsheet the contents may be modified and of course you can now add codes to your Codelist. The following image shows the Codelist template with 4 codes having been added.

Creating a new code is as simple as going to row 13 and adding the mandatory ID and Name for each code. The description (column C) is entirely optional. If you wish to assign a parent code, use column D.

So as we can see from the example, rows 13 to 16 show 4 items: ITM1, ITM2, ITM3, ITM4. Each has a different name and 2 have descriptions. ITM3 (in row 15) has a parent of ITM1 (shown in Cell 15D ).

You can create as many codes as you like by simply adding them from row 13 onwards. Note that when this file is submitted to the Registry, it will read from row 13 until it encounters a blank line, so in the example shown, if row 15 was blank, then processing would stop at row 15 and row 16 would not be read.

To submit your Codelist to the Registry save your Excel file to a known location. Back in the Registry, you can choose the option, "Upload Structures". Then simply provide the Excel file. If you defined your Codelist correctly, you will have a new Codelist within the Registry. If you made a mistake you will be informed of which cell in the spreadsheet the error occurred.

Maintaining an existing Codelist

You can export a structure in XLSX format, by simply navigating to
the overview page of the Fusion Registry and selecting codelists (see the image below).

You can either download all Codelists or a specific Codelist (the image shows the Codelist CL_AGE being selected) by clicking on the appropriate green download button. The following dialog is displayed:

Ensure that the Download format is XLSX and simply click "Download". Depending on your browser settings you will be asked whether to save or open this file or it may simply download straight to your hard-disk. You can then open this file and edit it as in the instructions above.

To Summarise

Being able to export a Codelist to Microsoft Excel form has a number of benefits. You may wish convert your Codelist into a form your colleagues understand. You may wish to perform large numbers of changes to a single Codelist. You may need to add new codes from another spreadsheet. You may just find that it integrates into your work-flow nicer. Now that the Fusion Registry can do this we hope that more users make use of it.

Currently only Codelists and Structure Sets are supported in XLSX form, but we will be adding support for more structure types in subsequent releases of the Fusion Registry, so please keep watching our website.

If you have any feedback at all, please let us know what you think of this feature. We hope you find it useful but let us know if you have further suggestions or if you think it could be improved in any way.