Are you currently using a different digital solution and want to transition to CommCare? This page walks through how you can think about importing your data into CommCare.

Understanding system architecture and data migration

Partners transitioning from another solution to CommCare may feel the urge to transfer all their historical data into CommCare. However, in most cases, they are primarily concerned with the most recent updated values of the entities they track. This facilitates a smooth transition of future data collection from their previous tool to CommCare. In these instances, Dimagi strongly advises preserving all historical data in secure environments such as cloud-based data warehouses and only importing the latest data into CommCare to ensure uninterrupted future workflows. Historical data can be integrated with BI tools along with data from CommCare for business intelligence purposes.

The reasoning behind this recommendation

The architecture of technology systems varies, leading to differences in how data is processed across platforms. On CommCare, for example, any data collected via CommCare apps in the field are considered “form submissions” - in other words, frontline workers (called mobile users/workers in our ecosystem) login to their CommCare apps and open individual forms and submit them after collecting data for clients or entities they are tracking. Once these forms are submitted, depending on what data model has been designed for that specific workflow, CommCare can create new cases, update existing ones or simply save these submissions on the server. The same underlying principles apply for web apps (CommCare apps running on the browser with no offline capabilities) submissions as well. In other words, data submitted on CommCare may have multiple dependencies.

As partners transition to CommCare, they are usually interested in shifting their current workflows to the new system after a certain cutoff date where they use the last updated values for the entities they are tracking, upload them to CommCare and continue tracking them through CommCare for the remaining life cycle of their projects. The following sections provide a walkthrough for both scenarios: a migration of last updated values to CommCare for continuing the project on the platform and a historical migration of all data (including form content and case properties) into CommCare.

Migrating Last Updated Values to CommCare

High Level Data Flow when switching from another tool to CommCare

Here’s an example:

Partner A is running a community health program tracking pregnant mothers. They are currently using Kobo Toolbox. Steps to follow:

When it is time to switch to CommCare, Partner A will download all their data into a secure environment. These can be robust data warehousing solutions like Snowflake, SQL databases like MySQL, cloud storage like OneDrive or SharePoint.
Partner A will create their data model on their CommCare project space, ensuring case properties are created for all the indicators they wish to track for their clients or other entities via CommCare. This will include building app(s) on CommCare with the corresponding case type, case properties and workflows required for future data collection.

Partner A will then use CommCare’s built-in case importing feature via Excel to upload the last updated values from their previous database into CommCare. This will require them to format the excel file with the corresponding column headers for all the case properties they wish to populate in CommCare.

Let’s assume the partner has collected data for 5000 clients which includes registering them and then following up with them as well. The dark blue section indicates the last updated values for these clients including their basic information like name, age, gender etc.

Since we have created the appropriate data model on CommCare and saved case properties for all these values (like name, age, gender, health indicators etc.), we are now ready to import data from the old system directly into CommCare by using the Case Importing feature.

Once the data is successfully imported into CommCare, Partner A will proceed to use CommCare for future data collection.

Preparing application content and data model for CommCare

Step	Relevant Help Links
Use CommCare’s built-in form builder to create the form content used in the previous tool.	Form Builder
Choose the Case List menu option in the form builder and define case type(s) for the different entities being tracked. Ensure that individual question types match with the format of the data in the previous tool.	List of Question Types in CommCare
Create case properties for any indicators that need to be referenced in the app going forward - these can be indicators that are used for entities or beneficiaries tracked over time.	Case Configuration \| :~:text=Since a case will always,." That is discussed here.
Manually create/bulk upload mobile users or use Organizations feature on CommCare to setup user/location mapping as needed.	User Management Organizations (Locations)
Once the complete app content and data model is recreated in CommCare, run a data cycle for a few entries and submit.

Preparing data from previous system for importing into CommCare


Create and export case data from CommCare depending on the number of case types chosen previously. Select all case properties. Make a copy of this file and rename it to reflect data import back into CommCare.	Case Data Export
Similarly, export the last updated values from the previous tool to an excel file.
Paste data into the relevant columns in the data import file created in step 1.
Follow instructions on the Import Cases via Excel wiki to upload data into CommCare.	Importing Cases Using Excel

Migrating Historical Data into CommCare

Data Assessment and Planning

