I recently came across an issue with one of the forms I am working on. The form worked find but when syncing I received the message
Internal Error
Please submit a bug report
Looking at the log file output on the device opendatakit/default/logging folder I was able to find a more informative error message
E/ProcessRowDataPushLocalChanges: 2021-02-03 13:37:03.166 Exception in synchronizeTable - pushing data up to server on table: Visit2_v2 exception: via RemoteException on IDbInterface: org.opendatakit|java.lang.IllegalStateException: Requested column: 157 has two or more data types in it
After a bit more digging I realised that one of my columns which was defined as a number type and populated using a calculation formula was causing issue - a mistake in the calculation would sometimes result in assigning a string value instead, which still saved to the database. Therefore when syncing sqlite detected a mix of both real and text data.
The issue is now resolved but I thought it might be useful information in case anybody else has similar problems. The way I figured out which column was causing the issues was to export a csv and number them (in this case the 157th column). I’m wondering if it might be possible to also include the name of the column from its ID number for the error logging in a future update?
I also did some further digging to try and understand how this can happen, and realised that the column types in sqlite on the device aren’t as strict as they are in postgres on the server (section 3 of Datatypes In SQLite explains how the type is essentially a recommendation for the data stored but not a restriction). There are some ways to prevent this using Check contrataints, but for now I’m happy to just pay closer attention to my calculations!
I have the same problem, but when importing, not when syncing.
It’s very courious, because the error message in the log file es ever the same: org.opendatakit|java.lang.IllegalStateException: Requested column: 16 has two or more data types in it.
My table have 53 columns. I have started trying to import alls columns, but I have tryed also with less and less columns; last time I try to import only 11 columns, but the messsage is allways the same: “E/simpleQuery: 2021-02-16 02:57:46.273 org.opendatakit|java.lang.IllegalStateException: Requested column: 16 has two or more data types in it dbHandle: uuid:ab9787b4-f61c-4da2-bd8a-4f035bae0846”
I don’t understand what’s the problem! Survey works well, and Tables also, but the import process, even obtaining the success message, only produce nulls rows, or no helth rows that provoque Tables to close, and Survey to show the illegal exception …
Hi @aortegon! This error message does suggest that what you are importing might have some data conflicts. Can you share the data you are trying to import and your form? The column 16 is probably counting in the table so it could be in a different order than what you import.
Thanks Caroline! You are always ready to answer, I’, very gratefull with you!
Yes, off course, I can share the data and forms:
efr.xlsx is the mean form
agrega_titular.xlsx is a subform, a linked_table of efr
efr.imp21.csv, and efr.imp22.csv are the last two versions of the file I try to import.
I start with a health table, with only 3 instances, that I can see perfectly in both, the List View ant the Detail View.
In the tables screen manager I pick the file to import, and I obtain the success message.
When I try to access the table, the List View don’t show anything; opening Survey and accessing the form produce the error message you can see (the java.lang.IllegalStateException)Uploading:
config.zip…
I send you the entire config folder of my PC, with an extra assets/csv/ folder in wihich I put the csv files to import.
You say “The column 16 is probably counting in the table so it could be in a different order than what you import.” How can I know exactly what’s the referred column?
Hi @aortegon! The upload doesn’t seem to have worked quite right, it says uploading:…
But in terms of the column, tables defaults to alphabetizing unless you tell it otherwise. And your import may have meta-data columns so not sure how the column numbers would exactly map. As well as deleting columns when there are problem imports I often delete rows. Try importing with just one row and see if the error occurs, then maybe half the data and divide in halves further to figure out which row has the problem.
Hi @elmps2018, thanks!!
“Try importing with just one row and see if the error occurs…” the files I have send you contain just one row, and a minimum number of columns. I have started with all columns (53) and a fews rows (59); after see the problems, I have cutted the number of rows, first at 3 rows, and after just only 1; because the problems persist, I have eliminate more and more columns; if you see the files I try to import (“efr.imp22.csv”), it has only 1 row to import, and only some columns.
Hi @aortegon! Unfortunately I am not seeing the files. Can you please try uploading just as .csv and .xlsx for the form and the csv you are trying to import?
I have see that in my efr.xlsx, the fiel causing the import problem is “aviso_legal”; I dont know why, it’s a acknowledge type field; when I import without this field, the process end with success, and I can see the instances in the spreadsheet view as well as in the List view… but if try then export, I obtain an empty file… grrrr
Note.- The field “aviso_legal” is the first data my survey take; it’s placed at the 4 row of efr.xlsx
I am not sure what the underlying issue is with the aviso_legal preload is, but one guess is that it may be caps (true vs. TRUE) or how the acknowledge field records that makes a contradiction. If you can push your survey and fill it in without an import then you may be able to export and see how aviso_legal should preload.
Otherwise an easy workaround is to change the acknowledge prompt to a select_one but make it have only one choice (yes). We’ve done that before. Then just preload with that value. That might solve your import causing a two data types problem at least. I am not sure for the export why your file would be empty though… you do have to export-table-by-table if that’s an issue.
Thanks Caroline!
I was see that aviso_legal values are “true”, so, this is the value I employ to load its instances.
To avoid the problem I just think almost exactly what you suggest me: I have changed the acknowledge type to select_multiple with only one option (to emulate the behaviour of acknowledge type, with a checkbox). It will be interesting know whats the issue here… but at least, I dont have the problem with aviso_legal!!
Are you using Microsoft Excel to modify your CSV? The reason I ask is when Excel converts CSV to XLSX it will changes the values originally in the CSV to it’s representation of boolean values instead of the string value.
I have just see that the problem with spanish letters is only a question of codification: converting to UTF-8 solve this issue… but the problem concerning export a table with rows previously imported persisted. I see also that Survey can’t manage theses instances… it never end to start: permanently show the “Wait…” message, so, it’s no possible to edit its.
I have found people who use spanish languages have lots of problems with Excel. I suggest using Libre Calc. It’s free and like Excel, but does better maintaining UTF-8.
@elmps2018 and @elmps2018 , for your information, I have tryed export generating the file with Libre Office-Calc. The result is exactly equal: I obtain the definition and properties file, and also the data folder and the data file, but the data file is empty.
The imported instances can’t be managed by Survey, so I can’t edit any of theses instances; I can’t export, and I can’t Synchronize… so, the imported instances have not value!!
When syncing, I obtain the image message you see:
Hi @aortegon! Sorry you are still having all these troubles! Let’s try to separate out the problems. If you push but DON’T import, but then just enter a new instance, can you export that data? If yes, then the problem is likely in the import file still, if no, then the problem is likely coming from the tables/forms/app in some way rather than the import. If the export works, then you can try pushing again, but with the export you did as the asset in csv (do not open or change in any way, just copy it) and see if you can import that. It will help separate out where the problem is happening.
Also I am not sure if you said, but what version of the app are you using? Just want to check on that.
Thanks another time Caroline!!
Be quoyed, trouble is jet solved… just now, but solved!
I have tryed with your instructions, before comment the problem here: I push my configuration, and it was possible export; I have examined the file I try import, that I have made taking into account the previously exported file. At the end, I have discover some importants reasons affecting the process:
with my local configuration, excel separate fields with “;” and not with “,”. It was easy to discover and avoid.
also for using excel to treat the data, thanks to @W_Brunette I realize that true or false are treated ¡and converted! to booleans, from its original string type.
encoding is critical, at least with spanish characters like ñ, or á, and so on… I must to convert to UTF-8
also, I have understand, after a lot of work, that a string in a select_multiple value look like [“value”], very different of value, and causing importing problems
and, for the last, following the documentation, I was trying to import metadata like _savepoint_creator,… and thanks to the resulting export file, I understand that I need to write username: before my credential.
…after all that ocurrences, and some other,and after a very big lot of attempts, I can say that I understand now a little better the import an export process!!!
@aortegon so glad you were able to work your way through the issues! I know it is extra challenging working with a variety of characters (I often work in Arabic)!
