CSV Converter Guidance - Using Bond Templates
A number of Bond’s members who are publishing their development cooperation data to the IATI Standard use CSV2IATI 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 in using CSV2IATI within this section.
- A 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.
- Bond have developed a set of models and templates that have taken a large number of the formatting tasks out of the process. Available to our members, is an Excel spreadsheet 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.
For an explanation of some of the complicated acronyms, please refer to theKey Terms for IATIpage:
Guide Two:Using the Bond templates
CSV2IATI allows you to create models that correspond directly to your spreadsheet. This means that the majority of the work is getting the spreadsheet and model “talking to each other”. Once this is working, all that needs to happen is the addition of new transaction data to a master spreadsheet, which is then uploaded to the CSV2IATI model.
In recognition of this, Bond have created Excel templates and CSV2IATI models that have already been synced up. The Excel template categorises IATI fields into tabs to help with easier spreadsheet management, in order to prevent organisations working with an ever-expanding spreadsheet. There is an Excel file that corresponds to the DFID minimum requirements, and another that allows for the full repertoire of the IATI fields. Please contact Bond via email@example.com for the templates, and if you think they would be useful to your organisation in IATI publication, continue with the walkthrough.
The following steps will need to be taken before you can start the modelling process.
Contact Bond via firstname.lastname@example.org let us know that you wish to use the template method. We will then share the CSV2IATI models with your CSV2IATI username.
In order to follow through with this method, you will need to download and install Open Refine. Open Refine is a very useful tool for editing, managing and converting data of all kinds,spreadsheetsin particular. Download the version that works for your computer and operating system from here - http://openrefine.org/download.html . You may need to clear the installation with your IT Department, and it is necessary to have Java downloaded before you start Open Refine- https://java.com/en/
Does your organisation publish beyond the DFID minimum requirements? If so, please do not jump straight for the Excel template restricted to the minimum requirements. Open the FULL version, and delete columns and tabs that are not relevant for your data. This will also be a useful process for becoming familiar with the columns in our template.
Populate your chosen template with your organisation data. Ensure that the data meets the formatting requirements suggested by column headers, e.g. if the header states ‘Region Code’, make sure you populate it with a code from the IATI Standard website instead of writing in the name of the region.
IMPORTANT - CSV2IATI is currently using version 1.03 of the IATI Schema. Make sure you are using codes and terms found in version 1.03, and not version 2.01. This is important to note as the default page for the IATI Standard website goes to v2.01.
Once you have filled in the template with your data and are happy that the columns reflect the reality of your organisation’s transactions, then you need to convert the Excel file into a CSV. CSV2IATI will only allow the upload of CSV files, which are ‘flat’ files with no tabs or extra formatting. Unfortunately, exporting the file from Excel as a CSV does not work in the way we want it to, so we have to enlist the help of Open Refine.
Open Refine may open up as Google Refine on your computer. This is a normal thing to happen, as they are the same piece of software. It is not a computer application in the traditional sense, and so will open up as a new window in your default browser. Although it is useful to note that you can use it when away from an internet connection and offline.
This video shows you how to do the following steps. Once the window is open, you will see a side bar on the left with the options to ‘Create Project, Open Project or Import Project’. Click on ‘Create Project’ and it will reveal the option to ‘Choose Files’ from your computer. Select the completed template, and click Next.
The next page is an 'in-between' stage, where you have selected the Excel file but you need to tell Open Refine which tabs you want to include. The page is roughly divided into two sections (excluding the side bar on the left); a preview window with columns from your spreadsheet, and a window below that which allows you to select which tabs you want to use from your Excel template by checking boxes. Check all of the tickboxes, and you’ll see columns and rows of data being added to the project.
This video shows you how to do a few neat tricks in Open Refine. The star function allows you to select rows and click freely around the page without losing your selection. You can highlight these using the facet function in the drop-down menu from the ‘All’ column on the left of the spreadsheet, which excludes or includes all rows of data you have starred using the true or false options. You can also use the text facet function to group information together and single them out. Once you have singled out your selection, you can edit the text if there are any errors, and you can export this selection as a separate spreadsheet. There is also an Undo/Redofunction, whch allows you to go back correct any changes, kind of like 'track changes' in other applications.
This video shows you how to do the following steps. As you will see, all of the tabs have been “flattened” into a single spreadsheet. All of the data is still present, and the information that was previously under column headers repeated in the tabs across the template have been collapsed into a single column. This is most notable with Activity ID, where information about the same Activity ID number that originally spread across tabs is now added on a row-by-row basis.
Unfortunately, exporting the file as a CSV direct from Open Refine will not work. Look to the top right of the screen and find the Export drop-down menu. Export the project as an Excel file, open the downloaded file into Excel and then save this as a CSV.
If you have received the template from a member of Bond, you need save it with a name you’ll recognise. Log on to CSV2IATI, and click on the model to open it. On the right there is an option to ‘Rename model’.
On the left you will see three stages: 1 - Organisation information, 2 - Mapping , 3 - Convert .
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.
When it comes to the 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. 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 the spreadsheet. If you do not tick this box, it will take the information from the first line only.
Once you have created the CSV file by following the Open Refine process outlined above, look to the column on the right for the ‘Upload new file’ link. Upload your CSV file.
The CSV2IATI model that has been shared with you by Bond will have ‘Mapping boxes’ pre-assigned to the column headers in your CSV file. The model will use these ‘Mapping boxes’ to identify data that is to be converted in the IATI XML.
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?
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.
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.
Download the XML file, and rename so it is relevant for your organisation.
Does your organisation have a webpage that relates to Open Information, Transparency or Accountability? This may be a good place to host your new XML file. You may need to contact your IT/Web team and ask them to create a new web page.
Log on to the IATI Registry, and update your Organisation profile to link to the page on your website that hosts the XML.
This step-by-step is a generic guide and will no doubt miss out finer tweaks that would be necessary for your organisaiton. Please feel free to get in touch and ask questions about the above process, or to discuss whether CSV2IATI will work for your IATI publication