MySQL/MariaDB to excel

Sometimes you just need to export some data and have it ready in a spreadsheet to email to sales and marketing. Yes i know a csv is a better storage format but the objective is to send the data in excel format so that people use to *just* using excel can carry on doing their work.

Turns out this is really easy to do on a linux server.

First create some sql query, for example pulling sales data from a sales table, and using the stock_details table to get its name where the data is a specific brand.. It does not matter really its going to return a fixed no of columns based on the query, lets call this query.sql

use stock;
select sales.barcode,stock_name,delta from sales,stock_details where sales.barcode=stock_details.barcode AND stock_details.brandid=1;

Next we have a little script to do the magic (getsales.sh) :-

#!/bin.sh
dir=$(mktemp -d)
mysql -u ROUSER --batch < query.sql > "$dir/sales.txt"
tr '\t' ',' < $dir/sales.txt > $dir/sales.csv
libreoffice --headless --convert-to xls:"MS Excel 97" $dir/sales.csv --outdir $dir
echo "sales report" | mutt -a "$dir/sales.xls" -s "Sales report" -- sales@example.com

This has a number of steps. Firstly run the mysql query in “batch” mode this dumps the results as tab separated values. Next libreoffice choked on tab separated so we use tr to translate to comma separated, may be an infilter option would cure this. Then we feed the comma separated into libreoffice in “headless” mode and this generates the Excel file for us. Finally mutt is used to write an email with the attachment.

One thing to note, the msql user in this example ROUSER must have prompt-less  READ ONLY access to the required database/table(s) By prompt-less i mean that the password is not required to be entered each time. To achieve this create a file ~/.my.cfg with the following contents

[client]
password=thepassword

where thepassword is the actual password for ROUSER.  Remember to set permissions on this file to be read/write only to the user. Also remember to make the user in the database only have read only access to the things it needs for additional safety.

Now stick that in a cron job and bingo, automatic spreadsheets via email!