Form Data Export

Owned by Jeremy W.
Last updated: May 16, 2024 by Kai Cowger
12 min read

Back to https://dimagi.atlassian.net/wiki/x/5SbKfw.

This export functionality provides the ability to download data from the forms submitted to CommCare HQ. You should use this feature when you are interested in measuring an outcome across beneficiaries across time. Applications that track a beneficiary over time will usually collect data in different forms at different points in time. This Form export will capture all of the changes in the data at these different time points. 

Location: CommCare HQ -> Data -> Export Form Data

This page is divided into five main parts:

Creating New Form Exports

A form export allows the user to set up a download of a form's data. Form exports can later be viewed and downloaded by other members of the project space.

In order to create an export, you can follow the steps below:

  • Next to the form name in the "Export Form Data" section, click on the "+ Add Export" button.

  • You can select which form you would like to export by selecting from the following options:

    • App type (in general you leave this as Applications)

    • Application

    • Menu

    • Form

  • Once you have selected your form, you will see the number of submissions that are associated with that form:

  • Click "Add Export" to navigate to that export's edit page, where you can manipulate more settings:

Export Settings

  • Change the name of your export in the "Export Name:" field. The will be the name displayed in the list of exports and should use keywords to identify the type of information in the report.

  • Select the desired default file type using the drop-down option.  There are four types available: Excel 2007, Excel (older versions), CSV (Zip file), Web Page (Excel Dashboards).

Additionally, there are five options that can be selected by checking the boxes:

  • Automatically convert dates for Excel: Leaving this checked will ensure dates appear in Excel format. Otherwise, they will appear in a normal text format.

  • Automatically format cells for Excel 2007+: If this setting is not selected, your export will be in Excel’s general format. If you enable this setting, Excel will format dates, integers, decimals, Boolean values (True/False) and currencies.

  • Create a Daily Saved Export: By checking this box, the saved export will be pre-processed once every 24 hours and available for immediate download on the main Form Exports page. After 35 days without downloading the export will stop updating, but you can press the "Update Data" button to resume daily updates. 

  • Include duplicates and other unprocessed forms: If every variable collected by the mobile user is the same, CommCare flags these submissions as a "duplicate." This can happen when there is poor connectivity, so CommCare mobile submits a form multiple times before it receives confirmation that a form has reached the server. By checking this box, there will be duplicate form submissions in the export download.

  • Expand Multiple Choice Questions: if you have checkbox questions in your app this setting will create one column for every possible answer. 

Form Settings

  • In the first column, the field (data point) can be re-ordered by dragging the box up or down. The order that is selected on this page will correspond with the order in which the variables are displayed in the export file. You can also select the fields you want to include in your report by using the checkboxes. 

  • In the second column, the fields shown represent the data collected by the mobile user, the application, the phone, and the server where the data is stored. 

    • Show Deleted Questions and Advanced Questions: If selected, the export will allow you to see advanced questions and/or questions that have been deleted from the current version of your application. Advanced questions include metadata like case updates and server information.

  • In the third column, change field names which will update the title row of the data export. By default, the display column will repeat the field name from the second column which is referred to as question ids.

  • You are also able to select and sort multiple rows simultaneously for easy management of form data. (Note: this is only on Standard plans and above)

    • To drag multiple fields, select by clicking while holding the CTRL key on a PC, or the Command key on a Mac.

    • To select a range of fields hold the Shift key and select the first and last field in the range.

    • To unselect a field hold the CTRL key on a PC, or the Command key on a Mac, and click.

Privacy Settings

