9.2.2 Excel

TABLE OF CONTENTS

• Import into Excel your downloaded data in csv format from your MDC platform
• Adding survey pictures from your MDC platform into an Excel file
  • Download collected data
  • Define pictures’ URL
• Excel Toolbox

---

Import into Excel your downloaded data in csv format from your MDC platform

When downloading the data from your MDC platform (in this tutorial we take KoBoToolbox as example) in csv format for data analysis, it is important to import the data properly into Excel rather than simply double-clicking on the csv file to ensure, for example, that non-Latin characters are correctly recognized and are not transformed into transformed into mangled symbols. This can be done by following the next steps:

1. In Excel, go to “Data” tab, then “From text”
2. Select in the window the file you just downloaded in csv format

3. Select the proper Encoding in the list – it should be UTF-8 and check “My data has headers”:

   

4. In the “Separator” tab, select the appropriate one (should be “coma”). You’ll know that you have selected the right separator when columns are created for each column of data, as below:

   

   Warning: The characters used in the csv file to separate the different “columns” may vary depending on your computer’s settings. In case something other than coma is being used, try the other options until the previous shows properly separated column, as in the image above.

5. For each column which contains a DATE, select the column (it will turn black). Then select “date” and make sure to specify the right order (should be MDY for “Month-Day-Year”)

   

   You will know in Excel if you have selected the right option, because data properly formatted as date in Excel is right-aligned, while regular text (that looks like a date, but that Excel will consider to be just text) will be left-aligned:

   

   If not properly done, it will be right-aligned:

   

6. Repeat for other columns that have dates. Do not forget columns containing dates.
7. At the end, click finish.

Adding survey pictures from your MDC platform into an Excel file

Work with data collection mean have to deal sometimes with pictures. In this tutorial, we use KoBoToolbox. Using KoBoToolbox as tool can be a little tricky. In fact, you will need to export “text” data and pictures separately and pictures will be divide in folders where one folder corresponds to one picture. It will also be up to you to find at which submission the picture is linked by looking to the UUID (the unique ID) and the name of the folder. Nevertheless, it exists a way to insert pictures into your data files that it’s explain in this tutorial. This allows to display pictures next to other text data and so it’s easier to link pictures to submissions.

Download collected data

1. First you need to download questions’ answers in a XLSForm. Do not forget multimedia data as you have picture.

   

2. You have to choose “Media Attachments (zip)” in “Select export type”
3. Be aware, that you need to store them in a place that will not change over time. Otherwise, you will need to repeat the process of this tutorial.

4. Multimedia’s files are in a compressed folder, you need to extract them or you will have a problem with files’ localizations. “Pictures that are collected in a KoBoToolbox survey are saved into separated files whose name corresponds to the UUID (the unique ID) of the form they are related to. The UUID of a form is written in the column _uuid of the XLSForm.” You need to identify in your computer the files’ locations on your computer:

   

You need to identify in the XLSForm :

• Corresponding cells of “_uuid” (unique ID)

  

• Corresponding cells which contains the name of the pictures: (Here “FD: Please take a picture of the latrine”)

  

Define pictures’ URL

1. Create a new column call “URL” and use this formula : =CONCATENATE(“[Files’ locations]”;[cell containing _uuid];”";[cell containing the name of the picture]) Where in our example:
   • TI2 : cell containing _uuid
   • FD: cell containing the name of the picture
   • Photokobo: name of the folder containing the photos So here the formula will be: =CONCATENATE(“C:\Users[username]\Documents\photokobo";TI2;”";FD2) *Be careful with the “ and *. In order to check the result of the formula, it is possible to copy it and paste it in the windows explorer. If done correctly the corresponding signature image should open.

   

   Warning: This is where it gets technical

2. Create a new column calls “Image” at the right of “URL”. The place is important.
3. Open Visual Basic for applications (VBA) to use a code that will permit to insert pictures in Excel : press ALT + F11

4. Click on Insert -> Module: and Enter the following code:

   View PDF
5. You need to modify the characters in red in the code:
   • TM2 : TM175 => correspond to the range of cells which contains the URL addresses to extract the images
   • TM2 => correspond to the first cell of the range of cells which contains the URL addresses to extract the images. If required, it is also possible to adapt the width and height of pictures in accordance to the original picture format and cell size.

   

6. Press F5 to run the code: pictures are going to appear in the Excel in the “Image” column. You can change row height in function of pictures you will insert.

   Warning: Pictures included in the document are still linked to their local URL. If you share the Excel file with someone else the pictures won’t appear.

Not many solution exists:

• Send data files and pictures separately and tell to the people you send it to re-do the process explain here
• Save the document as pdf. Be careful, depending to the length of the data table it’s possible that all the row will not appear, you need to adapt the document.
• Print the document. Same as for the pdf, you can have a problem with the data table size.

Excel Toolbox

To go further see the Excel Toolbox.