Challenge: create super-simple conversion from Excel to IATI XML

A few months ago I wrote down some thoughts on “spreadsheets to IATI”.

We’re exploring some options for an organisation. I think the “integrate it into their processes” is actually the key requirement here: that also means integrating it with quality and compliance, publishing, and using the data in one way or another.

dear Sarah, i think this is a great idea and i would be willing to provide contributions to this suggested work. my organisation is just starting to publish data according to IATI and as we have funding from the NMFA, for the consistency reasons, i will be advising the senior management to publish data using NMFA’s extended requirements. i can foresee that we are going to have difficulties in transforming our financial data from Quickbooks to CSV or even more so to IATI XLM formats. To transfer data manually would be too time-consuming.

i am looking at Rolf’s document and trying to figure out if it already brings solution, as apparently it allows for converting Excel into IATI XLM format? we publish data through Aidstream.

i am not sure how helpful i can be as our organisation have beginner’s level in terms of IATI. do you know if many organisations publish the financial data at the transaction level? as i believe only in this case the solution for an easy conversion is really important (contrarily to the scenario when aggregated data per quarter is published)?

many thanks

Thanks for putting this post and it was really interesting to read though the thoughts by Rolf Kleef too. Together, they set out a vision with a greatly improved user-experience than the IATI Secretariat’s existing CSV2IATI tool.

I like the idea of having multiple sheets within a single workbook, which would deal with the multi-dimensional nature of the IATI Standard. However, some thought might be needed to deal with elements that are themselves multi-dimensional (for example, an iati-activity can have multiple transaction elements, which itself can have multiple sector elements). The usability of such sheets in the workbook would need some consideration, particularly if the entire tool is to fulfil its goal of being ‘IATI beginner’-friendly.
Of note on this point - the element iati-activity/result/indicator/title/narrative is the ‘deepest’ repeatable element that I have found in that v2.02 standard.

In terms of development, I know IATI Studio and Aidstream were considering adding the ability to generate IATI XML from spreadsheet-like formats - perhaps [~379] or Bibhusan Bista might be able to comment on their plans??

Whilst the secretariat have made a commitment to host the existing CSV2IATI tool until new tools and publishing solutions emerge to replace it, it is highly unlikely that there will be further development work on this legacy tool as our focus for development work is currently focussed on streamlining the internal architecture and supporting the data usage agenda.

Greetings,

Would it not be better to have a web service/REST API hosted on the same domain as iatistandard to perform the conversions as opposed to distributing software that would be liable to change at each update of the specification?

The service could verify submitted Excel workbooks and maintained centrally. Just like a website it would serve desktops, tablets and mobiles.

Looking at the last post, it seems Aidstream essentially provides a way of does this

Regards

Thanks for raising this SJohns

I agree that there is a need for something around this. I did a lightening talk at the IATI members’ assembly to highlight experiences of how people can get “confused” during the conversion and validation process (warning: contains cats!)

Over at the 360Giving data standard, we’ve worked hard to support organisations to publish spreadsheets (via templates), which can then be “easily” converted to the JSON format (360 is JSON rather than XML) via such tools as CoVE. In this context. it has felt like a smoother process to publication, as organisations are able to control and publish data in a format they know - which is particularly important in smaller places where there is litlle or no IT support/infrastructure.

Of course, it should be noted that no process (imho) can ever be “super simple”. Even with 360 there are a few gotchas. We’ve all been down the date formats in windows / excel rabbit hole!

We should be minded that IATI has lots of one-to-many relationships: a typical activity will have multiple transactions at the very least, but perhaps also more than one participating-org, document-link, sectors and possibly geographies. When we get to results, then this starts to get more complex - multiple indicators with multiple time periods for reporting targets and actuals anyone?

As you state, this starts to make more sense when in a workbook. It should also be worth flagging some of the efforts around this already. I made a version of 2.01 (warning: this isn’t fully tested) and added the files here. These files provide “user friendly” titles for the various parts of the IATI schema, which is your point about the mapping to the standard. Indeed, with Bond we provided guidance about working with such files, “flattening” them to a CSV, and then running through CSV2IATI to mint IATI XML. I’d agree that this isn’t “super-simple”, but it was interesting to see some organisations adopt this approach, once initial support / training / explanation had been given.

There’s another school of thought which we should flag: “messing” around with spreadsheets can lead to inefficiencies and data quality loss. Outputting IATI XML data in the right and valid format should not be anywhere near the frontline. We’ve seen many organisations produce IATI data via teams working together at their strengths - with IT controlling the data production end. I just wanted to flag this, as no matter how great a tool is, it could be argued that solutions are better sourced in-house, within processes and systems already in place. That’s a whole other discussion though!

That said, at Open Data Services Co-operative, we will start to work around some of this in 2017. We’re not ready to announce timelines just yet, but will be testing how the various libraries and tools we have can address these user needs, starting with our own work. We will, of course, share our thoughts along the way.

Hi Sarah and All,

