XLSForm pulldata(): Look Up Values from a CSV

pulldata() in XLSForm is pulldata('file', 'column_to_return', 'column_to_search', ${value}), written in the calculation column of a calculate row, with the CSV uploaded as a form attachment. It returns the value from the first row of file.csv whose search column matches. ODK, KoboToolbox and SurveyCTO use the same four arguments, and every example below compiles with pyxform and ODK Validate.

Download example form (.xlsx)All five examples in one XLSForm, one group each. Tested with pyxform and ODK Validate; free to reuse under CC BY 4.0. Attach your own households.csv to fill it.Download

What is the syntax of pulldata() in XLSForm?

A follow-up survey needs the household head's name from the baseline. The baseline export becomes households.csv:

hh_id,head_name,village,hh_size
HH-0101,Rahima Begum,Char Kukri,5
HH-0102,Abdul Karim,Char Kukri,7
HH-0103,Shefali Rani,Dakshin Char,4

The form asks for the ID and pulls the name:

type	name	label	calculation
text	hh_id	Enter the household ID from the visit list (e.g. HH-0101)
calculate	head_name		pulldata('households', 'head_name', 'hh_id', ${hh_id})
note	show_head	Household head: ${head_name}

◀ ▶surveychoicessettings+

The four arguments, in order:

1. The file name without .csv. 'households' reads households.csv.
2. The column to return, exactly as spelled in the header row.
3. The column to search. Usually the ID column.
4. The value to search for, usually ${question}.

The first three are text, so they go in single quotes. The match is exact text: hh-0101 does not find HH-0101, and a trailing space in the CSV is part of the value. When no row matches, pulldata() returns an empty string, not an error.

How do I attach the CSV file to the form?

The form only names the file; the file itself is uploaded next to the form:

Platform	Where the CSV goes
ODK Central	Upload the form, then upload households.csv under the draft's form attachments before publishing
KoboToolbox	Project Settings, then Media, upload the CSV, then redeploy the form
SurveyCTO	Attach the CSV to the form definition when you upload it

Four file rules prevent most "pulldata returns nothing" threads on the ODK and Kobo forums:

• Same name, no spaces. household list.csv fails silently in Enketo on KoboToolbox. Use household_list.csv and 'household_list'.
• UTF-8. Save from Excel as "CSV UTF-8", or names in Bangla, Arabic or with accents come out garbled or never match.
• A dot for decimals. Excel on a computer set to comma decimals (2,5) saves CSV files with semicolons between columns, so each row reads as one column. Switch the decimal separator to . before exporting.
• Header names without spaces. Short names like hh_size, not Household size.

How do I pull several columns for one household?

Write one calculate per column. Each one searches the same ID column and returns a different field. Then show them together so the enumerator can confirm the right household before starting:

type	name	label	calculation	relevant
text	hh_id	Enter the household ID from the visit list (e.g. HH-0101)
calculate	head_name		pulldata('households', 'head_name', 'hh_id', ${hh_id})
calculate	village		pulldata('households', 'village', 'hh_id', ${hh_id})
note	confirm_hh	Household head: ${head_name}, village: ${village}
select_one yes_no	hh_correct	Is this the household you are visiting?
note	wrong_hh	Go back and check the household ID on the visit list.		${hh_correct} = '0'

◀ ▶surveychoicessettings+

list_name	name	label
yes_no	1	Yes
yes_no	0	No

◀ ▶surveychoicessettings+

Pulled values work like any other answer. ${village} can drive skip logic or a choice filter in later questions.

How do I use a pulled number in a calculation?

pulldata() always returns text, because a CSV has no data types. xlsform.org advises converting it with number() (or int() for whole numbers) before you use it as a number. This round-two check flags a household that changed size by more than two people since baseline:

type	name	label	calculation	constraint	constraint_message
text	hh_id	Enter the household ID from the visit list (e.g. HH-0101)
calculate	baseline_size		pulldata('households', 'hh_size', 'hh_id', ${hh_id})
integer	current_size	How many people live in this household now?		${baseline_size} = '' or abs(. - number(${baseline_size})) <= 2	Baseline recorded ${baseline_size} people. Check the roster before entering a bigger change.

◀ ▶surveychoicessettings+

