The Excel Query File

Child pages:

Quick Start

The easiest way to get started with the Data Export Tool is to have CommCare generate a query file for your data models based on an export. See https://dimagi.atlassian.net/wiki/x/JCrKfw for details.

If you prefer to start from scratch, We have provided a template query file here which can be used to build your query files.

See below for more details on the terminology and fields:

• Data Source: (Required)
  This determines whether you are exporting case, form, or messaging-event data.

• Filter Name: (Optional)
  When you include the “Filter Name” and “Filter Value” columns, you can limit the set of forms or cases returned when you run the DET. 

  • Forms: By default, if your data source is “form”, every form submitted to a project space will be returned. To select a particular form type, set “Filter Name” to xmlns.exact and “Filter Value” to your form's XML namespace. 

  • Cases: If your data source is “case”, every case in a project will be returned. To download one particular case type, set “Filter Name” to type and “Filter Value” to the name of the case type you'd like to download. See additional case filtering fields here: https://dimagi.atlassian.net/wiki/x/NDHKfw.

• Filter Value: (Optional)
  Filter Values determine which forms or cases will be processed and stored. 

  • Forms: When exporting forms, you can find the XML namespace for a form by going to your form > Advanced > View (Source XML) and look for the address in the header of your XForm.
    See additional form filtering options here: https://dimagi.atlassian.net/wiki/x/VjHKfw. Filter Names are paired with Filter Values to filter the results you request from CommCareHQ. Results are limited to the set of forms or cases whose Filter Name property matches the value listed in this field.

  • Cases: When exporting cases, the Filter Value is the Case Type listed on your case's settings page. You can pull all cases and store them in the same table or filter one case type at a time into a specific table.

• Field: (Required)
  Each row of this column specifies the name of one output field.
  Note that a field named “id” is required for use with a SQL database.
  Note that there is no maximum number of output fields.

• Source Field: (Required)
  Each row of this column specifies where the data of the corresponding Field should be extracted from. Note that form properties must be prefixed with “form”. and case properties must be prefixed with “properties”.
  (See more details about the proper format for Source Fields below in the How to Reference Fields in Forms and Cases section).

• Map Via: (Optional)
  This is only relevant if you're exporting to SQL database. 
  This column allows you to convert strings to other types, e.g.:

  • Str2date converts to datetime column

  • Str2num converts to integer column 

• Table Name: (Optional)
  By default, the table name is taken as the Excel Sheet name. This can be overridden by adding this column with a single value in the column for the table name.

Advanced Usage

• Data Types

  • When exporting to a SQL database you can also explicitly set data types by adding a column with the heading "Data Type". See Data Types in https://dimagi.atlassian.net/wiki/x/cgrKfw.

• Data Source
  Advanced Usage: You may specify a deep JSONPath into the data to iterate over repeats. For example, if you have a repeat called "child" in the root of the form, the data source would become "form.form.child". Also, note that fields are relative to the source, not absolute. See the DET Tips and Tricks page for more information.

• Alternate Source Field [N]: (Optional)
  In instances where different source fields should be placed in the same output field (for example, when renaming fields in the app), alternate source fields can be specified. Each Alternate Source Field.
  The column must be named Alternate Source Field [N] where "[N]" is replaced by a number. For example:

The source fields are each considered in order, and the first one that appears in the data is used (even if its value is empty).