Popular Help Content
Export from CommCare.zip: The zip file that contains Raw Data.csv that is downloaded from CommCareHQ.
CommCareHQ Application from the App Exchange (Coming Soon): This tutorial uses a very simple "Pregnancy Tracker" application to create the example data. You can download this application from the Exchange to try yourself shortly.
Custom Export in HQ for the project
- See this this page for for details on creating the data export: Data Export Overview
For Export Name name it "Raw Data"
- For Default File Type choose CSV (zip file) as Name your export
- For Default File Type choose Excel 2007 as the Default File Type. Excel won't recognize dates properly if you use an Excel file type.
- Choose Automatically convert dates for Excel
- Choose Create a Daily Saved Export
- Be sure to rename your Display columns to your Display columns to how you want them to display in your reports.
Download your export and save the zip file to your computer
Unzip the file you downloaded. It should contain a file called "Raw Data.csv"
If it is not a zip file, you need to edit the export and choose CSV as the default file type Your exported data will look like the below picture:
Step 2: Create the Report File and Import the Data The next step is to create the file that will hold the reports. This is a separate Excel file than your raw data file. This way you can update the raw data without having to change the reports file.
Step 1: Create Custom Export in HQ for the project
- See this page for details on creating the data export: Data Export Overview
- For Export Name name it "Raw Data"
- For Default File Type choose CSV (zip file) as the Default File Type. Excel won't recognize dates properly if you use an Excel file type.
- Be sure to rename your Display columns to how you want them to display in your reports.
- Once you create the export, it would be listed under 'Saved Custom Exports'.
- Across the export, click on 'HTML Preview' button. It would show you the raw data in the browser. Copy the URL.
- Save the export
Step 2: Copy the Web Address of your saved export
When you chose to Create a Daily Saved Report in step 1, this meant every night CommCare will generate the export with all the latest data and save it. We will use this report to pull into Excel.
- Scroll to the bottom of the Data Exports Page
- In the Daily Saved Exports section you will see your saved export.
- ***Important*** It will take one night after creating the export for it to appear. CommCare saves the report nightly.
- Right-click on the link to the export
- Choose Copy Link Address in the menu. This will copy the URL of the export file so you can paste into Excel in the next step.
Step 2: Create the Excel File and Import the Data
The next step is to create the file that will hold the reports.
- In Excel, Create a New File. Call it "Reports.xlsx" (or whatever you would like to name it)
- You will have a blank Excel file now.
- Now we will pull in the data from the csv Excel file
- Choose the Data menu
- Choose From Web in the "Get External Data" options in the top left
- Paste the URL that you copied in Step 1. Then click Go.Choose Connections
- Choose Add... from the Workbook Connections window that appears.
- The "Existing Connections" window appears. Choose Browse for More in the bottom left of the window.
- It might open a popup asking a username and password. Enter you CommCareHQ username and password.
- You would see some data in the window. In the top left of the data, notice a small yellow box with a forward arrow (there might be a couple of them).
- Click on the yellow box, just above the data. This would select the data to be imported, and the box would turn into a green checkbox.
- Click on 'Import'. It's important to click on the yellow box with arrow, before clicking on Import, otherwise things will not work properly.
- Choose New Worksheet in the next window the click "Properties..."
- In the Properties window
- Check the box for Refresh data when opening the file
- Check the box for Refresh every 60 minutes
- This will pull the latest data from CommCareHQ every time you open the file, or after 60 mins.
- Click OK
- Rename the tab "Raw Data"
- You now have the data in your reports file and it will automatically update each time you open the file or after 60 mins.