Loading external CSV choices breaks randomly

1. What is the issue? Please be detailed.
I have a location selection from an external CSV file that works correctly using search('tz_locs') function. Attached minimal example in the post.

Sometimes adding a line to the CSV file causes an error.

The very same entry on its own doesn't cause any errors. The csv file is UTF-8 encoded.

Error A is on loading the question:

no such column: c_tz_region_id (code SQLITE_ERROR):,
while compiling: SELECT c_tz_region_id, c_tz_region_label
FROM externalData

it is the result of adding a line

Another, error B on loading the form:

caused by adding a line Lindi,Lindi,Kilwa,Kilwa,Kiranjeranje,Kiranjeranje,X,X

I tried reuploading and redownloading the csv and form files, I tried changing the name of the file.

2. What steps can we take to reproduce this issue?

ErrorA_tz_locs.csv (15.7 KB)
ErrorB_tz_locs.csv (15.7 KB)
WORKING_tz_locs.csv (15.6 KB)
tz_test.xlsx (11.0 KB)
tz_test.xml (1.3 KB)

ODK Collect (v2023.2.4, v2022.4.4, and v2023.1.2). ODK Central versions are

a2b1595b5d0736de22dce0d4cc7e20d25e0c33c1 (v1.5.3)
 d9cb07fdceaa7df017ed2aee114db1b2b7e1a2d8 client (v1.5.3)
 badb3912fdf4d5dca29bd4cd520b9d3b4788db6e server (v1.5.3)

I can confirm that I can fully recreate the issue that @mixmixmix has posted with their files and with my own surveys (n=2).

Interestingly you can solve both ErrorA and ErrorB by deleting rows and / or columns. There does not seem to be a file size threshold though as sometimes bigger files work and smaller files fail. Can we push this up as an issue on GitHub?

As another interesting piece of information, if I create a new draft from an existing, working form and download the CSV in question and reupload it as a media file (without even opening the file) I get these failures (in my case Error B - the one about unterminated quoted fields) despite the published for working fine.

This is with ODK Central v1.5.3 and ODK Collect v2023.3.0

1 Like