Previous Next

If you have pre-existing data from a baseline survey, a listing survey, or any other source, you may want to reference that data in your survey form. For example, you might already have data on a household's agricultural plots when you go to collect follow-up data about those plots.

Click here to watch a video on pre-loading.

Methods of attaching pre-loaded data

When you want to reference pre-existing data in a survey form, you can pre-load that data into your form in one of two ways:

1. By directly attaching .csv data to your survey form.

2. By creating a server dataset, then attaching that dataset to your survey form.

Both methods are conceptually the same: you are attaching data to your form.

In the first case, you directly attach the data as one or more .csv files when you upload your form (the same way you attach image, audio, or video support files when designing your form in the designer or uploading a form to the Your forms and datasets section of the Design tab). If you work in Excel, and prefer to work with .xlsx files, you can upload an .xlsx file instead, and your server will offer to automatically convert it to the required .csv format for you. If your .xlsx file contains multiple sheets, you will be able to select which sheet to convert to .csv. The data becomes effectively part of your form, and when you want to update that data you have to upload a new version of the form with a new version of the data attached.

In the second case, you first create the dataset by clicking + in the Your forms and datasets section of the Design tab, then Add server dataset, followed by New dataset for data, and uploading your .csv file under a unique title and ID of your choosing. You then attach that dataset to your form by clicking the Attach button under your newly-created dataset and then selecting the form(s) to which you would like to attach it. In this case, you are still uploading and attaching a .csv file that contains your data – but this second option offers several key advantages over attaching the data directly:

1. You can later update the data without having to edit or upload a new version of your form. Simply return to the Your forms and datasets section, find your dataset, and click Upload; you'll be given the option to use your new .csv upload to either fully replace the existing data or append to it. As soon as you update the dataset, any forms to which that dataset is attached will be automatically updated. You won't need to upload updates to the forms themselves.
2. Second, your datasets can be updated automatically, with data streaming in from other forms. See Publishing form data into server datasets for more on that possibility.

Format of pre-loaded data

However you attach data to your forms, the format of your pre-loaded data should be the same:

1. The first row of each .csv file that you upload should consist of a header that assigns short, unique names for each column. Subsequent rows should then contain the data itself.

2. Columns in your .csv files must be separated by commas. Depending on your regional settings, Excel may save .csv files with semi-colons or other characters between columns, instead of commas; if that's the case, you will need to manually replace the column separators with commas, or save your data using a different program.

3. At least one column should uniquely identify each row of pre-loaded data. Such columns will be used at survey time to look up which row's data to pull into the survey. For the columns that will be used for looking up rows, you should add "_key" to the end of the column name (as in "hhid_key"). Any columns with names ending in "_key" will be indexed for faster look-ups on your survey devices.

Doing the actual pre-loading

The easiest way to use pre-loaded data in your survey form is to indicate that you'll be using pre-loaded data when you start a new form from the Design tab. In the Your forms and datasets section, click + then Start new form, enable Advanced options, and mark the checkbox next to Auto-generate fields necessary for pre-loading data; when prompted to choose your pre-loaded data, select a .csv file or server dataset to give an example of how your pre-loaded data will look (it doesn't have to be the final data, but it should have the correct columns); confirm that the data looks correct; identify which column uniquely identifies each row; and answer a few other questions regarding how the survey form should identify which row to load. Your new form will start out with all of the fields necessary to pre-load your data.

You can also add pre-loading fields to your form manually, of course.

In the form designer, click to + Add hidden field, select calculate as the field type, and click Configure to add a field that you want to pull into your survey. Next, give your field a name, click Specify with a wizard for the calculation, and select Pull pre-loaded data from attached dataset or .csv file. Finally, attach the appropriate .csv or dataset, specify the column name for the column you want to pull into your form, specify the column name for the column you want to use for looking up the appropriate row, and indicate which form field has the value to use in looking up the row. The wizard will save the appropriate calculation for you, and you can then refer to this new calculate field whenever you want to refer to the pre-loaded data elsewhere in your form.

