Fetching Live Data from Kobo using Power Query
Pulling Data Into Excel Power Query
This article covers how to connect to the API to pull your data into Excel Power Query.
- Login to your account.
- Next, go to the API for your account by going to either https://kc.humanitarianresponse.info/api/v1 or https://kc.kobotoolbox.org/api/v1/.
- Select the URL for the instance in which your account is located.
- Under the Data heading, click on /api/v1/data
Click on “GET” and select “xls”
- This action will download a text file to your computer called “data”.
- Next, locate your project’s data structure inside the data file. It will be in the following format: description,id,id_string,title,urlFor example:MIRA_DO,1314,MIRA_DO,MIRA_DO, https://kc.humanitarianresponse.info/api/v1/data/1314?format=xls
- To pull the data, take the form URL (in bold in the above example) and replace “?format=xls” to “.xls”.
- For our example, the URL should now be:https://kc.humanitarianresponse.info/api/v1/data/1314.xls
- Before transitioning over the Excel, make sure that there is a) at least 1 record stored in your data table in KoboToolbox and b) under the Project’s settings, set it to “share data publicly”.
- In Excel, click on Data > From Other Sources > From Web and paste your URL.
- You should now be able to have your dataset in Microsoft Excel.