Importing Cases Using Excel

Owned by Steve Snyder
Last updated: Jan 08, 2025 by Gillian Javetski
19 min read

The Excel Importer is a tool for creating or updating cases in bulk.  This tool will let you either create new cases, or update existing cases. (Its possible to both at the same time, but that is more advanced).  This tutorial will guide you through the process of using the tool. This is an advanced feature, so please read the instructions carefully.

When Should I Use the Case Importer?

When is the Excel Importer useful for me?

When is the Excel Importer useful for me?

  • You want to create 200 cases using data you collected through a non-CommCare program.

  • You want to add some information to some or all of the cases already registered (ex. a household number for all households)

  • You are running a clinical study and want to organize your cases into different study groups

  • You want to setup some cases for practice/training demos or testing

Create New Cases (via Import)

This section describes how to create your new cases.

Set Up Excel File

To import new cases from Excel, first you need to set up your Excel file. 

  • Create a column for each piece of case data that you want to import (ex. name, village, house, etc.)

  • Each row is one new case that you're importing

  • Setup the unique IDs for your cases

    • If you have an unique ID that you'd like to use from another system (ex. a unique household number), add that as a column as well.  

    • If you don't have a unique ID from another system, add a column titled "case_id" to your document.  This column should contain no information.  

    • Provide the username (or name of the group if your application uses case sharing) in another column called owner_name. 

    • The format for dates should look like: YYYY-MM-DD. You can find this format for dates by going to Format Cells and choosing the Locale English (United Kingdom).

  • For best performance, limit your file to no more than 10,000 rows. Uploading multiple small files can go more smoothly than one large file. Files with more than 100,000 rows cannot be uploaded.

 Two example tables are shown below (one with or without a unique ID).  

Existing Unique ID (household number):

name

village

household_number

age

owner_name

name

village

household_number

age

owner_name

Amelia Sagoff

Cambridge

AS4398

45

test_user

Andrea Fletcher

Pensyltucky

AF3933

24

mamta_devi

Jeremy Wacksman

Washington

JW4380

27

test_user

Sheel Shah

Toronto

SS6654

32

test_user2

No Existing Unique ID:

*it is best practice to include an empty "case_id" column as the FIRST column in your spreadsheet.

case_id

name

village

age

owner_name

case_id

name

village

age

owner_name

 

Amelia Sagoff

Cambridge

45

test_user

 

Andrea Fletcher

Pensyltucky

24

mamta_devi

 

Jeremy Wacksman

Washington

27

test_user

 

Sheel Shah

Toronto

32

test_user2

Next, select the file that you want to import.

 In your project, go to Data tab and select Import Cases from Excel.

Select your file and click on >> Next Step. 

