ODK Briefcase: Problem with displaying exported csv in Excel and SPSS

Hi everyone,
any help would be greatly appreciated. Many thanks in advance!

What is the problem? Please be detailed.
We have collected data from around 200 people using a fairly large ODK form on Android tablets. Since we are wanting to stay offline, we have been manually copying the instances from our tablets to the computer to then use ODK Briefcase to pull the data and export it to csv. Both processes, ie. pulling and exporting, seemed to be working smoothly at first, with Briefcase reporting 'SUCCESS' for both. However, when opening the resulting csv in Excel or SPSS, 10 survey results are not displayed properly. Whereas usually each row in the table equals one person and each data input is in its own column, for those 10 cases, we have the data broken up into two or three rows, with columns shifted and sometimes multiple inputs in one column, separated by commas. The data in the xml-files looks completely normal as far as I can tell and also the raw csv-file viewed in the editor shows no abnormalities.

What ODK tool and version are you using? And on what device and operating system version?
ODK Collect 1.8.1 on tablets (TrekStore Xiron 10.1 pure, Android 5.1.1), ODK Briefcase 1.8.0 on PC (Windows 7 and 10)

What steps can we take to reproduce the problem?
I have no idea. It just happens with those 10 people and it doesn't matter if I export just those 10 or them together with other. The data is always displayed in the same wonky way so it seems to be something inherently wrong about those 10 datasets. But I have no idea how those 10 are different from the 190 others that are being displayed without any problems. Both the xml-files as well as the resulting csv looks completely normal as far as I can tell.

What you have you tried to fix the problem?
I have tried opening the csv with multiple freeware csv to xls converter programs but all ran into the same problem of not displaying those 10 cases correctly. I have pulled and exported each of the problematic cases individually but the results are the same. However, when I open the csv in LibreOffice, everything is displayed correctly, including my 10 problem cases. Now I would love to just use LibreOffice to open everything and call it a day, unfortunately that's not possible as it has a 1024 column limit and our survey has more due to all the empty layout fields that don't take user input but are exported as columns anyways (groupings, notes, etc.).

Anything else we should know or have? If you have a test form or screenshots or logs, attach here.
This is what my Excel table looks like with two examples of unwanted line breaks in row 8 and 30:

Any ideas what could be causing this or how to avoid/fix it?

Or if not, any ideas on how I could restrict my export to include only those aspects of the survey that actually take a user input? If it didn't create columns for every group or note I created to have the survey displayed a certain way in Collect, I'm pretty sure I'd be under the 1024 colum limit and just be able to use LibreOffice to open the data.

1 Like

Hi @Nina_Morgenstern, thanks for providing so much detail! It makes it a lot easier to find the problem.

My guess is that this is an issue with the CSV export not properly escaping some characters (maybe quotes or commas or accents). Would you be comfortable sending me your zipped up ODK Storage folder (use a personal message) so I can try to reproduce the problem and suggest a fix?

1 Like

in case its of interest in order to prevent future such issues, we normally use a variation of the following constraint with all text questions, long or short.

regex (.,'^[A-Za-z0-9._؟?#&*/@;:؛÷×%$ لأآلإءأ-ي+-]{3,250}$')

it allows for a limited number of latin and arabic characters as well as a few symbols.
most importantly it prevents commas and other special characters which look to have caused your problem.
Addresses are usually the culprit as people naturally include commas and other unusual characters

2 Likes