Dear all,
I operate a multi-year indiator dashboard which relies on the v1 API. The data is updated yearly.
When I wanted to update the dashboard today, I found out, that the old link (https://kc.humanitarianresponse.info/api/v1/data/XXXXXX?format=csv) does not work anymore. Through this forum, I’ve got it to work again with the link
https://kc-eu.kobotoolbox.org/api/v1/data/XXXXXX.csv
My problem is now, that the header data (first row in csv) is not consistent with the old implementation I’ve used.
- A lot of elements are missing (e.g. only a field “gelocation” instead of “geolocation.0”, “geolocaiton.1”…)
- Some elements with options to choose from are split (e.g. instead of one column with the values “choiceA choiceB choiceC”, I get three columns “choiceA”, “choiceB”, “choiceC” with True/false statements.
My problem is now, that I would have to change all dataqueries because of this manually. Is there a way to get the old API v1 format back, which worked well until some months ago?
I also tried out the API v2 connection, there I get as column headers text instead of a label, which is also not compatible with how the dashboard is set up…
Thanks for your help!
Welcome to the community, @KoboAPIUser! Have you gone through this support article Connecting KoboToolbox to Power BI? The article should help you solve your issue!
Dear Kal_Lam
Thank you. As I wrote, when I use the API v2 (which is linked in the support article), the column headers are also not in the format I’ve used through the old API. Maybe I’m not seeing a detail in the support article which could help me with this?
Edit: I think what I’m mainly missing, is either to force the API v1 to give me a “single column” .csv file or to force the API v2 to use the column-name instead of the question text as header of the columns…
@KoboAPIUser, pinging @osmanburcu our Power BI expert for your support here.
hi,
sorry for late response, i was sick for awhile. @KoboAPIUser were you able to solve your problem?
hi, no, not yet, if you have an idea what I could try I would really appreciate it!
I invested some more time into it today.
I was not able to use the api V1 to get what I need - did not find a way to use the single column setting for the export which we used before and also did not get the same columns (see below).
For API v2, I was able to set the export to my needs with export settings, so I exported the xml-labels instead as column headers and used a single column export. I use a link like https://eu.kobotoolbox.org/api/v2/assets/XXXXX/export-settings/YYYY/data.csv now to read this data into PowerBI.
Nevertheless, a lot of columns I had in the old export, (using https://kc.humanitarianresponse.info/api/v1/data/XXXXXX?format=csv), are not available anymore. Can I somehow force API V2 to send them as well? Else I have to find all instances where they were deleted, reordered … in PowerBI, which probably will take ages and is really error-prone - would love to avoid this.
Some of the column labels I’m missing, maybe they help to explain what I’m trying to do
(was also not able to recreate an export in api v1 with them with a link like https://kc-eu.kobotoolbox.org/api/v1/data/xxxxxx.csv ):
“_geolocation.0”, “_geolocation.1”,
“_version__001”, “_version__002”, “_version__003”, “_version__004”, “_version__005”, “_version__006”, “_xform_id_string”, “formhub/uuid”,“meta/deprecatedID”, “meta/instanceID”,
Hi @KoboAPIUser,
Are those columns are imported for you or they are just blocking the refresh of you data? Because column names doesn’t seems important, so you can just exclude them from the advance editor and continue your life as it is
these columns do not exist anymore in the import, so they block the refreshing of the data. So yes, I can go and delete them manually, which I have to do at several places, as they were used (deleted, moved, renamed, reordered) in a lot of different steps all over the place. As explained, this is a) a lot of work and b) can lead to errors.
In the ideal world, I would like to reexport the data in the exact same format as I was able to until two months ago. This way, I don’t have to touch a system which was running and validated in the last three years.
If that is not possible, I would like to really understand, what these columns are/were and where they come from, so I don’t accidentally delete something manually, which I shouldn’t have… and I’m also not sure if this would solve all problems, or if there are more problems after this, so I’d really like to get to my ideal point from above 
I am not sure if i remember 100% correctly, but in the some old version of the kobo version information were directly added into your form as question and each time you edit the form new “_version_xxx”" question was added. Later they just changed it as one column which is version now and it is not added to form directly. So i believe you created your form a while ago, and after the recent changes kobo teams deleted this old version columns which encoded in the form, and it caused a problem from your side due to power bi. My dashboards are running fine for more than 2-3 years without any problem. So this was probably one time issue because of the recent changes
Thank you for your help!
So in the end, I just deleted every column manually which was not sent by API v2. Had to do some adjustments and further trouble shooting, but took me less time than expected.
So the solution for my problem was:
- create export settings of API v2 which align with my old kc-server API v1 export
- manually delete all appearences of columns which are not provided anymore by the API v2 in all queries (they did not contain any relevant information anyway)
- some manual clean-up
if you don’t want to do all this manual work, i could suggest to delete the column name from the first navigation step, after that add all the column names manually as a second step, and after that everything should work fine, just those column would be empty that’s it, but i would choose your method if i needed.
