Abt Associates, an EpiSurveyor user, has created a tool to allow the offline creation of a form in Excel, and then transforming the form to EpiSurveyor's svy format for import into the EpiSurveyor website.

The instructions from Joe Contini, of Abt, are below, along with the excel attachment. You can contact Joe with questions at Joe_Contini@abtassoc.com

 

Attached is the excel file that allows for the design of forms and exports the code for each question. This is loaded with forms we used for Ethiopia.
 
Here’s a quick rundown of the sheets:
 
·         Dashboard          - Form names manually entered. Gives an overview of question count in each form.
·         PHSP_Forms      - This is where the form is designed. More info below.
·         Export                   - This references the information on PHSP_Forms and assembles the code for pasting into the SVY template
·         Rapid Results     - This is just a framework for assembling IF THEN statement for qualitative calculations, eg. ‘Rapid Results’ which are used at the end of forms. Ideally, this form could be used to construct the statements and have them automatically inserted into XML. You can see how I used the concatenate function on PHSP forms (see E204) to get halfway there.
·         Lookup_Info      - I normally have this hidden. It contains some of the lookups required to generate the correct code (red), and selections for dropdown menus (blue).
 
We design using PHSP_Forms like this:
·         Form                     - The form name
·         Indicator              - The variable or unique ID for each question. Labels do not use them; EpiSurveyor randomly generates a unique ID based off of the first 25 characters of text in the label**.
·         Question             - The question text
·         Type                      - Dropdown for the type of question.
o   I created the Yes/No type, which renders a radio button type with Yes/No options predefined.
o   The Rapid Results and Calculation types render only as an integer, as SVY doesn’t support calculations.
·         Calculation/Options
o   Since these types are mutually exclusive, I combined them in one column.
o   A radio button or checkbox type question reads the values on in this cell, separated by one per line (how we’ve been doing it) or by commas.
o   If I could further develop this, my goal would be to have calculations made by entering them in this cell.
·         Req                        - Sets whether the question is required or optional.
 
If you unhide columns G-AA, you’ll see how the above input columns are converted to SVY code.
·         The columns in purple (Wahala,prompt_clean,optionset_clean) check for and strip out characters that would cause the form to crash or not upload.
·         The columns in red reflect the content of the XML tags in every question’s SVY format and parse the appropriate content from the input columns.
·         There is no way to code calculations or skip logic into an SVY. My hope is that something like this could be adopted to parse in XML
·         When new rows are created, the equations need to be dragged into that row before exporting.
 
The Export sheet
·         The column Form to select the form to export (one at a time).
·         Hidden columns C-O reference the G-AA columns in PHSP_Forms and wrap the appropriate XML tags around them
·         Column P concatenates the content of C-O into one cell, reflecting the entire XML string for that question.
·         Selecting all visible rows in column P and pasting into the attached template SVY will allow it to be uploaded to EpiSurveyor.org.

**This results in an outstanding glitch (even if using the EpiSurveyor online interface only) where similarly-worded labels cause a form to crash when loaded onto a phone. I’m able to identify affected labels only by looking at the XML in a text editor. The workaround to date has been to be more creative in naming similar labels.