About the DET

The Data Export Tool does not change your data in CommCare. It simply exports the data into a raw format that you can use for data insights and analytics using the CommCare API to export the data. The configuration for what data is exported and how it is formatted are provided to the tool via an Excel configuration file. The export can be in the format of a file (Excel, CSV, or JSON) or to a specified database. You can configure the DET to pull data on a schedule to automatically keep your data up to date or run it as a one-time data export.

Using the DET requires knowledge of both Python and SQL, and for this reason, is an advanced feature.

Important note about PHI: Please note that the commcare-export tool offers no security features for dealing with Protected health information (PHI) directly. If you intend to export PHI data from CommCare, please ensure the appropriate measures are in place to protect your data.

DET Example Use Case

Haik is a data analyst for a child immunization NGO specializing in tracking polio vaccinations in developing countries using CommCare. The increase of vaccination supplies and the training of competent healthcare workers to administer the vaccinations have significantly improved the child immunization program. More children are getting the vaccinations that they need. 

The program's success is leading to a considerable increase in the data collected. 

Haik needs to export data regularly to analyze and visualize it for the weekly reports. Manually exporting the data from CommCare HQ is not sustainable. Haik needs a way to export data in bulk in an automated manner. 

The CommCare Data Export Tool will help Haik. He can use the DET to pull data from one or more domains and combine data into one large data file, or database, for analysis. Below is an example dashboard that Haik may make with DET.

Open

Example dashboard configured with CommCare DET

Technical Requirements

Using the DET requires knowledge of both Python and SQL, and for this reason, is an advanced feature. You a can learn more below about the requirements for using both of these tools.

Python

More information about setting up Python for the DET.

To start, the DET requires you to download Python to your computer. You will use a command-line interpreter tool to run the commcare-export script from the Python script library. You need Python installed to use the commcare-export script. The script library is installed with Python. Installing the script allows the Data Export Tool to export your data from CommCare HQ.Below is a video walking through this process.

The Excel Query File

Read more about about setting up the Query file for the DET.

Overview

A Query file is an Excel file (.xlsx) saved to the Python Scripts folder. 

• The Query file specifies which data objects should be requested from the API and which values should be stored when the API payload is received. All other values that are not specified are discarded and not stored in the results set.

• One query file can contain many tabs and can combine case and form data queries.

It’s common to have a single query file with one tab for each case or form that you want to export.

A template query file is provided here for cases and forms. 

• demo_query_file_case.xlsx

• demo_query_file_form.xlsx

You must provide the path to where the query file is saved in the commcare-export command.

E.g.,commcare-export --query /home/username/Documents/my-query-file.xlsx

Below is more information about this process.

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 “Creating an Excel Query File in HQ” Below 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).

Creating an Excel Query File in CommCare HQ

The easiest way to create a query file is to have it generated by CommCare.

Any Export from the Data → Export Form/Case Data tab in CommCare can be used as a basis for an Excel query file. To generate a query file for any export, check the box that says "Generate a Data Export Tool config file".

Then, on the exports list page, a button labeled "DET Config" with a download icon will appear with the option to download the configuration file. This file will be pre-configured with the right filters and properties to export the selected data.

Open

Mappings and Tips

The following mappings are used to convert from the export to the query file:

The "sheet name" from the export will be used as the table name in the config file. It is recommended to edit this from the default "Cases" and "Forms" to something more meaningful like "pregnant_women" or "household_registrations".

The "Display" property will be used as the SQL column name.

For best practices and maximum SQL compatibility is recommended to remove all special characters from both sheet names and "Display" properties, and to make the fields lowercase.

Known Limitations

The following limitations apply to any query files generated in this manner:

1. Any "special" transformations - for example, pulling an owner name from an owner_id are not yet supported. These values will be pulled down as the raw underlying data (e.g. the ID).

2. The "Parent Cases" sheet on case exports is not supported.

3. Nested repeat data in forms is not fully-supported.

How to reference Fields in Forms and Cases

One of the easiest ways to confirm form and case field names is by looking at the column name in a regular form/case export from CommCareHQ.

In general, the name of form and case fields matches those that you wrote when you created your application, with predictable modifications. When referencing properties in the Source Field column, ensure that you've typed them in exactly as they're defined in CommCare - case-sensitivity matters.

• Top-Level Form Fields: A field named FIELD in a form is referenced by form.FIELD. For example, the field age is referenced by form.age in an Excel query file.

• Top-Level Case Fields: A field name FIELD in a case is referenced by properties.FIELD. For example, the field delivery_date is referenced by properties.delivery_date in an Excel query file.

• Nested Fields: A field nested more deeply within a form or case may be referenced by separating each level of nesting with a period. For example, in the demo_query_file_form.xlsx, you will see the nested field form.delivery_information.delivery_type, which will pull out the value of the delivery_type question inside the group delivery_information. This will always match the structure of your application.

Aggregation and Calculation Functions

The following are functions that you can use to change the value of a column. They can be added to the Map Via column in the Excel query file.

Exporting User and Location Data

The --users and --locations options export data from a CommCare project that can be joined with form and case data. The --with-organization option does all of that and adds a field to Excel query specifications to be joined on.