In the spreadsheet form definition, add one calculate field to the type column of the survey sheet for each data field that you want to pull into your survey. Give that field a name, and use the pulldata() function in its calculation column to indicate which field to pull from which row of which server dataset or .csv file. Here is an example:

pulldata('hhplotdata', 'plot1size', 'hhid_key', ${hhid})

This example expression will pull the value from the plot1size column of the attached dataset with ID hhplotdata – or from the directly-attached .csv file named hhplotdata.csv, if there is one – using the form's hhid field to identify the appropriate row in the pre-loaded data's hhid_key column.

Rather than writing it out by hand, you can use the calculation-builder to construct your own pulldata() expression. The builder is available as a tool in the Your forms and datasets section of the Design tab. Once you run the builder, choose Pull pre-loaded data from attached dataset or .csv file as the calculation type, then enter your details and click Build. You can just copy and paste the resulting expression into your form's calculation column.

Once you have inserted pre-loaded data into a survey field using the pulldata() function, you can reference that field in later relevance or constraint expressions, or in other fields' labels. You can refer to your pre-loaded field as ${fieldname}, just as you would any other field that was calculated or filled in by the user.

For a working example, see Pre-loading: Referencing pre-loaded .csv data.

Pre-loading into editable fields

The technique described above pre-loads data into hidden calculate fields, which you can then reference in labels, constraints, or relevance expressions. Instead, you may want to pre-load data directly into a visible, user-editable field. That is also possible.

In the form designer, change the default setting to Calculated default when adding or editing any visible field. Then choose Add with a wizard and follow the same process as described above.

In the spreadsheet form definition, just include the proper pulldata() expression in the calculation column of the editable field. That's basically it: you format the pulldata() expression in the same way described above, and you put it in the calculation column, also in the same way as above. The only difference is the field type of the row in which you place the expression: here, it's a regular, editable field type rather than a calculate field.

There are a few other things to consider when dynamically loading an editable field's default value, however, so see Providing default entries or selections for fields for more details.

Additional options and considerations

A few additional notes on pre-loaded data:

1. If your pre-loaded data contains non-English fonts or special characters, you will need to save in UTF-8 format in order for mobile devices or web browsers to render the text correctly. If you cannot directly save or export to a .csv file in UTF-8 format, you have two options.

   1. Use an .xlsx file or Google Sheet instead of .csv. When you attach an .xlsx file or a Google Sheet, your server will automatically convert it to .csv with the correct character encoding (UTF-8). If the file you're trying to attach has multiple sheets, you will first need to select which sheet to use, then select the option to convert to .csv.
   2. Use SurveyCTO Desktop to re-encode it: choose Re-encode .csv from the Offline form tools menu, select your file and the encoding for which its text appears correctly in the preview window, and then click Convert to save the re-encoded .csv file.
   UTF-8-encoded .csv text should then appear properly, provided that your device or web browser supports the font or language that you're using.

2. Even when they are numbers, data fields pulled from a dataset or .csv file are considered to be text strings. Thus, you may sometimes need to use the int() or number() functions to convert a pre-loaded field into numeric form (for example, before performing some mathematical calculation with it, like "number(${preloadedinterestrate})*100").

3. SurveyCTO will ignore any .csv columns that appear after a fully-blank column, so be sure not to include blank columns in your pre-loaded data (i.e., the first row should have names for every column present in your .csv file).

4. If your .csv file is very large and you're attaching it directly to your form, you can compress it into a .zip archive before uploading it. Just compress one or more of your form's support files into a .zip file and upload that .zip as you would normally upload a .csv or other support file. When your form is loaded, the support files will be automatically unzipped.

Finally, you can also use pre-loaded data to dynamically populate the choice lists for select_one and select_multiple fields. See the help topic on Loading multiple-choice options from pre-loaded data for details.

Previous Next