Exporting data to excel with API no longer in chronological order

Hey there,

We have been using the API from our account for a while now to create a link between the data in Kobo and our excel sheets.
(We followed this tutorial: http://support.kobotoolbox.org/en/articles/1559346-pulling-your-data-into-excel-power-query)

However, recently we have noticed that the data does not come in a chronological order any more.
That is, before our excel sheets where being filled by the questionnaire´s start date. This is however no longer the case and the new data is registered sometimes at the top of the excel sheet, sometimes at the bottom. And so far we haven´t figured out what logic the data is following.
As you can imagine, this renders the tracking of our data collection quite difficult.

Does anybody know how to return to the “old way” so that the data follows again the chronological order of the start date?
Is there a way to define/program this somewhere?

Any help would be appreciated!

We’re using Excel 2016 with the function “getting data from web”.

Thank you!

Hi @Abraxas,

Welcome to the community! After reading you post, I tried checking how the data gets stored in the server and found out as described below.

The data you collected first (either it be from KoBoCollect android app or from Enketo, makes no difference) gets stored in the server first. The second data then collected will get submitted to the server just on top of the data stored earlier. This process keeps repeating. For reference please see the image below:

The image shown above is from the KoBoToolbox UI. Data with Serial Number 1 to 4 was collected from Enketo and sent to the server while data collected from 5 to 8 was collected from KoBoCollect android app.

I then tried pulling this data to Excel Power Query and observed that the data was ordered in chronological order (i.e. starting from 1, 2, 3, … 8). Please see image below:

If you look closely at the image above, you will see that the serial_number matches the index.

However, please note that this is a case where there is only 1 user (where it was me collecting the data and submitting the data to the server). However during data collection, this may not be the case. There maybe several enumerators collecting data simultaneously. So in this case, the serial_number might get distorted i.e. their data will stay first (in the index) in the server (as you have identified for your case) if some enumerators (who have collected the data from say serial number 100-150) submits the data before other enumerators (who have collected data from say serial number 85-90) but submitted their data later.

So the main thing is that the server will start indexing the data (in it’s index variable) based on the submission_time once it starts receiving the data rather then considering the date of data collection (that was stored in KoBoCollect android app or in Enketo).

Hope this was helpful!

Have a great day!

Hi Kal_Lam

Thank you for your explanation.

From your example, I can see how the data gets stored in a nice chronological order, with the new data added on the bottom of the excel sheet when pulled to Excel Power Query.

As you can see from the screenshot below, our data, however, behaves somewhat differently and is not in a chronological order:

You are right, we are working with a team of enumerators and it is possible that there are collecting data simultaneously. However, if Kobo stored the incoming data in order of the submission date and time, why is this not the case in our example. As you can see there is data submitted on the 1st of january but it is all over the place.

What I find most misteriously though, is that this must have changed recently. Before (lets say 2-3 months ago) this was not the case and the data came in in chronological order (despite the simultaneous data collection).

Do you understand how the data in our model gets indexed?
And if it is not according to the submission_time, is there a way we can programme our questionnaires so that the data is indexed based on the submission_time?

Thanks again for you help and have a great day too!

1 Like

Hi @Abraxas,

Would you mind checking this with data from other projects. Looking at mine i can still see (other data) in chronological order. Please see image below:

Let us also hear from other users if they face the same issue that you are facing!

Have a great day!

I’m facing the same issue, it does not get order by submission time:

It also happens in another form that I administer.