Can You Upload Data From Survey to Excel

If you have pre-existing data from a baseline survey, a list survey, or any other source, you lot may want to reference that data in your survey form. For example, y'all might already have data on a household's agronomical plots when you go to collect follow-upwardly data almost 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 tin can pre-load that data into your form in 1 of two means:

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

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

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

In the offset example, you lot directly attach the data as ane or more .csv files when yous upload your class (the aforementioned way you lot 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 Blueprint tab). If you lot piece of work in Excel, and prefer to work with .xlsx files, you can upload an .xlsx file instead, and your server volition offer to automatically convert it to the required .csv format for yous. If your .xlsx file contains multiple sheets, you will be able to select which sail to convert to .csv. The data becomes finer part of your form, and when you lot want to update that data you have to upload a new version of the form with a new version of the information attached.

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

1. Y'all can subsequently update the data without having to edit or upload a new version of your course. Simply return to the Your forms and datasets section, observe your dataset, and click Upload; you'll be given the option to employ your new .csv upload to either fully supplant the existing information or suspend to it. Equally soon as you update the dataset, any forms to which that dataset is fastened will exist automatically updated. Y'all won't need to upload updates to the forms themselves.
2. 2nd, your datasets can exist updated automatically, with data streaming in from other forms. Meet Publishing form data into server datasets for more on that possibility.

Format of pre-loaded data

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

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 incorporate 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 instance, you will demand to manually supercede the column separators with commas, or save your data using a different program.

3. At to the lowest degree one column should uniquely identify each row of pre-loaded data. Such columns will be used at survey time to expect upwards which row's information 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 cavalcade proper noun (equally in "hhid_key"). Any columns with names ending in "_key" will be indexed for faster expect-ups on your survey devices.

Doing the bodily pre-loading

The easiest way to use pre-loaded data in your survey grade is to bespeak that you'll exist using pre-loaded data when you lot start a new grade from the Design tab. In the Your forms and datasets section, click + so Start new form, enable Avant-garde options, and marking the checkbox next to Car-generate fields necessary for pre-loading data; when prompted to choose your pre-loaded information, select a .csv file or server dataset to requite an example of how your pre-loaded data will look (information technology doesn't have to be the last data, just information technology should accept the correct columns); confirm that the data looks correct; place 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 outset out with all of the fields necessary to pre-load your data.

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

In the class designer, click to + Add subconscious field, select calculate as the field type, and click Configure to add a field that you lot want to pull into your survey. Adjacent, give your field a proper noun, click Specify with a wizard for the calculation, and select Pull pre-loaded data from attached dataset or .csv file. Finally, adhere the appropriate .csv or dataset, specify the column name for the column y'all desire to pull into your course, specify the cavalcade name for the column yous want to use for looking upwards the appropriate row, and indicate which form field has the value to use in looking up the row. The wizard volition save the appropriate calculation for you, and yous can then refer to this new calculate field whenever you want to refer to the pre-loaded information elsewhere in your form.

In the spreadsheet course definition, add together one summate field to the type cavalcade of the survey canvas for each data field that you lot desire to pull into your survey. Give that field a name, and use the pulldata() office in its adding column to signal which field to pull from which row of which server dataset or .csv file. Here is an instance:

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

This case expression volition pull the value from the plot1size column of the fastened dataset with ID hhplotdata – or from the directly-attached .csv file named hhplotdata.csv, if in that location 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 architect is bachelor as a tool in the Your forms and datasets section of the Design tab. In one case you lot run the builder, choose Pull pre-loaded information from attached dataset or .csv file as the calculation blazon, so enter your details and click Build. You lot can just re-create 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, y'all can reference that field in subsequently relevance or constraint expressions, or in other fields' labels. You can refer to your pre-loaded field as ${fieldname}, just as yous 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 yous can then reference in labels, constraints, or relevance expressions. Instead, yous may want to pre-load information directly into a visible, user-editable field. That is also possible.

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

In the spreadsheet form definition, just include the proper pulldata() expression in the adding cavalcade of the editable field. That's basically it: you format the pulldata() expression in the same fashion described to a higher place, and you lot put it in the adding column, also in the aforementioned way as above. The only difference is the field type of the row in which you place the expression: hither, information technology's a regular, editable field blazon rather than a calculate field.

There are a few other things to consider when dynamically loading an editable field'south default value, yet, 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 volition demand to save in UTF-8 format in order for mobile devices or web browsers to return the text correctly. If yous cannot straight save or consign to a .csv file in UTF-eight format, y'all take two options.

   1. Use an .xlsx file or Google Canvas instead of .csv. When you attach an .xlsx file or a Google Canvass, your server volition automatically catechumen it to .csv with the correct grapheme encoding (UTF-eight). If the file you're trying to attach has multiple sheets, yous will first need to select which sheet to use, and so select the option to convert to .csv.
   2. Apply SurveyCTO Desktop to re-encode it: cull Re-encode .csv from the Offline grade tools menu, select your file and the encoding for which its text appears correctly in the preview window, so click Catechumen to save the re-encoded .csv file.
   UTF-viii-encoded .csv text should and so appear properly, provided that your device or spider web browser supports the font or linguistic communication that you're using.

2. Even when they are numbers, data fields pulled from a dataset or .csv file are considered to exist text strings. Thus, yous may sometimes need to use the int() or number() functions to convert a pre-loaded field into numeric course (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 cavalcade, then be certain 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 information technology directly to your form, yous tin can compress it into a .cipher annal before uploading it. Simply compress one or more of your class'southward support files into a .zip file and upload that .zip equally you would unremarkably upload a .csv or other support file. When your course is loaded, the back up files volition be automatically unzipped.

Finally, yous tin also employ pre-loaded information 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.

hindmanmaxuld.blogspot.com

Source: https://docs.surveycto.com/02-designing-forms/03-advanced-topics/03.preloading.html

Share this post