Identify Data Sources: Determine the systems from which data will be migrated (e.g. Kobo, ODK, excel files).
Data Mapping: Define how data from the existing systems will map to the CommCare structure, including cases, forms, and users.
Data Cleaning: Clean the data to remove duplicates, errors, and irrelevant information.
Setup Database: Load all data into structured tables in a SQL database.

Setting Up CommCare

Project Space: Ensure the CommCare project space is set up with the necessary applications, forms, and case structures.
User Accounts: Set up user accounts and assign appropriate roles and permissions.
Location Structure: If data is mapped to hierarchical location structures, make use of CommCare’s organization feature to set up the location structures.

Data Migration Process

Extract Data: Use appropriate methods (e.g., SQL queries, Kobo API, ODK exports) to extract data from the source systems.
Structured Database: Ensure that all finalised data is saved to a SQL database that can be scaled for larger data sets and provides data transformation and mapping features if needed.

Data Transformation and Loading

Data Mapping and Transformation: Map the extracted data to the corresponding CommCare fields and transform it as needed to fit CommCare’s data model.
Automated Uploads: For dealing with large datasets or requiring regular updates, use CommCare’s APIs or integration tools like Azure Blob Storage
Here's a simplified table listing tools that can be used for this purpose, in order of technical complexity:

Tool	Description	Tech Complexity	Ideal For

Tool	Description	Tech Complexity	Ideal For
Power Automate	Cloud-based service for automating workflows across applications and services.	Low	Low-code/no-code scenarios, simple to moderate workflow automation.
Azure Data Factory	Cloud-based data integration service for creating data-driven workflows.	Moderate	Moderate to complex workflows, data transformation, cloud integrations.
Apache Airflow, Talend, Informatica	Combined category for complex workflows and data integration tools.	High	Complex ETL processes, data quality, flexible and customizable workflows, large-scale enterprises.

Data Validation

Check Data Integrity: Verify that all data has been correctly migrated and is accessible in CommCare.
Validation Testing: Perform tests to ensure data accuracy and completeness.

User Training and Support

Training Sessions: Conduct training for users to familiarize them with the CommCare interface and new data workflows.
Support Channels: Provide support through helpdesks, documentation, and FAQs.

Best Practices and Tips

Consistent Data Structures: Maintain consistent data structures across different source systems to simplify mapping and migration.
Documentation: Keep detailed documentation of the migration process, including data mappings, transformation rules, and validation checks.
Pilot Testing: Conduct a pilot migration with a subset of data to identify potential issues and refine the process before full-scale migration.
Data Privacy and Security: Ensure compliance with data privacy regulations and secure sensitive data during migration.

Common Challenges and Solutions

Data Quality Issues: Address data quality issues before migration to avoid complications.
Data Volume: Manage large data volumes by breaking them into smaller, manageable chunks.
System Compatibility: Ensure compatibility between source systems and CommCare to minimize data transformation complexities.

This tutorial explains how data from other tools can be programmatically migrated to CommCare using Azure SQL Database and Power Automate.

Historical Data Migration Walkthrough

Existing Data Structure

Data can be structured in numerous ways in other tools. Depending on the complexity of this structure, the level of effort involved in translating it to CommCare’s data architecture can vary from high to low. If single entities like clients or households are being tracked, setting up the similar structure in CommCare would be easier compared to a hierarchical structure with multiple related entities and repeat data.

Let’s look at a complex structure to understand how it can be replicated on CommCare. This will also inform approaches for more simpler structures.

Sample Data Structure in Other Tool

Here's a summarized table based on the diagram, detailing the entities, their relationships, and key indicators:

Entity	Relationship	Indicators

Entity	Relationship	Indicators
Household	Top Level Entity	Household ID, Location
Household Member	Linked to Household	Member ID, Name, Age, Relation to Head, Gender
Visit	Linked to Household Member	Visit ID, Visit Count, Health Score, Weight, Vaccinated
Medicines	Linked to Visit	Medicine Name, Dosage

Household is the top-level entity, identifying the group with unique identifiers and location.
Household Member is linked to the Household, with specific details about each member.
Visit records are associated with each Household Member, tracking various health-related indicators.
Medicines and Dosage are specific to each Visit, detailing the medical treatment received.

Setup in CommCare

Entity → Case Type	Relationship → Parent to Child Case Linking	Indicators → Case Properties

