Aggregate to mysql into word template

Support
1

Hi, Would it be possible if you may assist, I want to publish data(text and Images) from aggregate to a mysql server from there to be merged into a pre-designed word document. I have got aggregate setup, and accepting submissions fine, I just cannot get data out of aggregate and into mysql and then into my word template.

So far I have tried the Briefcase, But i discovered its only for local use,

Ideally i want to run this on an apache/ tomcat/ mysql server.

What would be the best approach…

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
3

Hi Guillermo, Thanks alot for that explanation, I've just got one question, How would I handle images ?

Would i have to read the csv, and convert them to base64 for storage in the DB ?

4

Hi, @Clinton_P!

I would advise against storing images in your database. Not only it's a well-known anti-pattern, but it will probably require some programming at your end.

I think your best option would be to make your Word template take the files directly from your hard drive. Briefcase will download them for you from Aggregate and place them in a media folder inside the directory of your choosing when exporting the forms. The CSV files will contain the paths to those files in all the binary attachment columns.

Once you load your CSVs to a MySQL table, you could replace those relative paths to absolute paths to make it easier for your Word template to locate them. Let's say a form has a binary field called some_photo and that you've exported it to the path /some/export/path.

  • You start with a table with a text column some_photo containing values such as media/1538040007350.jpg

  • We want to change those values into something like /some/export/path/media/1538040007350.jpg

  • We can achieve that by running this SQL:

    UPDATE table_name SET some_photo = CONCAT('/some/export/path/', some_photo);

I hope this helps :slight_smile:

5

Hi. Yes this helps alot, I wasn't sure whether to encode them in base64, But storing the path is much simpler, thanks alot for your assistance, much appreciated.

6

Happy to help!

This looks like a fine combination of ODK tools you're using. Once you get it working it would be awesome if you'd write a post in the https://forum.getodk.org/c/showcase forum. I think it would be helpful for other users :wink:

1 Like