Versions Compared

Old Version 50

changes.mady.by.user Krinda Wernicke

Saved on

compared with

New Version Current

changes.mady.by.user Gillian Javetski

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Multiexcerpt include macro
macro_uuid785c6088-0e28-4857-9b71-fdd34d840424
nameSoftware Plan - Standard
templateDataeJyLjgUAARUAuQ==
page[Internal] CommCare Public ManagementCommCare Help Site Design Guidance
addpanelfalse

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.

...

Expand
titleCLICK TO EXPAND

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

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 File), 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

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

is_pregnant

high_risk

household_number

Amelia Sagoff

no

yes

AS4398

Andrea Fletcher

yes

no

AF3933

Jeremy Wacksman

no

no

JW4380

Sheel Shah

yes

no

SS6654

Panel
panelIconIdatlassian-warning
panelIcon:warning:
bgColor#EAE6FF

Note

To update the value of an existing case property to blank/null, make sure to include at least one property in the sheet that is not null.

One way to accomplish this is to have a last column called "name" with a copy of the current cases' names. This will not change the names of the cases – since it should be the same as before – but it will force the upload to recognize and set the null values.

Step 2: Determine if updating data for a single case type or for multiple case types

Commcare has the option to update a single case type or multiple case types for example in a single upload case for household and household_member can be updated.

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

Expand
titleClick to expand

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.

Panel
panelIconIdatlassian-warning
panelIcon:warning:
bgColor#EAE6FF

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.  

958a07f0-1c4a-4c85-a247-565adf6f5bd0.png

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:

9e87aefe-227c-460f-bdb7-e9ac69b7f41a.png

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

  • Map one of the columns to the case property called name,  This will ensure that each case has a name. 

  • Map the owner_name column to the case property called owner_name. This will assign each case to a user, case-sharing group or location. You will need to check the box for "Create new property". If you are using a location for owner_name, then also go through https://dimagi.atlassian.net/wiki/x/ryXKfw.

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.

Expand
titleClick below to expand

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)

...

It is possible to update or create cases with Easy References, though there are some quirks. 

Click below to read more.

...

...

titleCLICK TO EXPAND

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. 

Expand
titleCLICK TO EXPAND

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

  1. 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. 

  1. 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:

  1. 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:

  1. 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:

  1. Select the module and proceed to the module settings page.

  2. Add a Module Filter that can never be true (for example: 1=2):

  1. 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?"

Click below to read more.

...

titleCLICK TO EXPAND

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.

Expand
titleCLICK TO EXPAND

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.

...