CSV Converter Guidance – Getting Set Up

A number of Bond members who are publishing their data to the IATI Standard use CSV2IATI/CSV Converter as their publication tool.

CSV2IATI is useful for your organisation if you are publishing large amounts of data, as it converts a spreadsheet into XML. This means you can have a spreadsheet with many rows of data, and convert it into the machine-readable data format that is required for IATI.

There are two guides to using CSV2IATI:

1. This walkthrough on how to set up your organisation, prepare a model for conversion and convert your data into an acceptable IATI format. This is recommended for those who may not be familiar with CSV2IATI and wish to understand the process. However, it is also useful for members who are already using CSV2IATI, but are producing data that repeatedly has errors.
2. Bond have developed a set of models and templates that have taken a large number of the formatting tasks out of the process. An Excel spreadsheet is available to our members, that has separate tabs for the categories of the IATI standard, and a CSV2IATI model that corresponds to this spreadsheet. There are two sets – one that meets the DFID minimum requirement, and one for the FULL number of fields that can be published to IATI. This guide will explain how to use these templates and then match them to the CSV2IATI model, which can then be converted into XML.

These guides are predominantly text-based, but there are videos which show examples of the complicated parts to help you along the way.

For an explanation of some of the complicated acronyms, please refer to the Key Terms for IATI page.

Getting Set Up on CSV2IATI

Go to the CSV Converter website and Log-in/Register.

IMPORTANT – Does your organisation already have a CSV2IATI username? If so, please use this so there is no confusion in sharing models and publishing. If your organisation has more than one username, please notify Bond which one you will be using and we will delete the extras.

NOTE – This guide should be used with direct reference to the CSV2IATI website, as it provides a step-by-step process. You may wish to have this guide and the CSV2IATI website open in two windows next to each other as you proceed.

Dashboard

On the Dashboard you will be shown all of the models that you have created or models that have been shared with you. If you are just starting out, there will be nothing to see.

Start by creating a new model, in the “Create new model” box. Name the Model and select the CSV file that you wish to convert into XML.

NOTE – CSV2IATI will only upload a CSV file, not an Excel file. CSVs are “flat” files with no tabs or extra formatting.

Model

On the left of the page there are three sections to open and complete: 1 Organisation Information, 2 Mapping, and 3 Convert.

Organisation Information

Fill in your details for the “Your Organisation”, “Basic Information” and “Contact Details” boxes. The hyperlinked text highlighted in blue will take you to websites to help you decide which codes to use.

If you are not using the template provided by Bond, please tick the box at the bottom of the “Contact Details” box – “Add to activities?” . Contact information is one of the DFID minimum requirements, and this option brings the details of this box into every activity automatically.

SAVE! – On the right hand side there are a number of options, including Save, Rename and Delete. Do not forget to Save along the way.

Advanced controls

Ignore the first two fields (Data Encoding and Hierarchies) as these will be automatically filled in. The important part here is the “Multiple” tick boxes. Tick all of the boxes that relate to the fields of data your CSV contains.

Spreadsheets can record information by going horizontal (adding new columns) or by going downwards (adding new rows). We think it is much more manageable to record data on a row-by-row basis (downwards), as adding more columns contributes to an ever-expanding spreadsheet. As we will see later, this can cause problems for the modelling.

When you select one of these boxes, e.g “Multiple transaction rows per activity”, you are essentially telling CSV2IATI to scan down the column within your spreadsheet. If you do not tick this box, it will take the information from the first line only.

Mapping

In this section you will tell the CSV2IATI model where to look in your spreadsheet to find the data you want to convert into IATI XML. Each “Mapping” box is ready to be assigned to a column within your spreadsheet.

There will be a default number of boxes available for you to fill in. However, as there are some that you do not need, and others that you will be need to add in, we consider it is easier to delete all of the boxes and then add them in again based on the fields you have in your CSV. Delete boxes by clicking on the small “rubbish bin” icon on the top right of the Mapping box, and add them back in by scrolling down to the bottom of the page and opening the drop-down labelled “Add a new element”. This way you will not spend time thinking about whether the default boxes are relevant to you. Here is a video using the DFID minimum requirements template as an example of how to do this.

SAVE!

In the process of adding the Mapping boxes, you may find that you cannot assign new “elements” directly from your column headers. It is likely the case that these extra column headers are attributes, and need to be selected within the Mapping boxes themselves. Here is a video using the same template as an example.

Once the Mapping boxes are in place, assign the numerous drop-downs to the relevant fields in your spreadsheet. This will tell the CSV2IATI model where to find data content from your spreadsheet. Here is a video as an example of how to do this. Along the way, make sure that you are assigning the “code” or “(text)” drop-box accordingly, for example, make sure the “collaboration-type” box has the “code” drop-box linked to the column in your spreadsheet that represents the codes, and not the text description.

SAVE!

The model is almost ready. Depending on the data in your spreadsheet, you may need to edit the model to make sure the data meets the formatting requirements. CSV2IATI will only Convert the data into XML if it meets the correct format. The usual offenders in this case are the date, the IATI activity identifier and making sure the “Attribute” types are correct, e.g. is the Activity Title assigned to a (text)” Attribute (correct) or code?

Date

The standard date format for IATI is yyyy-mm-dd, e.g. 2015-01-01. If the dates in your spreadsheet are in a different format they need to be changed, e.g. 01/01/2015.

CSV2IATI provides a tool for transforming the dates in your spreadsheet for converting to XML without changing the source data. In the “activity-date” Mapping box you should already have assigned an “iso-date” Attribute to the column containing date information. To the right of the drop-down will be a small “spanner” icon. Clicking on this will reveal an option to “add transform”, which you then assign as a “Date” transform. In the box revealed, type in the following code, depending on which date format your spreadsheet contains. This video shows how to do this:

01/01/2015 = d%/m%/y

Further date transform codes will be added to this webpage.

IATI Activity Identifier

The XML will fail validation unless the IATI activity identifier follows the correct format; with the organisation identifier and the activity identifier, and with the hyphens in the right places, e.g. GB-COH-54321-AACT01.

If the column for the Activity ID in your spreadsheet only includes the activity number, then you will need to add the organisation identifier. You can use the Transform function on CSV2IATI to do this.

Look to the “iati-identifier” Mapping box. To the right of the drop-down will be a small “spanner” icon. Clicking on this will reveal an option to “add transform”, which you then assign as a “Text before” transform. In the box revealed, type in text that will then appear before any content in your column, i.e. your organisation identifier. This short video gives a visual example.

Once you”ve done all of these steps, and checked formatting issues have been dealt with, go for the Convert. Ensure you have saved any changes before you try to Convert.

Convert

Click on Convert, and the subsequent blue button that appears. This will load into a new page with a link to an XML file. Click on the Validate link below to be taken to the Validator. You are looking for it to say “Well formed XML”. Then, in the bottom of the greyed out box click on the “Test Validation” link. This will pick out any errors of formatting. If any appear, be sure you have gone through the steps above.If it comes back saying “Success!”, then the XML is ready to be published on your organisation”s website.