Aggregate to mysql into word template

Support
2

Welcome to the ODK forum, @Clinton_P! We're glad you're here. When you get a chance, please introduce yourself here. I'd also encourage you to add a real picture as your avatar because it helps build community!

I can't help you with the Word part, but I can tell how I'd go about moving your data from Aggregate to some MySQL tables that you could hook up to your Word templates.

Aggregate can't export submission data directly into another database. You can export or publish data and then load it into your database, but it will involve some manual work and technical expertise.

First, I'd advise to define a process that you can manually run, and then think about automating it.

How I'd move data from Aggregate to a MySQL database

  1. I'd use Briefcase to pull the form and export it to CSV format.

  2. I'd create a table in MySQL with the same column structure as the CSV files I have.

  3. I'd use the MySQL load function to make MySQL read the CSV file and insert all the lines into the table I've created.

    Here's a good SO post about this: https://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table

If the form has repeat groups, I'd repeat the process described above as many times as CSV files I get.

How I'd go about automating this
I'd create all the MySQL tables I need beforehand, and then I'd write a shell script that runs these commands:

  1. Pull the form: java -jar briefcase.jar -plla etc.

  2. Export the form: java -jar briefcase.jar -e blah blah

  3. Load exported CSV files into MySQL:

    mysql db_name < load_export.sql

    Provided that the file load_export.sql has the SQL required to do the loading of the CSV file. Something like this:

    LOAD DATA INFILE "/briefcase/export/path/My Form.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

    (I haven't tried this exact SQL... the actual column separator and escape characters may require some adjustment)

  4. Repeat for each CSV file Briefcase exports (one main file, one per repeat group)

After checking the script works, I'd automate running that every hour/day/week/month using crontab (macOS, Linux) or the Windows Scheduler

1 Like