ODK 2.0 data export?

Che_Geiser · December 3, 2015, 4:29am

Hi,

I've set up a few forms that are working well now in ODK 2.0. The tables
work, with collected data showing in maps, spreadsheet and list views.

What I cannot figure out now is how to get the data out of the Aggregate
and into a spreadsheet and the photos that are attached into a folder. Even
if the folders are only available by hyperlink to a web address (within the
Aggregate) I can write an Excel macro to pull them out if those hyperlinks
can be exported into a csv file.

With ODK Collect all the there are tools within ODK Aggregate that allow
for exporting and for publishing, as well as the very helpful Map
visualization. The ODK 2.0 data that is synced doesn't show up under the
Submissions tab in the Aggregate so I have not export option.

I've tried ODK Briefcase also but that wouldn't allow me to select the SD
card on my android device - when I select to pull data from Mounted Android
SD Card, then select Choose, then Refresh - the SD card never shows up.
However I can see the Android phone and SD card via Windows Explorer. If it
matters, this is a Nexus 4 with no removable SD card so I'm trying to pull
data from the device memory.

Thanks for any tips!
Che

Mitch_S · December 3, 2015, 6:12pm

Correct. This is yet another work item we have not gotten around to.

At the current time, the process is:

In your office, set up a device to sync to your server.
Sync it.
Launch Tables.
Navigate to the list of tables screen. If you have a custom home screen,
you can get there via the icon on the action bar of that screen.
Choose the icon that looks like "=>".

You can then export the data from each of the tables into a CSV file on the
device.

The images and CSV files will be written to the
/sdcard/opendatakit/tables/output directory.

Zip those up and copy them onto your desktop.

···

=============== If you are a Java developer, we have code written for a specific deployment that would be the starting point for an ODK Briefcase-like data-downloading app for the 2.0 datasets. It is not generic and would need to be either made generic or customized for your specific needs.

On Wed, Dec 2, 2015 at 8:29 PM, Che Geiser chegeiser@gmail.com wrote:

Hi,

I've set up a few forms that are working well now in ODK 2.0. The tables
work, with collected data showing in maps, spreadsheet and list views.

What I cannot figure out now is how to get the data out of the Aggregate
and into a spreadsheet and the photos that are attached into a folder. Even
if the folders are only available by hyperlink to a web address (within the
Aggregate) I can write an Excel macro to pull them out if those hyperlinks
can be exported into a csv file.

With ODK Collect all the there are tools within ODK Aggregate that allow
for exporting and for publishing, as well as the very helpful Map
visualization. The ODK 2.0 data that is synced doesn't show up under the
Submissions tab in the Aggregate so I have not export option.

I've tried ODK Briefcase also but that wouldn't allow me to select the SD
card on my android device - when I select to pull data from Mounted Android
SD Card, then select Choose, then Refresh - the SD card never shows up.
However I can see the Android phone and SD card via Windows Explorer. If it
matters, this is a Nexus 4 with no removable SD card so I'm trying to pull
data from the device memory.

Thanks for any tips!
Che

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

You received this message because you are subscribed to the Google Groups
"ODK Community" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to opendatakit+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Mitch Sundt
Software Engineer
University of Washington
mitchellsundt@gmail.com

Che_Geiser · December 4, 2015, 1:02am

This worked and exported the geotagger.csv, geotagger.definitions.csv and
geotagger.propertiese.csv files.

Two issues I've found though:

There is no folder with actual pictures. The geotagger.csv does list
the picture file name, but no files. The _id column contains the uuid that
is the same as the folder names found in instances folder. Is the best path
to get the pictures to simply download the instances folder along with the
csv files?
In data entry fields that have text, if a comma is used, it throws of
the column that the data is stored to. I've attached a downloaded data file
that shows this in rows 7, 8 and 9.

The second issue above is very serious if users are not aware that they
cannot use commas.

Thank you,
Che

geotagger.csv (3.35 KB)

Che_Geiser · December 4, 2015, 1:12am

FYI - I've also tried the data import as described here:
https://opendatakit.org/help/form-design/workarounds/#csv_output

This did not work, the comma still throws off the columns where the data is
entered.

Mitch_S · December 4, 2015, 2:40am

Ah, right. rev 126....

The images are not copied when exporting on rev 126, and the file paths are
not altered, so you will want to pull the /sdcard/opendatakit/tables/tables
tree, as all the image files are referenced relative to that. I.e., in
the csv, you will see:

tables/geotagger/instances/uuid_72a337e3_4809_4732_84b4_d5b8da41a9cc/1449174422878_imei_355136052420075.jpg

and 'tables/...' is /sdcard/opendatakit/tables/tables/...

···

---------- And, yes, thank you. That is a very bad bug.

On Thu, Dec 3, 2015 at 5:12 PM, Che Geiser chegeiser@gmail.com wrote:

FYI - I've also tried the data import as described here:
https://opendatakit.org/help/form-design/workarounds/#csv_output

This did not work, the comma still throws off the columns where the data
is entered.

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

You received this message because you are subscribed to the Google Groups
"ODK Community" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to opendatakit+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Mitch Sundt
Software Engineer
University of Washington
mitchellsundt@gmail.com

Che_Geiser · December 8, 2015, 7:53pm

Would this be a simple change to the formDef.json to have any 'text' fields
surrounded by a pipe, or quotes so that that could be used as a text
qualifier when importing to Excel?

I unfortunately am not a programmer and have searched for an answer but
really don't know how to search for this. If it really is complicated don't
hesitate to say "just wait for a future update".

Thank you for your help!

Mitch_S · December 8, 2015, 8:03pm

It is a future-update sort of thing.

However:

You can pull the SQLite database off of the device and export the CSV from
that database using a database viewer tool.

To do that, assuming you have adb and grunt set up (which you had to do for
the app designer)

In the app designer directory, type:

grunt adbpull-db

This will pull the database into the app/output/db folder.

It will be named something like 0000000000000001.db

Then download SQLiteBrowser http://sqlitebrowser.org/

And open the database file using that tool.

Under the File menu, there is an Export...

and it looks like you can export individual tables to CSVs.

I haven't tried this myself.

···

On Tue, Dec 8, 2015 at 11:53 AM, Che Geiser wrote:

Would this be a simple change to the formDef.json to have any 'text'
fields surrounded by a pipe, or quotes so that that could be used as a text
qualifier when importing to Excel?

I unfortunately am not a programmer and have searched for an answer but
really don't know how to search for this. If it really is complicated don't
hesitate to say "just wait for a future update".

Thank you for your help!

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

You received this message because you are subscribed to the Google Groups
"ODK Community" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to opendatakit+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Mitch Sundt
Software Engineer
University of Washington
mitchellsundt@gmail.com

Che_Geiser · December 8, 2015, 8:29pm

hmm. the grunt adbpull-db didn't work, though grunt adbpush-app did work to
load the app.

I will wait for the update and for now manually edit the csv.

Thank you!

Mitch_S · December 9, 2015, 1:03am

you could also do:

adb pull /sdcard/opendatakit/tables/metadata/webDb/http_localhost_8635/ .

which would pull the database into whatever your current directory is.

(this is the underlying command that 'grunt adbpull-db' performs).

···

On Tue, Dec 8, 2015 at 12:29 PM, Che Geiser wrote:

hmm. the grunt adbpull-db didn't work, though grunt adbpush-app did work
to load the app.

I will wait for the update and for now manually edit the csv.

Thank you!

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

You received this message because you are subscribed to the Google Groups
"ODK Community" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to opendatakit+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Mitch Sundt
Software Engineer
University of Washington
mitchellsundt@gmail.com