Case Import with Excel
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? |
|---|
|
|
|
|
Case imports are handled one at a time per project space. If you start multiple imports, the later ones may show as “Waiting to start” until the earlier imports finish.
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
| 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 |
|---|---|
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.
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.
Update A Single Case Type
In order to properly update the cases of a single case type, we recommend that you follow these steps:
Step 1: Setup the import file to update a single case type.
Step 2: Import the file.
Step 3: Map the Case Properties and Complete the Import.
Click below for the full instructions
Update Multiple Case Types in a Single Import
Below are instructions for updating multiple case types in a single import. Click below for the full instructions.