The ${baseline_size} = '' part lets a household that is missing from the baseline file through: number('') is not a number, and any comparison with it is false, so without that part the question could never be answered. More validation patterns are in the XLSForm constraint examples.

How do I check that the ID exists in the CSV?

Put pulldata() in the ID question's own constraint. If the lookup comes back empty, the ID is not in the file and the enumerator cannot move on. Inside a constraint, . means "this question's answer":

type	name	label	calculation	constraint	constraint_message
text	hh_id	Enter the household ID from the visit list (e.g. HH-0101)		pulldata('households', 'hh_id', 'hh_id', .) != ''	This ID is not on the visit list. Check for a typo or a missing dash.
calculate	head_name		pulldata('households', 'head_name', 'hh_id', ${hh_id})
note	show_head	Household head: ${head_name}

◀ ▶surveychoicessettings+

Returning the search column itself ('hh_id' twice) is the simplest test: it is never empty for a row that exists. Scanning a barcode instead of typing (type barcode) removes most typos in the first place. For building IDs that are easy to type, see unique IDs in XLSForm.

Should I use pulldata() or instance() in ODK?

Both read the same attached CSV. ODK's documentation and xlsform.org now describe instance() as the preferred lookup, because the filter in square brackets can be any condition. Declare the file with a csv-external row, named after the file without .csv:

type	name	label	calculation
csv-external	households
text	hh_id	Enter the household ID from the visit list (e.g. HH-0101)
calculate	head_name		instance('households')/root/item[hh_id = ${hh_id}]/head_name
calculate	big_households		count(instance('households')/root/item[hh_size > 6])
note	show_head	Household head: ${head_name}. Households with more than 6 members on the list: ${big_households}

◀ ▶surveychoicessettings+

Need	Use
One value by ID, form used in KoboCollect or older apps	pulldata()
A lookup with two conditions, a count or a sum over rows	instance() with csv-external
The respondent picks a row from the CSV	select_one_from_file, see cascading selects
Very large files (tens of thousands of rows) on slow phones	pulldata(), which xlsform.org suggests when instance() is too slow

With instance(), one rule matters: if the filter matches two rows and you ask for one value, ODK Collect stops with a type mismatch. pulldata() quietly takes the first row instead, which hides duplicate IDs. Check the CSV for duplicates either way.

Common pulldata() mistakes and how to fix them

Most pulldata() mistakes compile without complaint and leave the field blank on the device, so test with a real ID before fieldwork. We ran each of these through pyxform and ODK Validate:

You wrote	Write instead	What you will see
PULLDATA(...)	pulldata(...)	cannot handle function 'PULLDATA'
pulldata('households.csv', ...)	pulldata('households', ...)	No error, field stays blank
pulldata(households, head_name, hh_id, ${hh_id})	Quote the first three: 'households', 'head_name', 'hh_id'	No error, field stays blank
pulldata('households', 'head_name', 'hh_id')	Add the value: ..., 'hh_id', ${hh_id})	No error, field stays blank
File named household list.csv	household_list.csv and 'household_list'	No error, blank in Enketo
The same ID on two rows of the CSV	One row per ID	No error, the first row wins every time

For other compile messages, see common XLSForm errors, or paste your form into the free XLSForm validator.

Does pulldata() work the same in ODK, KoboToolbox and SurveyCTO?

The syntax is the same on all three: pulldata('file', 'column_to_return', 'column_to_search', value), with the file name given without .csv. What differs:

Platform	What to know
ODK Collect and Central	Works offline once the form and its CSV are downloaded. instance() with csv-external is the documented alternative
KoboToolbox (KoboCollect and Enketo web forms)	Upload the CSV under Settings, then Media. Enketo fails silently on file names with spaces
SurveyCTO	Same function in the calculation column; skip logic goes in a column named relevance

A form that uses these examples moves between the three platforms as long as the CSV travels with it.

Why preload instead of asking again

Every value you pull from the baseline is a question the respondent does not answer twice, and a mismatch you can catch at the door instead of during cleaning. Showing "Household head: Rahima Begum" before the first question tells the enumerator they are in the right house. A constraint against the baseline household size catches a roster that lost three people while the household is still there to explain why.

Build my first form free

Next: XLSForm calculation examples →