We are using the ODK 2.0 suite, and we are going to have to update our survey database regularly as we are in the field. For example, when members of a household split over time, we need to create new households for them, assign them to the right team, etc. We have the process for this all set up in STATA to take a finalized household and identify any splits, assign them an id, and generate a new household. Or we have to systematically reassign households that have moved to a different team (we are doing this at a fairly large scale, so cannot just edit one by one in tables)–we can do this in STATA as well, systematically, based on addresses.
STATA is successfully generating csvs with all the right data to then add to our database (Aggregate 1.4.15) for new households and modified households. What we have been doing is re-pushing to a tablet, importing the revised csv and then syncing. This is a rather sub-optimal process for updating.
Having been playing around with downloading in suitcase, it looks like there is also a possibility to upload in suitcase, but alas, no documentation. I worked through a first error message which implied that the uploaded structure needed to include sub-folders of assets and tables, and got to the point of “Uploading…” but then the message “An error occurred.”
I know there was a pending documentation item on uploading in suitcase:
Any updates on that or anyone who can point me in the right direction in the meantime? Help much appreciated so we can test the uploading before we go to the field in a week or so!
The CSV file used for this operation is mostly the same as the one used for data pre-loading on Android, with one small difference.
The first column of the CSV file has to be an operation column.
The valid values for this column are: FORCE_UPDATE, UPDATE, NEW and DELETE
The value in this column instructs Suitcase how to treat that row.
UPDATE and FORCE_UPDATE
Both of these options are used for updating data on the server. They update data on the server by matching the _id. FORCE_UPDATE retries updates that failed with a more aggressive strategy.
Used for creating new rows.
Used for deleting rows. Rows are matched using _id.
And already thought of a question! So we are not using cloudendpoint, just aggregate on a SQL server (for fielding, we’ll test it first on an appengine setup). Will this still work just as written, or do we need to modify the command in any way?
Happy to report that we have successfully updated some data! Still working through all the details of the different operation options and deciphering the (many pages when it works…) output in terminal. But the concept seems to be working well so far! Extra appreciation to whomever programmed in all the informative error messages for the command that we were able to work through, like “CSV does not contain metadata column:_row_etag” because then I could figure out to solve it by adding that column!
org.apache.commons.cli.UnrecognizedOptionException: Unrecognized option: -delete
Can someone help post the correct parameters and syntax for Sync Endpoint? Many thanks!
@elmps2018 is correct if you are trying to delete specific rows. If that is your intent the CSV should have the delete in the operation column with the rest of the row.
IF instead you are trying to delete the entire table including it’s associated files and definitions. Then you can use this command based on me guessing from the line you showed. java -jar ODK-X_Suitcase_v2.1.6.jar -cloudEndpointUrl https://precise.obgyn.ubc.ca -appId default -dataVersion 2 -username demo -password pwd123 -tableOp DELETE -tableId profileSummary
It would be helpful if you tell us precisely what you are trying to do so we can give improved answers.
@elmps2018@W_Brunette Many thanks for your immediate help!! You are right and I should run with “-update” command.
This is the file profileSummary_link_extra.csv (1.8 KB) I am using. I changed the value of operation column to “DELETE”, and still kept “_deleted” unchanged (false). I found when I saved as “CSV (UTF-8)” format in Microsoft Excel, it was saved in the “UTF-8 BOM” format. I converted to the “UTF-8” format and it was working well.
By the way, which columns are necessary or unnecessary to keep if I want to delete? What about updating records? Can I update “_id” column to link with a different record in another table?
You definitely need the _id and the operation columns. I am not sure if any others are needed, but if you want to change which instances you are altering, you can change the _id to whatever instance you want to delete or update. If you want to do something in a different table, you can, but will need to use the _id within that table and also the right tableID when you are running the command.
What we generally did for updates using Suitcase was to download the data in a csv and then add the operation column to that csv with DELETE or whatever and then upload that file. The processing can be done just in the csv if you are doing only a little bit, or if going to scale you can use STATA or SPSS or R (for instance, we used STATA to identify and prepare a csv to delete completed records) – just be super careful about all the UTF formatting as you note it can get messed up in Excel etc.