Work with CSV data in ArcGIS Survey123
Background
ArcGIS Survey123 authors often have existing data in a tabular format they would like to use in their surveys. For example, you could have a list of building locations or plant species. While you can create a choice list on the choices worksheet in the XLSForm, if you have a large list of hundreds - or even tens of thousands - of rows, adding a CSV file in Survey123 Connect is more efficient. Once the CSV is added to the survey, you can look up and retrieve a single value from the file or populate a choice list using a column from the CSV.
This blog post provides an overview of the methods for adding CSV data to your surveys and how to work with that data in XLSForm.
Add CSV data to your survey
In Survey123 Connect there are two ways to add a CSV file to your survey – either by copying it manually to the media folder or by linking your survey to a CSV item in your ArcGIS content. If you know your data is not going to be updated often then the manual method is fine for adding CSV files.
If your data will be updated regularly, for example, a list of personnel, then it is beneficial to link your CSV via Linked Content as this will enable you to update your CSV outside of Survey123 without the need to republish the survey. Ismael explains this in more detail in his Survey123 Tricks of The Trade: External choice lists blog. Additionally, other people can update the CSV item in your ArcGIS organization as well, if they have the necessary permissions.
Use CSV data in your survey
Once the CSV has been added, you are then able to use it in your survey. You can either derive a single value from a CSV or populate a choice list using a column in the CSV.
Pulldata()
To look up a single value, use the pulldata() function in the calculation column of the XLSForm. This is useful to populate an answer based on a previous response. For example, if you have a list of tree species, pulldata() can be used to look up and return the tree’s genus and family, or it can also be used to calculate values based on a previous selection. The pulldata() function is explained in more detail here.
There are two options to populate a choice list from a CSV file: select from file and the search() appearance.
Select from file
Select from file uses either a select_one_from_file or select_multiple_from_file question type to retrieve a column from a CSV file. For example, it could be used to populate a choice list with employee IDs. Like a choice list on the choices worksheet, the CSV must contain name and label columns for the function to work. In the XLSForm, enter the name of the CSV file after the question type to populate the choice list. For example, select_one_from_file teams.csv.
You can also add a choice filter in the choice_filter column, like selected(${teams},team) to narrow down the choices based on a previous selection. This requires an additional column to be added in the CSV, which is explained further in the video below. If you are using select from file and have multiple choice lists in your survey, you will need a different CSV for each list.
Search() appearance
The other method to populate a choice list is the search() appearance, compatible with select_one and select_multiple type questions. Your CSV does not need to have any specific column names and one file can be used in multiple lists. For example, it could be used to populate a list with products to restock. In the choices worksheet of the XLSForm you will have to create one choice that has the same name as the column in the CSV file you would like to return in your survey. To return the entire column as the choice list, in the appearance column, enter the name of the column after search. For example, search(‘product’).
Your choices can be filtered with further parameters. There are four parameters that are compatible with CSV files: startswith, endswith, contains, and matches. They could be used to filter by date, colour, or name, for example. Brett’s blog further describes how to use the search() appearance.
The video Work with CSV Data in Surveys goes through examples for each of the methods mentioned and may help you better understand how to add and use CSV data in Survey123.