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.
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:
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).
The "Parent Cases" sheet on case exports is not supported.
Nested repeat data in forms is not fully-supported.