Pull CSV data into your forms
We have a new feature that we are sure will make data collection easier. It is now possible to pull existing survey data into your forms on the Ona Platform using CSV files.
What does this mean?
Say you have data from a previous survey, such as a baseline survey of members in each household in a village. After the baseline survey ended, you exported this data in CSV format, and it’s sitting on your computer. You now want to go back to the same village and conduct another survey, such as a Vitamin A coverage survey. When visiting each household, you would like to know how many children in this household are under 5 years of age, because they are eligible to receive Vitamin A. Using the pull CSV data feature, you can actually pull that information from your baseline survey and display it in the Vitamin A survey form. You can now conduct your survey with confidence, knowing you are capturing every eligible child from your baseline survey!
So how exactly do I do this?
To start with, go to your Ona account and upload your CSV file under the Media section of the survey you wish to pre-load with data:
Here are a couple points to keep in mind when you upload your CSV file(s):
- The first row of the CSV file should contain short, unique names for each column. Subsequent rows should contain the data itself, one row for each survey entry.
- At least one column in the CSV file should contain unique identifiers for each row of data. This column will be used, when filling out the survey, to identify which row of data to pull into the survey. Optional: suffix this column name with _key for faster look up during the survey (e.g., id_key).
- Note that you can upload a compressed zip file that includes your CSV and other supporting files in case your files are too large. ODK Collect will automatically unzip the files when they are downloaded.
Here’s an example showing the number of children under age 5 of different households:
Data CSV
| hhid_key | u5child |
|---|---|
| 22 | 2 |
| 23 | 0 |
| 24 | 1 |
| 25 | 3 |
| 26 | 1 |
Once your CSV file(s) are uploaded, follow these steps to pull the data into your survey:
- For each field you want to pre-load into your survey, add a calculate type field.
- Give that field a name to refer to it later on.
- In the calculation column, include the pulldata() function, indicating which field to pull from which column from which CSV file.
See the example below for the pulldata syntax:
Survey XLS
| Type | Name | Label | Calculation |
|---|---|---|---|
| integer | hh_id | Household ID number: | |
| calculate | u5_children | pulldata(‘hh_survey’, ‘u5child’, ‘hhid_key’, ${hh_id}) | |
| note | u5_children_note | This house has ${u5_children} children under the age of 5. |
In this example, the pulldata expression is saying to pull the value from the u5child column of the hh_survey CSV file, using the hh_id field in this form to link to the specific row in the CSV file’s hhid_key column. Now that the u5_children field is populated with the same value from the CSV file, you can reference it in notes (as in the example above), constraints, relevant fields, etc., just as you would reference any other field.
You can take a look at this XLSForm here and the example CSV file here. If you download these files to try on your own, make sure you download them in Excel and CSV formats, respectively. For now, this feature works only on ODK Collect for mobile data collection and not Enketo webforms.
How does this actually work?
Continuing with the Vitamin A survey example above, let’s say the Vitamin A team enters a household in the village wanting to give Vitamin A supplements to all the children under age 5 in the household. Someone on the team opens up the Vitamin A survey on their ODK Collect application. They enter the household’s ID number, which was assigned during the baseline survey (this will be used to identify the correct household data to pull into this form). Not seen by the user, the survey will read the pulldata expression in the u5_children field to populate that field with the matching value from the CSV file, which was obtained during the baseline survey. The note in the form informs the user how many children under the age of 5 were recorded for this household during the baseline survey. The Vitamin A team then uses this information to ensure they reach every child in the household eligible for the Vitamin A campaign.
That’s it! We hope you enjoy using this new feature. If you have any questions about it or get stuck somewhere, shoot us an email at support@ona.io, and we’ll sort it out!