Kobo excel download(export) not showing latest data

[ryanend]

Hi,

We had deleted one submission, and when we exported the excel data, we found that the data in the excel export still had that deleted submission. We are not able to export the latest data in the excel format, however when we exported in csv format the data were the latest (without the deleted submission).

Any help as to why the data in excel export is not getting updated? The help regarding this will always be appreciated, thanks.

[Kal_Lam]

Do you mean you see the deleted data in your Excel Power Query or while downloading it as an Excel file?

[ryanend]

While downloading as an excel file.

[Kal_Lam]

Could you share with us the screenshot of the difference between the csv export and the xls export. It should help us better understand your situation. Would you also mind letting us know the server you are using.

[ryanend]

We have kobo installed on our own server.
Also the difference is of the number of submissions, e.g. lets say I have a form with 2 submissions, when I delete a submission and download the excel it still shows 2 submissions. But when a day later, i try the same export, it has the updated data with 1 submission.
There must be some sync issue that persists of which we are unaware of?
Thanks.

[Kal_Lam]

Would you mind following the exact steps in one of our servers (either OCHA or the HHI) and see if you are able to replicate the issue there.

[ryanend]

Hi we tested on HHI server, and it was not replicated there, the issue persists on our server though.

[Kal_Lam]

So it could be an issue with how you installed KoBoToolbox in your server.

[ryanend]

Hi @Kal_Lam, we checked the installation, and we couldn’t find anything in the installation that might be causing this issue.

Also we found that a xls folder is created under the exports folder in kobo_media_uploads in .vols folder, we tried deleting the existing file, and then too the URL didnt work, we are using this url to export data ’ ‘{kc_base}/api/v1/data/{formid}’.xls’

Thanks.

[ryanend]

Hi @Kal_Lam we observed more of the issue, and found what is actually happening, and how we can replicate the issue -

1. Lets say there are two submissions on any form -

   

2. Excel exported using the Kobo Rest API - https://{kobo_domain}/api/v1/data/31.xls (31 is the form Id)

   

3. When the excel is exported there is an entry created in the ‘Excel Exports’ which can be seen in the ‘Legacy Project’ view

   

4. We deleted one of the submission -

   

5. We tried exporting again using the Kobo REST api - https://{kobo_domain}/api/v1/data/31.xls
   Here we found that the data in the newly exported excel file is still the same, it did not change

   

6. We also noted that there was no new entry created in the excel export in the Legacy Project page -

   

Due to this, we are still getting the stale data, no new excel file gets added here, and every time we export the data using REST excel, we get the same stale data.
We also found when we delete the existing excel in the ‘Excel Export in the Legacy Project’ page, the new export works properly.
We also found that this issue exists only when the submission is deleted, if the submissions are added, or updated the newly exported excel actually contains the updated data.

These were how the issue was replicated…
Just wanted to know if we are missing anything here? Thanks!
Any Help would always be appreciated.

[ks_1]

This is probably how the API is designed (or it might be a bug). The best way to proceed in this case would be to use Power Query in excel to pull the data from /api/v1/data/31 and load it as a table.

[Kal_Lam]

Thank you @ryanend! This information is very helpful!

[Kal_Lam]

OK, would you mind trying this API discussed previously (it should solve your issue):

API to GET Kobo submissions using 'label' rather than value

After making your data public: Settings–> Sharing–> [image] USE THIS GENERAL STRUCTURE: Default Language: https://kc.humanitarianresponse.info/“USER_ACCOUNT”/reports/“FORM_ID”/export.csv Or other instance… EXAMPLE of FORM_ID_IN_KOBOTOOLBOX: a5n9jp9qxyiKPfRNYq7KAt If “english” is one of your languages… https://kc.humanitarianresponse.info/ USERNAME /reports/ FORM_ID_IN_KOBOTOOLBOX /export.csv ?lang=english

[ryanend]

HI @Kal_Lam actually we require the data in the xlsx format, also we are trying this on the Kobo which is installed in our server.

[Kal_Lam]

You could use your server details with the API shared above. It should work.

[ryanend]

Hi @Kal_Lam we tried with the above shared API, and it says the ‘The resource can not be found’ .

[ks_1]

@ks_1

This is probably how the API is designed (or it might be a bug). The best way to proceed in this case would be to use Power Query in excel to pull the data from /api/v1/data/31 and load it as a table.

@ryanend Have you tried this?

[ryanend]

@ks_1

This is probably how the API is designed (or it might be a bug). The best way to proceed in this case would be to use Power Query in excel to pull the data from /api/v1/data/31 and load it as a table.

Thanks for suggesting this, but we tried this too, and the result was the same, because the source is not getting updated anyway. So it didn’t work either.

[ks_1]

I am using this on a daily basis and I can confirm that it works for me. If I remove a submission from kf, it does not get pulled to the excel sheet after update.