Specifying the --users option or --with-organization option will export an additional table named 'commcare_users' containing the following columns:

The data in the 'commcare_users' table comes from the List Mobile Workers API endpoint.

Specifying the --locations option or --with-organization options will export an additional table named 'commcare_locations' containing the following columns:

The data in the 'commcare_locations' table comes from the Location API endpoint along with some additional columns from the Location Type API endpoint. The last columns in the table exist if you have set up organization levels for your projects. One column is created for each organizational level. The column name is derived from the Location Type that you specified. The column value is the location_id of the containing location at that level of your organization. Consider the example organization from https://dimagi.atlassian.net/wiki/x/NjPKfw . A piece of the 'commcare_locations' table could look like this:

In order to join form or case data to 'commcare_users' and 'commcare_locations', the exported forms and cases need to contain a field identifying which user submitted them. The --with-organization option automatically adds a field called 'commcare_userid' to each query in an Excel specification for this purpose. Using that field, you can use a SQL query with a join to report data about any level of your organization. For example, to count the number of forms submitted by all workers in each clinic:

Count forms by clinic

Note that the table names 'commcare_users' and 'commcare_locations' are treated as reserved names and the export tool will produce an error if given a query specification that writes to either of them.

The export tool will write all users to 'commcare_users' and all locations to 'commcare_locations', overwriting existing rows with current data and adding rows for new users and locations. If you want to remove obsolete users or locations from your tables, drop them, and the next export will leave only the current ones. If you modify your organization to add or delete levels, you will change the columns of the 'commcare_locations' table, and you will likely want to drop the table before exporting with the new organization.

Exporting Messaging Event Data

As of version 1.7.3, data from CommCare's SMS messaging events can be exported using the Data Export Tool. 

To export data, change the "Data Source" to "messaging-event".

Example Query File

Open

API Fields

Please see here for the full list of messaging event API response fields.

Exporting One Row Per Message

When "Data Source" is set to "messaging-event" the export will contain one row per messaging event. Since each messaging event may have more than one message, this may result in multiple messages per row, which can be difficult to analyse.

To export one row per message, change "Data Source" to "messaging-event.messages.[*]". This will result in each message being exported to its own row.

When "Data Source" is set in this way, you can access data from the parent messaging event by prefixing the fields with '$.'

Note: if there are no messages then the event will not be exported at all. To work around this, you can use the “--export-root-if-no-subdocument” command line option which will result in a single row being exported for the event if there are no messages.

Additional Information

For information on the message event itself and its data fields, please see API at https://dimagi.atlassian.net/wiki/x/6jHKfw.

Exporting Data

You have the choice to export to a file(Excel, CSV, JSON, etc.) or a SQL database. Exporting to Excel files and Markdown is primarily used for testing. Exporting to SQL is more commonly used. 

Export to an Excel file

Exporting to an Excel file allows you to get a full results-set without setting up a local database. When you export to Excel, there are no checkpoints. It’s all or nothing. If the query fails, the Excel file is not created.

You can run a data export and save it to an Excel file with the DET. Excel files are great for one-time data exports or if you want to use them to analyze and visualize the data. Excel is also a great option if you are not familiar with working with databases.

The downside to Excel files is that they can’t handle large datasets. You can also only do once-off export to an Excel file; there are no “checkpoints” created for incremental updates when the DET is run. 

Export to a Database

Exporting to a database supports incremental updates. When you export to a database, you can have millions of rows of data, so the DET handles it in batches. 

After each successful batch, the tool writes a checkpoint to a database table that the DET maintains.

By default, each time the commcare-export command runs, it will only import results that were modified after the most recently imported form or case. This means that, for very large datasets, you do not need to export a full set of results every time. 

You can configure the DET to pull data on a schedule to automatically keep your data up to date or run it as a one-time data dump.

Exporting to SQL allows you to clean and manipulate the data before you integrate it with data visualization tools like Power BI or Tableau.

By choosing "--output-format sql" and "--output DATABASE_URL" in your command, you can save the output to a SQL database. 

The term DATABASE_URL is a standard URL format for databases in these commands. If, for example, you have a PostgreSQL database called “registrations” that is hosted on db.example.com that you would like to save into instead, the URL might look like postgresql://db.example.com/registrations (you may need to include username and password in the URL).

Checkpoints

• The first time you run a DET query to a database, it will get all the records that exist and then set the checkpoint at the end.

• The next time you run the query, it will just start from the last checkpoint and get all the cases modified after that time or forms submitted after that time.

• This checkpoint data is stored in a table called “commcare-export-runs” in the database.

• If you change the query file, the DET will know that it’s a new query file because the MD5 hash will not match. This triggers a full refresh of all data in the domain. See https://dimagi.atlassian.net/wiki/x/OTDKfw for more information.

• A checkpoint will get set after each batch of data that is fetched from the API. Using the --batch-size=number command-line argument will change how much data is fetched in each batch and, therefore, the frequency of the checkpoints.

Technical information for exporting to SQL

Note that a field named id is required for use with a SQL database.

Exporting to SQL requires installing additional python libraries. These are listed in the table below.

Supported Databases

Data types

Data Type Detection

By default, the data export tool will choose the data type of the SQL column based on the incoming data. Strings, integers, and boolean values are supported.