IATI Tables Documentation

What is IATI Tables?

The IATI Standard is in XML format, so the data cannot be directly loaded into a database or data analysis tool. IATI Tables solves this problem by transforming IATI data into relational tables and making it available to users in multiple output formats.

Process

Once a day, IATI Tables pulls the latest IATI Data Dump and processes it. The metadata table shows the timestamp of the data dump which was used, and at what time IATI Tables processed the data.

IATI Tables replaces all the data each time it runs, so updates and removals will be respected.

IATI Tables serves raw data. It doesn’t perform any deduplication or corrections. For example, if an activity has been published multiple times, it will appear multiple times in IATI tables.

Data Schema

The IATI Tables homepage shows a list of the tables and columns available, with descriptions.

The _link column acts as a primary key for each table. The _link_activity column acts as a foreign key back to the activity table.

Output Formats

Datasette

Datasette is an open source tool for exploring data. The IATI Tables Datasette instance allows you to explore IATI data in the browser using SQL, as well as download the results of your query in multiple formats. For more information please see the Datasette documentation.

CSV Zip

The CSV Zip file contains a compressed folder containing a CSV file for each table, which you can explore by importing into a spreadsheet viewer such as Excel or Google Sheets. For more information, please see Spreadsheets.

SQLite Zip

The SQLite Zip file is a compressed SQLite database, which can be run and explored with sqlite3.

PG Dump

The PG Dump files can be loaded into a PostgreSQL database and are created by the pg_dump utility.

There are two options to choose from, gzip or custom:

Gzip

The ‘gzip’ format gives you a compressed plaintext script of SQL commands, which can be restored using psql.

Custom

The ‘custom’ format can be restored using the pg_restore utility. This option is more flexible if you want to perform any schema changes before restoring.

Colab Notebook

The Google Colab notebook, shows some examples of how the data can be explored in Jupyter Notebook format. You can save a copy of the notebook to edit it for your own use case.

Data Usage

Spreadsheets

Spreadsheet programs such as Microsoft Excel or Google Sheets can help with viewing and analysing CSV data. Below are some recommendations for exploring IATI Tables data in spreadsheet programs.

Disable Auto-Formatting

When opening CSV files, spreadsheet programs try to automatically assign types to each column, for example for numbers and dates. Auto-formatting strips leading zeros from numerical identifiers, for example transforming the identifier 00010 into 10.

To prevent these incorrect transformations, disable auto-formatting when loading CSV data into spreadsheets. For example, in Google Sheets, take the following steps:

Create an empty spreadsheet
Select File > Import
Upload the file you want to import
On the Import file screen, uncheck the box labelled “Convert text to numbers, dates and formulas”
Click Import data