Link KoBoToolbox to Power Bi using APİ and 30000 rows limitation Problem

User Support API
,
1

Hi
İ am connecting KoBoTollbox to Power Bi with a project has 250,000 rows throgh the next Api:
https://kc.humanitarianresponse.info/api/v1/data/XXXX?format=csv
where XXXX is the id of the project …
is there are any way to retrieve all the rows to make LİVE analyse via Power bi
NOTE:
İ want to link them cloud NOT locally so İ doN’T want to download the XLS file locally
because İ have adjusted Power Bİ to refresh the data cloudy every 1 hour

2

Hi
İ am connecting KoBoTollbox to Power Bi with a project has 250,000 rows throgh the next Api:
https://kc.humanitarianresponse.info/api/v1/data/XXXX?format=csv
where XXXX is the id of the project …
is there are any way to retrieve all the rows to make LİVE analyse via Power bi
NOTE:
İ want to link them cloud NOT locally so İ doN’T want to download the XLS file locally
because İ have adjusted Power Bİ to refresh the data cloudy every 1 hour

1 Like
3

You’ll have to retrieve the data in batches. You can use https://kc.humanitarianresponse.info/api/v1/data/XXXX?format=csv&sort={"_id":1}&start=30000 to skip the first 30,000 submissions, for example. The sort part is important; without it, your submissions may not be returned in a consistent order, and the batches may omit or duplicate submissions.

You may also use MongoDB query operators to filter your data, as described in “Query submitted data of a specific form” in the documentation at the top of https://kc.humanitarianresponse.info/api/v1/data.

3 Likes
4

I see you are suggesting sort={"_id":1}
I have a question about that perhaps you might be able to clarify.
I am would like to download submission data in descending order. (also with a limit, but that part works fine)

This is what i have which downloads all the submissions for a form
curl -X GET ‘https://kc.kobotoolbox.org/api/v1/data/XXXXXX’ -H ‘Authorization: Token YYYYYY’

I have tried adding the sort but then the request does not work
.
I have tried it as such:
curl -X GET ‘https://kc.kobotoolbox.org/api/v1/data/XXXXXX?sort={"_id":1}’ -H ‘Authorization: Token YYYYYY’

It seems the sort function isn’t recognized this way

5

1 is ascending order; try -1 for descending: https://docs.mongodb.com/manual/reference/method/cursor.sort/index.html#sort-asc-desc.

1 Like
6

Thank you for respponding!

apologies. my question is not about ascending vs descending. My problem is that the sort breaks altogether.

in testing the curl command in terminal I just get back a html file with error 500

7

I’m facing the same problem as @hcomp. For example when calling: https://kc.humanitarianresponse.info/api/v1/data/XXXXX?format=csv&start=30000&sort=%7B%22_id%22%3A1%7D (which should be the url encoding of {"_id": 1}) I get a bad gateway response (see below) after about a 30 seconds wait.

<html>
<head><title>502 Bad Gateway</title></head>
<body>
<center><h1>502 Bad Gateway</h1></center>
<hr><center>nginx/1.19.10</center>
</body>
</html>

Removing the sort parameter for the query (https://kc.humanitarianresponse.info/api/v1/data/XXXXX?format=csv&start=30000) is nevertheless successful.

Any pointer about what I might be doing wrong?

Not sure if it matters, but I’m using htmltools in R for the query but I get the exact same response if I use curl directly.
Thanks!