All columns do not appear in API

There is a lot of columns missing in the APIs that actually appear in the dataset when I download data into CSV or Excel.

Anyone else experienced this? How do I solve this issue? Am I calling the wrong API?

Welcome to the community, @abeeharry! Could you also let us know the use case so that we could help you out?

I am using the the following to retrieve data from Kobotoolbox via a REST API:

HttpClient client = new HttpClient();

        client.DefaultRequestHeaders.Accept.Clear();
        client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

        var _auth = _config.GetSection("Authentication");
        var userName = _auth["User"];
        var passwd = _auth["Password"];

        var authToken = Encoding.ASCII.GetBytes($"{userName}:{passwd}");

        client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic", Convert.ToBase64String(authToken));

        HttpResponseMessage response = await client.GetAsync(URL);

        if (response.IsSuccessStatusCode)
        {
            var content = await response.Content.ReadAsStringAsync();
            return content;
        }

        return null;

I then save that data into a db on Azure.

I have no problem retrieving all the ROWS with the above code.

However, the APIs are not retrieving all the columns. There are many more columns that are available for the same datasource . I can see them when I log into Kobotoolbox’s website and download the datasource into say an excel or csv but they are not all available through the API.

Pinging @Josh here for the support.

Hi @abeeharry, can you please give an indication of which columns are missing in the API?

In a private message, can you please send me the following so I can take a look:

  • Which server you’re using (HHI/OCHA),
  • Username,
  • Project name
1 Like

Hi Josh. I am not sure how to send you a private message. Do you have an email address?

You can give this a try:

1 Like

I typed out this whole explanation but It says I cannot send you a private message.

I’ve sent you a private message now to start the conversation :+1:

1 Like

@abeeharry, if you are still not able to send a private message, please refresh your page. You should then be able to share it.

Hi @abeeharry as far as I can tell, the only difference between the XLS export and the API response is that the questions of type note are not returned by the API. This is the expected behaviour. Are there questions other than note types that are being excluded?

1 Like

Hi Joshua

Please let me know if you were able to find a solution to our problem.

The team is getting a little nervous as they bet the farm on KobolToolbox which is missing fundamental pieces of the puzzle.

Thanks,

Amar.,

Please try to keep the discourse constructive.

What URL are you using to retrieve data? Your code shows

HttpResponseMessage response = await client.GetAsync(URL);

but it provides no indication of where URL came from.

Please also respond to inquiries from our staff who are trying to help you, such as this one from 8 days ago that you have not answered.

Hi @abeeharry, just to summarize our discussion:

The /data.json endpoint returns questions that have had data submitted to them. This means that there can be instances where columns will show up in the Excel export but not at the endpoint. As mentioned, there are several reasons why there might not be data for a particular question in a submission:

  • a note-type question (doesn’t receive submissions)
  • skip-logic that moved to the next question for that person
  • it wasn’t a required response and was left blank
  • it was part of a previous revision of your form that never received submissions and is not longer displayed.

If any of these conditions happen to be true for all submissions for a specific question, you won’t find that question in the response data from /data.json, however you may find that /data.xml will have what you’re looking for.

2 Likes