When selecting the Microsoft Excel export format, you can select an earlier Excel version if you don't have Microsoft Excel 2007 or later. However, please note that the Excel 2003 format may be discontinued in the near future.
Each group level is exported as a separate worksheet. The first three columns of each file/worksheet contain key metadata about the submission:
- [Submission Id] – a system generated identifier which is unique to that submission.
- [Fieldworker Name] – the name of the fieldworker who uploaded the submission (based on who was assigned the handset at the time).
- [Fieldworker Id] – a system-generated identifier which is unique to that fieldworker. Two fieldworkers with the same name will still have unique identifiers.
Apart from the actual response data worksheets, each export includes a number of additional worksheets:
- The “Submissions” worksheet contains a summary of all submissions which are included in the export. Only metadata is included.
- The “Codebook” worksheet lists every field used in the form. Each field receives a unique ID from the system. For select-type field, each option is also listed along with its associated value.
- For reference purposes, a “Questions” worksheet is generated which lists each field contained in the form, the field's system-assigned ID, and the text displayed and the field type.
- A “Raw Data” worksheet is produced which is useful for importing the response data into other databases or performing other types of transformations.
If you need to, you can specify one or more fields to appear in every worksheet by using column fixing on the Grid tab.
For repeating groups there are additional columns in the relevant worksheet:
- [Repeats On Question] – this is only relevant to legacy users and will usually be blank.
- [Repeat Question Value] –this is the number of times the group was repeated for a particular submission. It is thus the number of rows which will be generated for that submission in that particular group.
- [Repeating Index] – indicates the repeat number of the row in question. This value is zero-based, i.e. the first repeat is 0, the second is 1 and so on.
- [Path] - indicates the relative instance number (in square brackets) and the absolute instance numbers (in round brackets). E.g. if you have a repeating group called 'HH_Roster' where you enumerate all the household members, and a repeating nested group inside it called 'Children', an entry of "/Household_Page/HH_Roster(4)/Children(8)/Info" means you are looking at the information of the second child of the fourth household member - this is the eighth child overall for this submission.
Multi-select option naming
There are two options which can be used to determine the naming convention for multi-select field types. When exporting multi-select fields, a column/variable is generated for each available option which is then represented as "selected" (1) or "not selected" (0).
Assume you have a multi-select field "HOUSEHOLD_ITEMS" with 3 options: Television (value: TV), Radio (value: R) and Fridge (value: F):
- By selecting "Append option values to field name", the column/variable names generated for each option will be labelled by appending the relevant option's value to the field name. E.g. "HOUSEHOLD_ITEMS_TV", "HOUSEHOLD_ITEMS_R", "HOUSEHOLD_ITEMS_F".
- By selecting "Append option text to field name", the column/variable names generated for each option will be labelled by appending the relevant options's text to the field name. E.g. "HOUSEHOLD_ITEMS_Television", "HOUSEHOLD_ITEMS_Radio", "HOUSEHOLD_ITEMS_Fridge".
Single-select option naming
There are also two options which can be used to determine the type of data exported for single-select field types. When exporting single-select fields, you can choose whether the selected option is exported using the text or value of the option.
Assume you have a single-select field "HOUSEHOLD_ITEMS" with 3 options: Television (value: TV), Radio (value: R) and Fridge (value: F):
- By selecting "Use the selected field's text", the option's text will be exported. E.g. "Television" is exported if you selected "Television".
- By selecting "Use the selected field's value", the option's value will be exported. E.g. "TV" is exported if you selected "Television".