Discussion

[Beta] Generating version 2.02 IATI XML with CSV2IATI

Steven Flower
Steven Flower • 21 January 2016
12 comments

Hi everyone

Thanks to Ben Webb - IATI Secretariat and [~379] I’ve recently been able to generate version 2.02 IATI data out of the CSV2IATI tool

A word of caution - this isnt a straightforward process -so please only try/test this on non-production data models to begin with. Ideally - create a brand new model in CSV2IATI to test it out for yourself.

The key to this project is this JSON code. What this does is generate the “mapping” model in CSV2IATI according to the 2.02 schema. As people might be aware, CSV2IATI currently outputs to version 1.03 by default. It is actually possible to construct a model in line with any version of the schema - but this requires some careful work, planning and clicking. Hence, this code helps generate the template according to 2.02, giving you the foundation to build on.

To do this, try the following steps:

  1. Create a new model in CSV2IATI, with the source data CSV file you want to convert
  2. When in this model, click the link on the right hand side “Show JSON” - this is the current model in JSON format
  3. In a new tab, open the 2.02 JSON file - and copy the JSON (tip: click on the Raw button to the same code without the surrounding web page)
  4. Go back to CSV2IATI, select the existing code, and then paste in the 2.02 version - this replaces the underlying code for your model
  5. Whilst the JSON window is still open, find the “Save Model” button click it. This can be tricky in some browsers/interfaces - but it’s essential (I found) to save the model whilst the JSON is still open
  6. Once saved, you can close the JSON and moved to the model and mapping.
  7. In the mapping page you should see the whole of the activity standard elements and attributes, to map you source file again.
  8. If you really dont need a particular element you cna delete it with the small trashcan icon for each element/attribute.
  9. Tip: As you do so, dont forget to save!
  10. When you’ve finished, you can move directly to the convert page, and convert to XML.

The resultant file is structured according to 2.02 , but with two small issues. To fix these, you’ll then need to download the XML and edit it:

  1. The version attribute of iati-activities will still be 1.03. Manually edit this to 2.02
  2. The reporting-org element will not be formatted according to 2.02, if you have provided an Organisation Name in the first page of CSV2IATI - eg:

<reporting-org ref="12345" type="60">Org Name</reporting-org>

Find and replace the existing code with something like:

<reporting-org ref="12345" type="60"><narrative>Org Name</narrative></reporting-org>

As highlighted, this is quite involved, but does work if you make some time for it. Would be great to hear from others who try it

Comments (12)

Steven Flower
Steven Flower • 8 years ago

Update: There’s a slight snag with this in that when the CSV is converted to XML, the elements are not presented in the exact order required by the IATI 2.02 schema. We’ll look at a fix and update…

(Rolf Kleef might be keen to know this)

Pelle Aardema
Pelle Aardema • 8 years ago

Hi Steven, great stuff!
I was just looking into possible tools to support all the upcoming Dutch publishers.

I’ll give this a try in the coming week. Keep you posted.

Rolf Kleef
Rolf Kleef • 8 years ago

Ok, I’ve tried to adapt the model for an existing test dataset, and managed to get a “2.02-ish” file.

Too bad this mandatory ordering of elements was introduced in version 2.01, just to make life harder for the producers

I haven’t completely re-arranged everything to pass the test, just one activity, and that works.

A few observations:

  • Under budget there is an attribute status that I don’t see in the standard.
  • activity-status suggests to map a text value as well as the status code, but any text value will break validation.
  • In my sample set (for 1.03), it would need some additional conversions from text to numeric codes, something to factor in.

Will be good to try with a real-life example, just need a simpler way to re-arrange elements in the proper order…

Ben Webb - IATI Secretariat
Ben Webb - IATI Secretariat • 8 years ago

I’ve created a Python script to order an XML file according to the schema: https://github.com/OpenDataServices/iati-utils/blob/master/sort_iati.py

Usage on Ubuntu:

# get the script
git clone https://github.com/OpenDataServices/iati-utils.git
cd iati-utils
# install lxml
sudo apt-get install python3-lxml
# get the schemas
git clone https://github.com/IATI/IATI-Schemas.git
# sort the xml
python3 sort_iati.py input_file.xml output_file.xml
SJohns
SJohns • 8 years ago

Hello - yesterday Pelle and Theo mentioned that Open Data Services Cooperative are working on a CSV to XML Convertor that does 2.02. Any more info on that please? Steven Flower Ben Webb - IATI Secretariat cc. Rolf Kleef We need it for some of our orgs that have MFA funding please, and the above workaround is way too complicated for us mortals.

Steven Flower
Steven Flower • 8 years ago

Hi SJohns

Thanks. Everything that we (at Open Data Services Co-operative) have worked on is shared in the post I’m afraid.

The CSV2IATI tool is hosted by the Secretariat - I’m not aware of any roadmap to enable this to be 2.0x out of the box.

As a community, we probably need to think about what is useful for us, and how this is then resourced and maintained. In the case of CSV2IATI, I’m aware of how complex flat files can be (including the work we did on tabbed spreadsheets with Daniel Mackenzie ), and how far we can get from “plug and play”. However, the nature of IATI (multiples of multiples within multiples!) does demand a lot of attention in many cases.

Ben Webb - IATI Secretariat
Ben Webb - IATI Secretariat • 8 years ago

[Apologies for the deleted post, hit send too early].

Some further technical details.

The CSV2IATI 2.02 JSON mapping was generated by modifying a local instance of the CSV2IATI metadata generation code to use the 2.02 schema. I then created a mapping in the (local) interface, and exported the JSON from “Show JSON”). The JSON then needed manual editing to convert properly, this can be seen at: https://gist.github.com/Bjwebb/b3eccf2d314a2bdd3c83/revisions

The modified metadata generation can be seen at: https://github.com/OpenDataServices/CSV2IATI/commit/6677296976feb75e93e476d1edec7a38a34a7c3d (I’ve shared this with Dale and Rory, but I don’t think it’s posted on this thread yet as it’s not needed to follow Steven’s instructions).
This change, and the XML sorting code also linked above might be useful to create a 2.02 version of CSV2IATI, but more development (and deployment) work would be required.

Brent Phillips
Brent Phillips • 7 years ago

I’m curious if anyone has experimented with the reverse and gotten the IATI XML to CSV tool to work recently? Ben Webb - IATI Secretariat contributed some code to it last on GitHub: https://github.com/IATI/IATI-XSLT and here’s some information on using it published by Adrian Collier: http://adrian-collier.com/converting-iati-xml-data-to-csv/

I’m testing it but can’t get it to work and wondering what I’m doing wrong. I’m mainly interested in examining what it outputs depending on what files are used as input.

Please log in or sign up to comment.