Case Import with Excel
- Steve Snyder
- Freddie Sangma
- Derek Hans
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? |
|---|
|
|
|
|
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.
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:
Unique IDs for each case (Creating New Cases - 1. Setup Excel Filearchived). Ideally include these in a column called external_id
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 |
|---|---|
A1 | Tom |
A2 | Joe |
A3 | Bob |
Example child excel file:
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 |
Import the parent case file (if needed). Make sure you match external_id to the External ID when identifying how to identify cases.
Import the child case file. Make sure you match external_id to the External ID when identifying how to identify cases.
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:
Import parent cases excel file (Case Import with Excel )
Export parent's case_id
Select the 'Data' tab from the options at the top of the screen, choose 'Export Cases', filter appropriate users and download your parent cases.
Create Child case excel file:
Your Excel spreadsheet should be broken down into columns matching different case properties in your application.
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 |
Import child cases excel file (follow Case Import with 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.
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
Step 1: Setup the import file to update a single case type
Instead of renaming the tab to one of the cases in your project when updating a single case type, use a generic name such as Sheet_1.
Step 2: Import the file
You have now created the Excel file to import and decided what unique ID to use to identify your cases. The next steps are to select the file for import and to specify the case type and the Excel column where your case ID is (so that CommCare HQ knows in which column of your Excel file the unique case ID is located).
Step 2a: Select the File to upload
In your project, go to the Data tab and select Import Cases from Excel.
Select your file and click on >> Next Step. This brings you to a page that looks like this
Step 2b: Determine the case identifier
If you see a different page chances are that the name of the case was used to name one of the tabs in the Excel file and Commcare will automatically consider this file as an import that updates multiple case types in a single file, to rectify this rename the tab to a name that does not match any of the cases in the project.
On the page above of your project space, you will need to complete two actions 1. Specify the Case Type to Update/Create and 2. Identify the Case by Specifying the Case ID you want to use. We're now going to look at each of these action
First, you choose the case type that you want to update.
The 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 info.case_id as your Excel column and the 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 the Corresponding case field as External ID. (see the second screenshot below) |
Using Case ID (CommCareHQ Export)
Using existing Unique ID (ex. household number)
Make sure you don't check "Create new cases if there is no matching case". This will ensure you don't accidentally create new cases if you mistype an ID.
Step 3: Map the Case Properties and Complete the Import
You should now be on a page with the following interface:
Map Excel Headers to Case Properties
The next step is to map the columns in your Excel document to case properties that you want to create: you basically want to tell CommCare HQ what name to give to the new case property or match the Excel column header to an existing case property. Understand in which situation you are following the table below and then complete the steps specified
IF | AND | THEN DO THIS |
You're updating existing cases | your application already has case properties | you can choose these for each Excel column. (Note that the tool will only show case properties that have been submitted. If you have not created a case yet, it will not show any case properties). |
You're updating existing cases | your application doesn't have a case property | you can choose to create a new one for the appropriate Excel column. The Auto Fill button will automatically match your Excel headers to the existing case properties' names. |
You are creating new cases |
|
|
Choose each Excel column you want to import and the case property you want to create or update. If you want to create a new case property, check "Create new property" and type in the name of the case property.
Once you've mapped all of your Excel columns, click on the Confirm Import button.
NOTE: If you're creating a new case property, following the above steps will only create the case property for the cases. It will not automatically create a case property under case management in the form builder.
Check for details in Recent Uploads
Upon starting an import, you will be returned to the landing page, and a new row will appear at the top of Recent Uploads, letting you view the progress of your import. Upon completion of the import, the following information will appear under details (messages only appear if the # isn't 0):
# rows matched and updated (corresponds to the number of cases updated for the Excel fields that you matched to case properties in the application)
# rows did not match any existing cases and had new cases created for them. If case_id's were used, these were ignored. (if you selected the option to create new cases, this indicates the number of new cases that were created where there were no matching Excel fields/case properties)
# rows matched more than one case at the same time (you may have multiple cases in your system with the same case_id, in which case the system will not update anything as it will not know to which case the new data corresponds)
The Form IDs and Case IDs download buttons will download a list of the corresponding ids that were created for that particular upload. These can then be used to bulk update cases or forms.
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.
Step 1: Setup the import file to update multiple case types
If you are importing multiple case types, format your file so that each tab corresponds to a unique case type. Each tab needs to be renamed to the corresponding CommCare case type so that CommCare can differentiate which case type is being updated.
Step 2: Select File to Import
You have now created the Excel file to import and decided what unique ID to use to identify your cases. Next steps are 2a. select the file for import and 2b. specify the case type and the Excel column where your case ID is (so that CommCare HQ knows in which column of your Excel file the unique case ID is located).
Step 2a: Upload file
In your project, go to the Data tab and select Import Cases from Excel.
Step 2b: Determine the identifier
Select your file and click on >> Next Step. This brings you to a page that looks like this
CommCare will detect that there are multiple case types and instead of showing the case types it will show the label “commcare-all-case-types”,
Step 3: Completing the Import
Then you should be on a page with the following interface, press confirm and the CommCare will show the result of the import.
Once you press confirm, check for details in Recent Uploads
Upon starting an import, you will be returned to the landing page, and a new row will appear at the top of Recent Uploads, letting you view the progress of your import. Upon completion of the import, the following information will appear under details (messages only appear if the # isn't 0):
# rows matched and updated (corresponds to the number of cases updated for the Excel fields that you matched to case properties in the application)
# rows did not match any existing cases and had new cases created for them. If case_id's were used, these were ignored. (if you selected the option to create new cases, this indicates the number of new cases that were created where there were no matching Excel fields/case properties)
# rows matched more than one case at the same time (you may have multiple cases in your system with the same case_id, in which case the system will not update anything as it will not know to which case the new data corresponds)
Close Cases
The Excel importer also has the ability to close cases.
Click below to read more.
To do this, add a column to your Excel file to specify which cases to the excel file to be mapped to the case and supply case or external id's for matching. In the following table, cases 1001 and 1002 will both be closed, but 1003 will not. Note that the close property needs to be added manually to your excel file.
case_id | close |
|---|---|
1001 | yes |
1002 | yes |
1003 |
|
Prepare your Excel workbook for import.
Go to 'Export Cases'
Download "Open Cases"
This will give you access to the unique Case Id which will be random and look something like "2fa35b2d-e685-408f-99d5-17e215598fa1"
Once you have identified the cases you would like to close, delete all columns except for the case id.
Add a "close" column, and complete each cell with the word 'yes'.
Save your file as a .xls or .xlsx
Note: Please check that you have saved your workbook as a .xls or .xlsx file.
Note: Prepare a separate excel file for each "case type" you would like to update.
Note: Closing cases on import only works when you are Updating Existing Cases. It will not work when you are Creating New Cases. If you want to open and close cases using import, you have to do it in three steps: Create the new cases, Export the cases to get their case_id's, and then Update the cases with the "close" column as described below.
Specify the "case type" of the file you are importing.
Next, map this column to the "close" property of a case. This isn't a real property, so use the "Create a new property instead" option to enter it.
Now, once you finish the import, all cases with a "yes" in the mapped column will be closed.
Note: To verify this import was successful, you can 'Export Cases' , select Open Cases, and review the Excel export to confirm that the cases no longer appear.
Note: It is not currently possible to reopen a closed case using the Excel Importer. To do that you must use the Archive Forms tool to archive the form which closed the case. Closing a case using Excel Importer creates a submission for an "unknown" form in the case history, which can be archived to undo closing, i.e., reopening, the case.
To verify that your cases have been closed
Go to Reports > under the Inspect Data section > Case List. Run a report for the same case type you have uploaded. The report should show a Status column, and show closed for the relevant cases.
Case Importer with Easy References
It is possible to update or create cases with Easy References, though there are some quirks.
You can directly type in the case property as #case/<property_name> to reference a property that is not in the app structure but was imported via the case importer. However, if that property is not used anywhere else in your application, you will see an "Unknown question" warning. This warning will not prevent you from building and using your application. It will simply look as though there are errors with the application.
Click below to read more and to see the following example to remove the warnings.
Set-Up
For the following steps, let's assume we have updated/imported new cases with properties sick and high_risk that were updated via the case importer:
name | info.case_id | sick | high_risk |
|---|---|---|---|
Nick | e399c035-0c83-48ef-b0c0-0d3bbe9f7a39 | no | no |
Blaine | 4691e1d6-3aae-4683-943d-9613e56bd057 | no | no |
Chris | 1651931f-d390-434e-84d7-5a7c6416d37a | yes | yes |
When you go to reference these properties in a form, you may receive warnings that the property is unknown. These warnings will not prevent you from deploying and using the application. Note that you will need to enter the properties manually using #case/<case_property>. The property will appear as a yellow triangle with an exclamation point when turned into an easy reference:
While those warnings do not cause any issues, they can be annoying. Let's remove them!
Instructions
First, create a new module and give it a name. Be sure to set the case type to the be the same as the cases that you imported new properties for.
Now select the Untitled Form that is created with the new module. Edit the form and create a question for each property you have imported that you would like to reference in the application:
Now we need to save the properties so the application can use the properties in other forms without receiving that error message. Return to the Form Settings page and access the Case Management tab. Set the form to 'Update or closes a case.' Save the questions that you created in the form:
If you go to one of the references to the imported property in your application, you should no longer receive the yellow warning message:
Optional
There may be instances in which you do want to reference imported properties throughout the application, but you don't want your mobile workers to be able to update them. To handle this situation, perform the above steps and then add a filter to the module so that it is hidden from the mobile workers:
Select the module and proceed to the module settings page.
Add a Module Filter that can never be true (for example: 1=2):
Now when the application is deployed, the mobile workers will not be able to see the module that contains the imported properties.
Undoing Case Imports (and other system actions)
"Oh no! I performed a huge case import, but now I need to roll them back. What do I do?"
Does this sound like something you've said before? Sometimes, we need to roll back system changes that we've made, whether they were done by accident or incorrectly. Thankfully, CommCare allows you to find most of these forms and roll them back by "archiving" these actions.
Warning! Do not rely on this feature as an integral part of your project workflow. The below directions are only for users who need a last resort method for undo-ing system actions!
Click below to read more.
Finding System Forms:
Navigate to your "Manage Forms" feature
Set the following filters
Users: "admin", "web users", "unknown users"
For case imports specifically you can select "web users" only
Check the box for "Show Advanced Options"
Check the option for "Unknown Forms"
Leave the "Choose Unknown Form" selection as "Show All Unknown Forms"
Set the dates when you think the system actions took place
Set the Archived/Restored filter to "Normal Forms"
Find all the forms in the results section which are "System Forms"
You can open and review these forms to identify whether these are the system actions you are looking for.