Transferring Raw Data to MySQL

Dear Community,

I'm based in Lesotho and am experiencing some predicament here (i'm new to
this technology); we have a complete functional web-based information
system stores its data on MySQL. Just like in any developing country we're
faced with the challenge of limited internet connectivity.
So this is where i hit the wall; I want to know if i can collect data using
ODK Collect and then have all the data collected transferred straight to
MySQL so that information system will play with the data.

First, note that ODK Collect is designed to work under conditions of
limited, intermittent network connectivity, or even without any internet.

There are two ways this could work:

(1) you can use ODK Aggregate installed on Google AppEngine as the
recipient of all your data submissions from your surveyors' devices. This
would require each surveyor to be within reach of a full internet
connection at the time of their upload of data.

Then, you can use ODK Briefcase at your data processing center to download
a full copy of the uploaded data from ODK Aggregate, and export CSV files
for all of that data. These CSV files can then be imported into your MySQL
server via LOAD DATA INFILE
http://dev.mysql.com/doc/refman/5.6/en/load-data.html

The entire system requires occasional internet connectivity from all data
collection points and from your data center where you are running ODK
Briefcase.

ODK Briefcase now has command line functionality which should allow you to
automate the extract-and-transfer process using, e.g., a scheduled task
(cron job).

A minor variant of this would involve setting up your own ODK Aggregate
server to receive all submissions from ODK Collect within your own data
center, rather than leveraging Google AppEngine to host it. This requires
greater technical expertise.

Another minor variant would have you implement a webserver that implemented
one of the data formats that ODK Aggregate can publish. This webserver
would then take the published data and insert it into your database. The
various publishers are described here
http://opendatakit.org/use/aggregate/data-transfer/ ODK Aggregate would
then be configured to publish submitted data into this webserver as it
flowed in. If ODK Aggregate were hosted on Google AppEngine, there are
various retry mechanisms that should allow the publisher to continue
operating even if the internet connectivity to your data center is
intermittent.

(2) or, you can skip ODK Aggregate entirely.

This is a much more manual submission workflow:

Field supervisors review the contents of each device at the end of the day
or survey period

All surveys are required to be Finalized -- any that are not are deleted.

Using a file manager like OI File Manager on the device, the supervisor
creates a ZIP file of the /sdcard/odk directory, naming it something like
deviceID-2014-09-26.zip

They then send that zip via some mechanism (USB drive, e-mail, etc.) to a
data center (there is an option to send a file via e-mail within OI File
Manager).

They then open ODK Collect and use the Delete Saved Forms button to delete
all the filled-in submissions on the device.

Finally, they review receipt-confirmations (sent from the data center) for
the zip files sent on previous days, and delete all zip files on the device
that have had their receipt confirmed (the one from the current survey
period remains on the device until it is confirmed in this manner).

At the data center, you have a staff that accepts the zip files (via
courier, mail, e-mail, etc.).

They do a quality assurance pass by deleting the /sdcard/odk directory on a
device, copying the zip to the device, un-zipping it, and opening ODK
Collect to review the submitted data, and confirm that all submissions are
finalized. This works best if the devices used during this step exactly
match the devices used in the field.

If the quality assurance pass succeeds, they then send the zip on to a data
curator.

The data curator unzips the file and uses ODK Briefcase to directly input
the data onto their desktop computer.

And then you use the Export capability to generate a CSV and load it into
your local MySQL database, as in (1)

··· ------- Mitch

On Thu, Sep 25, 2014 at 2:04 PM, Makhate Makhate makhatemakhate@gmail.com wrote:

Dear Community,

I'm based in Lesotho and am experiencing some predicament here (i'm new to
this technology); we have a complete functional web-based information
system stores its data on MySQL. Just like in any developing country we're
faced with the challenge of limited internet connectivity.
So this is where i hit the wall; I want to know if i can collect data
using ODK Collect and then have all the data collected transferred
straight to MySQL so that information system will play with the data.

--

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

Thank you very much Mitch,

Let me assimilate all of these....

··· On Thu, Sep 25, 2014 at 11:47 PM, Mitch Sundt wrote:

First, note that ODK Collect is designed to work under conditions of
limited, intermittent network connectivity, or even without any internet.

There are two ways this could work:

(1) you can use ODK Aggregate installed on Google AppEngine as the
recipient of all your data submissions from your surveyors' devices. This
would require each surveyor to be within reach of a full internet
connection at the time of their upload of data.