Entity → Case Type	Relationship → Parent to Child Case Linking	Indicators → Case Properties
Household	Parent Case	Household ID, Location
Household Member	Child Case of Household	Member ID, Name, Age, Relation to Head, Gender
Visit → Medicines (repeat group in Visit)	Child Case of Household Member	Visit ID, Visit Count, Health Score, Weight, Vaccinated
Medicines	Repeat Group Data in Visit	Medicine Name, Dosage

The output of this setup will look like:

Using the documentation on the help site, this can be easily built as an application in a project space on CommCare.

Sample Application Structure for Example Above

Households Module:

Household Registration and Household Member Registration

Household Members Module:

Creation of visits case which includes repeat group data for medicines

Visits Module:

Followup on Previous Visits

Once the app is created, submit data as a mobile user and save the raw xml for each form creating or updating cases. These will be required in the Power Automate flow when submitting data back to CommCare.

The form submission raw XML can be accessed from multiple reports. This is an example of how it can be accessed and copied from the Submit History report.

Setup in SQL Database

The ideal approach to migrating data from other sources to CommCare requires standardisation of storage in a structured, secure and scalable tool like a SQL DB. As different tools may have different options for data extraction, this tutorial assumes that relevant steps would be taken to load all data to be migrated into structured SQL tables.

Sample tables in SQL

Required Fields in SQL Tables

case_id: The case_id column is a UUID that is required for creating a case in CommCare. While this can be generated later as well, it would be easiest to generate UUIDs in SQL for each entity mapped to a unique case type above.
parent_case_id: For any child case data, the parent case’s case_id should be mapped to the relevant SQL table as well.
Indicators: While column headers do not need to match, it should be ensured that all indicators that are mapping to case properties in CommCare are present in the table. Similarly, any fields that need to be part of form submission and not necessarily case properties should also be in the table.
owner_id (optional): It is recommended that users and/or organization/groups are created in CommCare and bulk reassignment done in CommCare once data has been migrated successfully. However, if needed, this column can be populated as well with the correct IDs from CommCare.

Setup in Power Automate

Create individual flows for every case type. In this example there are three flows:

CommCare Migration - Parent Cases: This flow will receive data from SQL, transform and make API calls to submit forms to CommCare for all household (parent case) data. See [Power Automate] Parent Cases Flow Configuration

CommCare Migration - Child Cases: This flow will receive data from SQL, transform and make API calls to submit forms to CommCare for all household members (child case) data. See [Power Automate] Child Cases Flow Configuration

CommCare Migration - Repeat Groups: This flow will receive data from SQL, transform and make API calls to submit forms to CommCare for all visit (child cases to household members) data along with repeat group data within the visit data for medicines. See [Power Automate] Repeat Group Data Flow Configuration

Setup in Azure Data Factory

Create individual data flows and pipelines for every case type. In this example there are three sections:

CommCare Migration - Parent Cases: These pipelines contains a data flow for the parent case type data wherein data is transformed and final XML content is saved to the SQL DB and actions through which this data is submitted to CommCare. See [Azure Data Factory] Parent Cases Flow Configuration

CommCare Migration - Child Cases: These pipelines contains data flows for the parent case block and household member data block wherein data is transformed and final XML content is saved to the SQL DB and actions through which this data is submitted to CommCare. See [Azure Data Factory] Child Cases Flow Configuration

CommCare Migration - Repeat Groups: These pipelines contains a data flow for the visit case type data (child cases to household members) along with repeat group data for medicines wherein data is transformed and final XML content is saved to the SQL DB and actions through which this data is submitted to CommCare. See [Azure Data Factory] Repeat Group Data Flow Configuration

The aim of these flows is to show the general approach taken to receive data into CommCare from a SQL DB source. Flows will need tweaking based on individual requirements and data structures.

Approaches to consider

CommCare offers robust documentation for each of these steps and partners can easily follow instructions to get the latest updated values of their data from a previous tool into CommCare through self-service.
All users are highly encouraged to complete Dimagi Academy courses and go through the CommCare Help Site and User Forum to familiarise themselves with the platform.
CommCare Onboarding - Dimagi’s Customer Success team is well positioned to support new partners in setting up CommCare to receive data from a previous tool. Please contact us at sales@dimagi.com for more information and the best suited plan for this support.
Professional services support from Dimagi. Please contact us at sales@dimagi.com for more information and the best suited plan for this support.