This allows the user to select form data to be de-identified. This means that when the data is exported to an excel sheet, the columns will still be in the data export but the data values will not contain personal information that can be tracked to a single beneficiary.      

  • Click “Allow me to mark sensitive data” and another column called “Sensitivity” will be added to the Form table.

  • A drop down box will appear next to each field name. A field can be marked as “Sensitive ID” which can be used for all text or numeric fields such as name or age. Alternatively, a field can be marked as “Sensitive Date” which would be used for date of birth. Finally, a field can be left blank and the data will export directly as it was input into the application.

  • Once you have marked the sensitive fields, scroll all the way down to Privacy Settings and check the box "Publish in De-identified Export"

  • Create: Click "Create" to finish the export creation process.  You're done!

  • The export will now be available on the Export Forms page

Downloading Form Exports

The Export Forms page shows the exports you or others have created:

            Name: The name you titled the export

            Edit: Edit your export to change the data included in that export

            Export: Download the export

            Bulk Export: Download your exports in bulk. Each export will be downloaded as an individual sheet in a single Excel workbook

In order to download an export, click "Export"

  • Once you have selected your filters, click "Prepare Export"

  • Click "Download" and your export with the filters you have selected will be downloaded!

Bulk Form Management

Bulk Forms Export

Purpose: Allows a data manager to track the data across forms in one single excel document. It further increases the scope of data analysis and allows more in-depth study.

The bulk forms export gives you the option to download all form exports in bulk - i.e all forms data can be downloaded into one excel workbook with each tab in the excel file representing each form in the application.

These are the steps to create a bulk forms export

  • In the Export Dashboard you will find a check box to select all or select specific export

  • On checking the box, you will notice a "Bulk Export" button becomes available on the right side of the page

  • Click on the "Bulk Export" button, and all selected exports will be downloaded to a single excel workbook

 

 

Note: Bulk exports will only export the first tab from each export. Tabs containing repeat or case history data will not be included.

Bulk Delete Forms Export 

The bulk delete form export gives you the option to bulk delete multiple exports at once. 

These are the steps to bulk delete from export

  • In the Export Dashboard, you will find a check box to select all or specific exports. On checking the box, you will notice a "Delete Selected Export" button becomes available on the left side of the page

    • Click on the "Delete Selected Export" button and you will be prompted to confirm the bulk delete action

    • Once Delete Export is clicked it will bulk delete the exports selected at once

Types of Forms Exports

Multimedia Exports

Downloading Captured Media Files

Media files in CommCare include files captured from Image, Audio, Video, and Signature questions. These files are referenced in standard form exports, but the multimedia file is not displayed outright. When exporting a form that captured multimedia, that question in the export will contain a link rather than the multimedia file itself. Clicking this link will allow you to either download the multimedia or will open a link containing the multimedia element, depending on the kind of file.  Ways to download multimedia directly are as follows:

If you want to download a single multimedia file, you can locate the form it is associated with as described in https://dimagi.atlassian.net/wiki/spaces/commcarepublic/pages/2143958321.

1. To start, save the export you would like to download (as you would in Part B above). Be careful to include the multimedia field by adding check-box next to it.
2. Scroll down on your form exports page until you see the Export you want to download. Select the blue export button
3. Now you have arrived at the download form export page. there are two buttons. 1) prepare export (deep blue) and 2) prepare multimedia (light blue). Pictured below. 
4. Select the "Prepare Multimedia" button, it will process, and the button will turn green. Select "Download Multimedia"

You can download all of the media files attached with a form from the Export Forms page on CommCareHQ:

Any form with associated multimedia files will have a Download button accessible. When you download the multimedia, it will appear as a zip file onto your computer, each photo will be a unique JPEG. These media files will be named in the format question ID-username-form id

Example: [casename]-[question_id]-[username]-[form_id].jpg

Example: johnm-head_of_household-supervisor5-e9b4cdfa-c6a2-8607-b4e8-9839493ca2de.jpg

To identify which form submission a multimedia file came from, use the form_id (in green above). Each form submission has a unique form_id.

Repeat Data in Form Exports

Overview

When you create a form export you have the option to include data that was captured in repeat groups. These are in a separate section of the export setup page, and you need to click on the checkbox to include this data in your export: 