On the next page, first choose your case type.  If your desired case type is not listed, please create an application that contains that case type first (https://dimagi.atlassian.net/wiki/x/ry7Kfw). 

Next step is to tell CommCare HQ in which Excel column the unique ID is and what type of unique ID you're using. Follow the table below:

IF

THEN

IF

THEN

you're using case_id to update the cases (i.e. you did a case export from CommCareHQ),

choose caseid as your Excel column and Corresponding case field as Case ID (see first screenshot below)

you're using an existing Unique ID (ex. household number),

choose that column as your Excel column and Corresponding case field as External ID. (see second screenshot below)

Existing Unique ID

No Unique ID

Make sure you check "Create New Cases if there is no matching case"

Import Parent / Child Relationships

The case importer allows you to import parent/child case relationships, either separately or simultaneously.

Click below to learn more.

Import cases simultaneously

Important!

Please make sure you have a parent_type column in your import! This is required along with the parent_id field.

You will need two Excel files, one for parent cases and one for child cases.

Your parent case file will need:

Your child case file will need:

  • A column to uniquely identify the child cases (external_id)

  • A column to identify the parent case (parent_external_id)

  • A column to identify the parent case type (parent_type)

Example parent excel file (case type is father)

external_id

name

external_id

name

A1

Tom

A2

Joe

A3

Bob

Example child excel file: 

external_id

name

parent_external_id

parent_type

external_id

name

parent_external_id

parent_type

child1

Tom's Son

A1

father

child2

Tom's Daughter

A1

father

child3

Bob's Son

A3

father

  1. Import the parent case file (if needed).  Make sure you match external_id to the External ID when identifying how to identify cases. 

  1. Import the child case file.  Make sure you match external_id to the External ID when identifying how to identify cases. 

  2. When mapping columns for the child case file, make sure you map parent_external_id and parent_type columns.

Import cases separately

Important!

Please make sure you have a parent_type column in your import! This is required along with the parent_id field.

When you would want this approach:

  • If your parent cases are already in the system

  • You don't want to deal with external_id's on your cases

Warning: Depending on how unique the rest of your data is, it can be hard to correctly match up the parent cases imported with those exported.  Be very careful with this step, as getting it wrong can cost you a lot of time and effort fixing it.  Using external_id's (or making sure you have at least one completely unique identifier on your data) would be a good way to avoid this potential hassle.

If you are importing both parent and child cases separately, you would need:

  1. Import parent cases excel file (Importing Cases Using Excel )

  2. Export parent's case_id

    1. Select the 'Data' tab from the options at the top of the screen, choose 'Export Cases', filter appropriate users and download your parent cases.

  3. Create Child case excel file:

    1. Your Excel spreadsheet should be broken down into columns matching different case properties in your application.

    2. Match your parent's case_id to your child case excel file (see warning above!) 

  • Note: if you have downloaded a case export and are using those fields as your template for importing, the case export will show the field indices.parentcasetype (where "parentcasetype" is the case type of your parent case). Do not use indicies.parentcasetype, instead, replace this text to say parent_id.

  • Match your parent's case type using a column called "parent_type" (this is required).

Example child case excel file:

ID

name

parent_id

parent_type

owner_id

1

Child One

dbd248b6a4744652b9535ad

example_type

f1b782cf397106625955f69

2

Child Two

e20157f404704fbfaef4b21

example_type

f1b782cf397106625955f69

  1. Import child cases excel file (follow Importing Cases Using Excel).

First, map on case id to some column. Then, click 'Create new records if there is no matching case'

Then, map the parent id and parent type columns so the relationship is created.

  • Click "Create a new property instead"

  • Enter "parent_id" as shown in the image below

  • Repeat for "parent_type"

Warning: If a parent_id is provided and no case with the provided case ID exists, the row gets ignored by the case importer without any warning messages. If you neglect to add "parent_type", the import will fail.

Update Your Cases

In order to update cases using an Excel file, we recommend that you follow these steps:

Step 1: Determine which cases to update.
Step 2: Determine if updating data for a single case type or for multiple case types.

Click below to learn more.

Step 1: Determine which cases to update

To update your cases the first step is to determine which cases to update. To do so you need to use a unique ID so that CommCare can identify your cases. You have two options to do so:

Options to determine which cases to update

Steps to follow

Options to determine which cases to update

Steps to follow

Option 1: Use the CommCare case export feature to download your cases and update them (https://dimagi.atlassian.net/wiki/x/vCzKfw ). CommCare generates a unique ID for each case and stores it in a property called case_id.

  1. Download your cases and make a case export, making sure you include the case_id column.

  2. Remove any cases that you don't want to change

  3. Remove any columns that you don't want to change/update

  4. Update the data in the columns that you want to change

  5. Add new columns for any new case properties you want to change

Option 2: If you already imported these cases using a unique ID (Creating New Cases - 1. Setup Excel Filearchived), you can use this ID to update the cases.  For example, you may have used household number as a unique ID for importing cases. 

  1. Include your existing Unique ID column in your Excel document

  2. Add columns for other case properties you want to change or add.  You do not need to include all columns for the case, just the ones you want to update or add. 

Two example tables are shown below, one that using a downloaded case ID (option 1) and another that uses an existing Unique ID (option 2). 

Option 1: No Existing Unique ID

name

is_pregnant

high_risk

info.case_id

name

is_pregnant

high_risk

info.case_id

Amelia Sagoff

no

yes

bcf3df0a-d19e-4701-86de-88f005aa258b

Andrea Fletcher

yes

no

4ddba071-7b1e-433d-871b-c6d9c89fc390

Jeremy Wacksman

no

no

b09bd9e8-cd9a-4a86-b14d-d0ac99d9a47a

Sheel Shah

yes

no

8bb2e7ad-d098-4929-a67f-1d8df22e9be5

Option 2: Existing Unique ID (household number)

name