*! version 1.1 Anna Reuter 18mar2021

Title

odk2xls -- Convert an xlsForm questionnaire written in xls/xlsx into a readable questionnaire in Excel.

Syntax

odk2xls
using
filename,
to(outputfile)
[options]

options

* to(outputfile) name of the resulting xlsx file

Item and column options
keep(colnames) keep columns colname_1, ..., colname_k

droptype(types) drop questions of type type_1, ..., type_k

dropvar(items) drop question items named item_1, ..., item_k

max(int) only keep answer lists with length int (default: 30)

fill display requested input type below open-ended questions

Label options
delete(string) delete substrings from questions and answers

clean clean Markdown and xlsForm code

mark(multiple|single|both) mark questions as multiple or single select

Output options
tfmt(fmtopts) format table header

qfmt(fmtopts) format questions

afmt(fmtopts) format answers

replace replace existing file

* to is required.

Description

odk2xls transfers xlsForm-based questionnaires written in xls/xlsx into readable questionnaires and stores them as Excel files.
It was developed and tested for xlsForms written for ODK.
Note that the formatting options might increase the computation time for larger questionnaires, especially if cells should be colored.
See odk2doc for a conversion into docx files.

Options

to(outputfile) specifies the destination file of the resulting questionnaire.
outputfile can have the file extension .xls or .xlsx, but the formatting options might not work properly when specifying xls.

keep(colnames) specifies which xlsForm columns should be kept (based on their names in the xlsForm). The columns
type, name and label* are always kept. colnames might be abbreviated, e.g.
"constr" to display column constraint and all existing constraint_message
columns. Columns will be displayed in the order they were entered.

droptype(types) specifies which question types should be dropped. Groups
and repeats can be dropped by entering "group" and "repeat".

dropvars(items) specifies which items (as specified in column “name”) should
be dropped. Abbreviations (e.g. food*) are supported, but not namelists (e.g. food1-food5).

max(int) specifies the maximum length of answer lists, i.e. the maximum number of elements such a list can contain.
Answer lists which are longer are dropped. The default maximum length is 30.

fill adds a row below questions of type integer, decimal, range, text, image, and geopoint,
as well as below select questions with trailing "or_other" to display the requested input type.
If the question is of type integer, decimal, or range, and constraints or parameters were specified, these will be displayed below the question.
If the question contains a trailing "or_other", "Other, specify" will be displayed as an additional answer.
In all other cases, the requested input type will be displayed instead of an answer, e.g. "[Text]" for questions of type text.

delete(string) specifies which substrings in labels (i.e. questions and answers) should be deleted. Put
the substrings in double quotes if the style attributes contain quotes. Note that
option clean already deletes Markdown language.

clean deletes Markdown and xlsForm language from question and answer labels. More specifically,
all “#”, ”*”, “_”, and spans (e.g. <span style="color:red"> and </span>) will be
deleted from the questions and answers. Item referrals ($) will be replaced by "____" to simulate
a fill-in gap.

mark(multiple|single|both) adds to all labels of multiple select options the phrase "[Multiple select]",
respectively to all labels of single select options the phrase "[Single select]".

tfmt(fmtopts) formats the table header (i.e. the first row)
according to the options provided by the putexcel command. The formatting options might not work properly when specifying xls as file extension.

qfmt(fmtopts) formats all questions
according to the options provided by the putexcel command. The formatting options might not work properly when specifying xls as file extension.

afmt(fmtopts) formats all answers
according to the options provided by the putexcel command. The formatting options might not work properly when specifying xls as file extension.

replace specifies that the output file should be replaced, if such a file
already exists.

Example

Create a plain xlsx questionnaire from the xlsForm "Example_xlsForm"

. odk2xls using "Example_xlsForm", to("Example_converted.xlsx")

Keep the columns "relevant", "constraint_message::English (en)" and "constraint_message::Deutsch (de)". Drop the question named "comments". Replace the existing file.

. odk2xls using "Example_xlsForm", to("Example_converted.xlsx") keep(relevant constraint_m) dropv(comments) replace

Display required output below open-ended questions. Delete the words "Introduction" and "Vorstellung" from every question and answer. Clean the mark-up language and mark multiple-select questions.

. odk2xls using "Example_xlsForm", to("Example_converted.xlsx") keep(relevant constraint_m) dropv(comments) fill del("Introduction" "Vorstellung") clean mark(multiple) replace

Create a fully formatted questionnaire using the formatting options provided by the putexcel command.

. odk2xls using "Example_xlsForm", to("Example_converted.xlsx") keep(relevant constraint_m) dropv(comments) fill del("Introduction" "Vorstellung") clean mark(multiple)
tfmt(border(top) bold) qfmt(fpattern(solid,lightsteelblue)) afmt(italic) replace

Author

Anna Reuter