After downloading the export you will see that the data for each type of repeat group is stored on a separate tab in your Excel file. A special column called row.number is used to link the repeat data with the main form submission. 

If you need to link the repeat data to a specific form submission you must include row.number in both the general form export and the repeat group export.

Using row.number

The row number will be your key to determining which general form data is associated with a given row in the repeat data.

In the main form export you will see that row.number just contains an increment count (1, 2, 3, 4) with one unique number per row.

In the repeat group tab row.number will contain a number in the format:

x.y

  • x is the number of the row from the main form export that the row in the repeat group is related to

  • y is the number of the count of the repeat group

So if you had 2 form submissions, and each time the repeat group was filled out twice you would expect to see the following:

Main Form Submission (first tab)

number

Note

number

Note

0

First Form

1

Second Form

Repeat Data (second tab)

number

Note

number

Note

0.0

First repeat group from first form

0.1

Second repeat group from first form

1.1

First repeat group from second form

1.2

Second repeat group from second form

You can use Excel functions like VLOOKUP to pull data from one tab to the other. For example, if you wanted to use a piece of data from the main form submission in a list of the repeat group data you could look up the row number from the first tab and pull over the necessary info.

Nested Repeat Groups

If you have multiple repeat groups, nested one within the other, then you will notice that the row.number reference includes more levels. 

For example, if you a form with one repeat group (houses) and then another group within that (house members) you might see a row.number in this format for the house members repeat group tab:

x.y.z

  • x is the row from the main form

  • y is the row from the house repeat

  • z is the number of the repeat from the house members repeat group

By referring to these components you can find the different levels of relevant data.

Common Questions Regarding Data Exports

What is the difference between "---" and blank values in form exports?

1. Seeing "---" in a form export

If you see "---" in a form, this means that a question was never presented to the end user. For example, if you have a question that had a display condition that was not met and therefore the question was hidden completely from the mobile worker, including that field in a form export will show "---" as the value, i.e. questions skipped logically/automatically by the app show up in exports as "---" 

This also happens when the mobile user is not using the latest version of an application in which question ids or the application structure have been changed, hence the user never sees the latest questions and the data appears as "—". For more information, see https://dimagi.atlassian.net/wiki/x/8STKfw. 

2. Seeing blank in a form export

If you see a completely blank cell in Excel for a question, this means the question was presented to the user but not answered. This is different from "---" because the question was shown to the user, i.e. questions that the mobile user saw but decided to skip on their own show up in exports as an empty value, " ".

Error Messages In Exports

Multimedia Exports

You may notice this error message when trying to export multimedia:

 This indicates that the file size is too large, and you should reattempt downloading the multimedia export by changing your filters to reduce the file size. Due to limited system resources, we are able to support multimedia exports only up to 5 GB in size.

Some of the measures you could take to reduce the file size of the export are:

  1. Reduce the date range of the export to a shorter range, hence narrowing down the number of multimedia files being exported at a single go.

  2. In the "User(s)" filter, try narrowing down to a few users instead of selecting all "Active Mobile Workers"

Generally, in case your application collects a lot of images, it is good practice to regulate the size of your images in the formbuilder:

  • Selecting the image size will enable you to reduce the size of images that are captured, as per the selected size on formbuilder.

For more information on images in CommCare, please see https://dimagi.atlassian.net/wiki/x/6wfKfw.

Form Exports

You may also sometimes encounter an error mentioning: 

This export contains 101865 rows. Please change the filters to be less than 100000 rows.

This means that the number of rows exceeds what we're able to successfully support via the export UI. You can try to reduce the date range of the export and download in batches, or you can also add filters to reduce the number of users the export is covering.

Alternatively, you can use other methods we have available to export your Form Data in case you have a large data set. Please see https://dimagi.atlassian.net/wiki/x/BibKfw for more information on selecting tools for data exports on CommCare HQ.