Then, you can use ODK Briefcase at your data processing center to download
a full copy of the uploaded data from ODK Aggregate, and export CSV files
for all of that data. These CSV files can then be imported into your MySQL
server via LOAD DATA INFILE
http://dev.mysql.com/doc/refman/5.6/en/load-data.html

The entire system requires occasional internet connectivity from all data
collection points and from your data center where you are running ODK
Briefcase.

ODK Briefcase now has command line functionality which should allow you to
automate the extract-and-transfer process using, e.g., a scheduled task
(cron job).

A minor variant of this would involve setting up your own ODK Aggregate
server to receive all submissions from ODK Collect within your own data
center, rather than leveraging Google AppEngine to host it. This requires
greater technical expertise.

Another minor variant would have you implement a webserver that
implemented one of the data formats that ODK Aggregate can publish. This
webserver would then take the published data and insert it into your
database. The various publishers are described here
http://opendatakit.org/use/aggregate/data-transfer/ ODK Aggregate would
then be configured to publish submitted data into this webserver as it
flowed in. If ODK Aggregate were hosted on Google AppEngine, there are
various retry mechanisms that should allow the publisher to continue
operating even if the internet connectivity to your data center is
intermittent.

(2) or, you can skip ODK Aggregate entirely.

This is a much more manual submission workflow:

Field supervisors review the contents of each device at the end of the day
or survey period

All surveys are required to be Finalized -- any that are not are deleted.

Using a file manager like OI File Manager on the device, the supervisor
creates a ZIP file of the /sdcard/odk directory, naming it something like
deviceID-2014-09-26.zip

They then send that zip via some mechanism (USB drive, e-mail, etc.) to a
data center (there is an option to send a file via e-mail within OI File
Manager).

They then open ODK Collect and use the Delete Saved Forms button to delete
all the filled-in submissions on the device.

Finally, they review receipt-confirmations (sent from the data center) for
the zip files sent on previous days, and delete all zip files on the device
that have had their receipt confirmed (the one from the current survey
period remains on the device until it is confirmed in this manner).

At the data center, you have a staff that accepts the zip files (via
courier, mail, e-mail, etc.).

They do a quality assurance pass by deleting the /sdcard/odk directory on
a device, copying the zip to the device, un-zipping it, and opening ODK
Collect to review the submitted data, and confirm that all submissions are
finalized. This works best if the devices used during this step exactly
match the devices used in the field.

If the quality assurance pass succeeds, they then send the zip on to a
data curator.

The data curator unzips the file and uses ODK Briefcase to directly input
the data onto their desktop computer.

And then you use the Export capability to generate a CSV and load it into
your local MySQL database, as in (1)


Mitch

On Thu, Sep 25, 2014 at 2:04 PM, Makhate Makhate <makhatemakhate@gmail.com wrote:

Dear Community,

I'm based in Lesotho and am experiencing some predicament here (i'm new
to this technology); we have a complete functional web-based information
system stores its data on MySQL. Just like in any developing country we're
faced with the challenge of limited internet connectivity.
So this is where i hit the wall; I want to know if i can collect data
using ODK Collect and then have all the data collected transferred
straight to MySQL so that information system will play with the data.

--

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

--

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 a topic in the
Google Groups "ODK Community" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/opendatakit/RqnzfWwlYOQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
opendatakit+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Regards,
Makhate Makhate (Lesotho)

"Forgiveness received makes a jubilant soul, forgiveness granted purifies a
soul"

hi mitch,

can i ask something about my project - im trying to send a RAW data from a laboratory machine "SWELAB" TO mysql / php ., can you please help me or suggest any way for this - what process do i need to do ., thank you in advance for helping me about this matters. ill send you a sample of the raw data which i copy using the hyperterminal -- TIA :slight_smile:

parse.txt (8.79 KB)

I do not give generic advice like this.

··· On Tue, Jan 27, 2015 at 8:06 PM, wrote:

hi mitch,

can i ask something about my project - im trying to send a RAW data from
a laboratory machine "SWELAB" TO mysql / php ., can you please help me or
suggest any way for this - what process do i need to do ., thank you in
advance for helping me about this matters. ill send you a sample of
the raw data which i copy using the hyperterminal -- TIA :slight_smile:

--

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