We made a standalone offline excel publishing tool a few years back and still use it for our publication - see https://github.com/WaterAid/iati-xl2xml/raw/master/src/xl2xml.xlsm . We needed to take the technical aspects out of publishing IATI and allow the various people involved in the publishing process (different areas of responsibility, approval processes etc.) access to the data on their terms so we chose Excel as a common tool. There is a different worksheet for each of the main areas of the standard, eg basic information, Transactions, Activity dates etc. linked via the first column of each table, and we use a mixture of manual data entry and automatic data feeds (where we have corresponding systems). We only fill in the blue cells of the table (grey and green are autofilled based on the IATI standard and WaterAid’s publishing model - saving about 2/3rds of the publishing burden).

To create the XML you just run the “GenerateXml” macro and choose an output directory. At the time we anticipated our country offices would need to enter some of the data so we made this tool 100% offline and requires no additional installers etc. beyond having a windows machine with Excel installed (vba using standard windows libraries) - meaning it can be emailed back and forth with all relevant sign-off data in one place (useful for approval processes).

Although we have used WaterAid publication as an example, technically it can publish as much or as little of the IATI standard as you like without restriction - you just change/ add / remove the table column headings (row 20) as appropriate and copy in new sheets for any additional repeating elements (under the hood the tool starts with the “iati-activities” sheet then traverses sheets (linking to its appropriate parent through cell C1) to recursively build out each sub-element on a row by row basis before moving to the next row, using the IDs in column C and row 20 to guide output - so its actually a general purpose excel to XML export tool really!).

While this has proven great for our publishing, what’s needed to make this super simple for other organisations is a number of standard templates (with the worksheets configured accordingly) that users can choose according to their publishing style and language. Even better would be a front-end setup GUI allowing users to choose which elements of the IATI standard they wish to publish (the tool would then use the IATI schema to create worksheets, columns and codelist drop-downs appropriately, even setting up auto fills) - we started looking into this (eg using the IATI validator rulesets), however we couldn’t justify further time on the project as we didn’t have an internal need at the time. Instead we open sourced it in the hope that others might take this further (Steven Flower - this is the same tool and workflow I talked you through the other year, should this help jog memory!). We’d be very happy to talk more about this, and given the new DFID requirements for NGOs to ensure our partners publish data we may well start another round of development/ create some new templates for this - anyone interested in collaborating? Funding would also help maintain/ build communications for this tool - anything available?

Best,

Mike

I’ve just done a spreadsheet-to-IATI conversion for Aids Fonds last week, which enable me to set up a first rough tool chain following the “pseudo-iati merger” approach from my document:

• They can extract individual Excel spreadsheets from their Management Information Systems (in their case one, but could be multiple): basically tables of information with an IATI activity identifier in each row (results, transactions, basic project information, …)
• I convert them to CSV files, set up the mapping, and merge the results into a single IATI file. Setting up the mapping is still a manual step that needs to happen once.

We’re working along the way of Steven’s “different school of thought”: extracting useful spreadsheets from existing systems, rather than setting up a special IATI spreadsheet. It’s an incentive to fix the data at the source, rather than during an IATI publication process.

And the mapping allows for some additional transformations (for instance from internal codes to IATI code list values), and mostly is a one-time set up.

Hi all,

Just to chip in here. As part of our development for the ZOOM project (a data analysis platform built for Aidsfonds in cooperation with HumanityX -Leiden University-) we are in the final stages of the Datamapper component: a (lots of different types of) CSV convertor to the ZOOM data model. ZOOM is built on top of the IATI Studio core, which runs on top of the OIPA Datastore, so we’re trying to re-use existing technology.

With regards to the ZOOM Datamapper component, I’d be happy to see how we can amend that conversion process so it could fit a ‘generic’ CSV file for organisations to publish to IATI. In this setup we have many moving parts: data input, data normalisation, data storage and data output (API).

Seeing how ZOOM will touch some of the IATI Studio UI elements and OIPA, I could see the following scenario play out:

Datamapper (ZOOM data conversion component)
CSV input (different types)

OIPA Datastore (input/conversion/storage/API)
IATI XML input

IATI Studio
IATI Publisher component (Manage IATI data/connect to Registry etc.)

I’d be happy to have a call on the ZOOM Datamapper for:

• CSV ‘IATI’ Ready files for input
• Map CSV ‘IATI’ to OIPA/IATI Studio
• Publish the converted CSV ‘IATI’ XML file to the Registry.
• Continue & repeat

Can we setup a call and see how we would like to move on this specific subject?

Thanks, Siem

I’ve now written up our approach to Spreadsheet to IATI conversion, as introduced in my lightening talk at the TAG:

Flatten-tool: New approach for Spreadsheet to IATI conversion Community Zone

This post is mostly a write up of the lightening talk I gave at the TAG, with some additional info on how the work is progressing. CSV2IATI is going to be retired this year, so at Open Data Services Co-operative we've been looking at alternate approaches for converting spreadsheets to IATI XML. For OCDS (Open Contracting Data Standard) and 360Giving, we built flatten-tool. This is a command line tool, and python library, for converting from flat spreadsheet formats to nested structured form…

The command line tool is currently usable offline, but I would concede that’s not super simple. We’re working on a simple web interface though, but that won’t be available offline.

Interested to discuss this further, to see if we could better address SJohns bullet points. One approach we discussed very briefly at the TAG, was whether Mike Smith ’s tool and ours could be made more compatible.