XLS data download using new KoBo API

User Support API
,
1

Hi,

I am writing to know a solution to download data from KoBo using new API and R (or Python). Using the methods explained in https://github.com/tinok/kobo_api, I converted these code to R-script process. I am able to create exports, get the list of exports and get the lastest export for the deployed project. My R-script codes are available at https://github.com/ppsapkota/kobohr_apitoolbox.

My problem is that I am not able to download data from the export URL that is generated. When I have the export url (CSV or XLSX), it returns an error message ‘Private storage access denied” even if I pass username and password. With the same process I used, I can download other XLSX files from the web. Any help would be appreciated.

Best regards,
Punya

2 Likes
2

Hi all,
Is there anything new on this ? any endpoint or a way to use the new API to download xlsx files.
Thanks

3

It will be appreciated if anyone has a solution. to punya’s question. Anybody?

4

Hi @mowdou,

Have you tried out as suggested in the support article Pulling your data into Excel Power Query. It should be helpful!

GOOD DAY!

5

Thank you @Kal_Lam; for the tip, and it worked. However, what I was looking for is a way to actually pull in the survey questions into Power Query as column names. I want to see the questions because I want to know what the data inputted is for otherwise if I get the code name generated in Kobo during form design such as Q1, g1, or whatever; I can’t tell until I go to the questionnaire to see what Q1 correspond to what question…

6

I usually do this:
ALSO EXPLAINED IN A VIDEO TUTORIAL: HERE
The turorial is for POWERBI but POWERQUERY is the same.
Go to settings–>Sharing and click in “Anyone can view submissions made to this form” ( cautions with sensitive data)
Then, if you have no language specified (Default language), create your query to the following WEB:
https://kc.humanitarianresponse.info/“USERNAME”/reports/“ID_STRING”/export.csv

if you have defined othe languages (EX: “::english”) in your form:
https://kc.humanitarianresponse.info/“USERNAME”/reports/“ID_STRING”/export.csv?lang=english

Where “USERNAME” is: your account name
and “ID_String” is the +20 caract name of your form…when you click on it would be something like :“aPnv9qNPKT5HG4e8i9rR9n”

EXAMPLE: https://kobo.humanitarianresponse.info/#/forms/aPnv9qNPKT5HG4e8i9rR9n/landing

image
image.png830×255 53.4 KB

This metod gives you the answers and data in “Readable” language , instead of the names.
Does it help?

2 Likes
7

Hi @iker.montes,

Thank you for sharing your knowledge and experience with the entire community! Expect the same in the upcoming days too!

GOOD DAY!

1 Like
8

How can i get the same data in Codes without group name as headers. Is there any other alternative, as with the API we can the data in Codes but the column headers are with Group Name.

9

@iker.montes, @Kal_Lam. Does Anyone know how to do the same process and get data into python pandas dataframe ? it would help me a lot.

10

Sorry @gatoni, Cannot help you here.

1 Like
12

Hi @gatoni ,

here’s to solution:
You can get the url of your latest export using this very useful script I found on github

make sure you specify the right export type at line 29 (if you have quite a complex form, I suggest xls)

The function lastest_url() returns the url of your latest export

At line 116, paste the code below

"""
get xls and read into pandas.
requests to kobo can only be acepted with the cookie 'kobonaut'
to debug, send the request through browser first, check the headers and copy the cookie here
make sure you have installed openpyxl 'pip install openpyxl'
"""
headers = {'cookie': 'kobonaut=xxx'}
resp = requests.get(url, headers=headers)
xls = pd.read_excel(resp.content)
return xls

to get to kobonaut cookie above, get the url of your export from the console, paste it in the browser and check the headers of your request. Then paste it in the code above