Pull data into excel

User Support Data Management
,
1

Dear All

I have read the article https://support.kobotoolbox.org/pulling_data_into_excelquery.html?highlight=automatically%20download%20files however i am still a bit unsure of how to proceed.

When i use the Get csv option i can download a csv but I have two issues 1) if i give the form url with .csv into the web browser the csv downloads, however if I use a new blank excel-Data-From web-url.csv i get the error The webpage cannot be found

image
image1366Ă—768 108 KB

any advice on this is appreciated.
My second issue is that the csv option allows a mixture of data labels and data values to be downloaded rather than just data values
image
image1366Ă—768 111 KB

When I download the data manually I choose csv and XML values and headers, which gives me the desired output

image
image1366Ă—768 91.7 KB

How can I get the csv to download with the XML values and headers?

Best wishes

Catherine

2

Would you mind trying out the following api:

https://kc.kobotoolbox.org/api/v1/data

You should have a lot of option to download your data with as outlined in the image below:

image

Have you tried with xls or xlsx? Would you mind giving a try and see if it satisfies what you are looking for.

3

Dear @Kal_Lam

Thank you for your response.

Initially when I select Get csv the data file with the urls downloads correctly.

I then edit the url to replace “?format=xls” to “.csv” if i enter my newly formatted csv to my web browser a file downloads, however if I do it from within excel the file does not download. If I edit the url to be .xlsx a file downloads however it is not formatted exactly the same as when i manually download csv XML values and headers.

Is there any text I should add to the URL to make sure it downloads as XML values and headers?

Do you know why I cannot download the file from within excel using the Data-Get external data-From web

Best wishes

Catherine

4

I feel like you are missing a step here …

image
image848Ă—462 64.7 KB

Would advise you to have a closer look at this section. Seems like you are directly putting the URL to the browser.

5

Dear @Kal_Lam
The picture you have pasted is blurry, from what I can make out I have a slightly different ribbon in excel, I am using excel 2016 and when i click the data tab, there is a get external data ribbon which includes from web. see the screenshot the left hand side. If I choose “From Other sources” I do not get web as an option.

image
image1366Ă—768 101 KB

If I enter my URL to the From web option in my version of excel it doesn’t work.

But even if I do get this to work I am left with the issue that downloading the data as csv is not exactly the same as downloading manually from the dashboard and selecting csv and XML values and headers.

Catherine

6

OK in this case, please follow the following (it should probably help you out):

  1. Make your data public for that particular project that you are trying to pull it in your Excel from your KoBoToolbox user account.
  2. Select From Web.
  3. Then paste your URL.

Doing so you should be able to pull your data in an Excel sheet.

7

Thank you @Kal_Lam, changing it to public did work but only if i download it as .xls format, csv still isn’t working. I will need to check if having our links as public will be an issue.

1 Like