MySQL CSV’s with column headings — Part 1

In a previous post I discussed the stumbling blocks and security concerns that the mysql documentation doesn’t tell you about having a mysql user create csv files on the filesystem.

I ran into yet another issue. In order to do what I want with these csv’s, they have to have column headers. I thought surely by now MySQL must have come up with some syntax in the query to do this, but Paul DuBois assured us all in a 2006 forum post, that there is no such thing.  I thought maybe Zend Framework might have such a thing in it’s DB class, but I didn’t locate one.  Correct me if I’m wrong–Zend Framework is huge, and I might have missed it.

So, what to do? We have several options:

1. Install the “csv storage engine”. I have no experience with this but it implies that csv is the native storage format. Maybe that would be ideal for someone who mostly interacts with spreadsheets but I don’t.
2. Manually prepend the column headings after the fact. Fine if you don’t do it often, and de facto, what most people end up doing.
3. Write a union query where you simply union the headings onto the top of the dump as shown in Example #1 below. This has the problem that it truncates the ensuing fields to the string length of the header label. Not good.
4. Query the length for each data column from metadata as shown in Example #2. CAST the headings to the data column length as shown in Example #3 below. This works if your query is relatively static, and it certainly beats prepending the columns each time you run the query. But if you are doing arbitrary queries, or if your data schema changes, this will become a maintenance nightmare.
5. Write some code to automate   the process. It will be fiddley diddley pain in the arse regular expression code that no one likes to write, because you basically have to parse the query.

EXAMPLE 1: Hard coded headers and risk truncation

SELECT 'Fiscal Year','Location','Sales'
UNION
SELECT FY, Loc, Sales INTO OUTFILE 'salesreport.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM SalesTable;

EXAMPLE 2: Find out what length the data columns are

describe m1_tax_class;
+------------+----------------------------+------+-----+----------+----------------+
| Field      | Type                       | Null | Key | Default  | Extra          |
+------------+----------------------------+------+-----+----------+----------------+
| class_id   | smallint(6)                | NO   | PRI | NULL     | auto_increment | 
| class_name | varchar(255)               | NO   |     |          |                | 
| class_type | enum('CUSTOMER','PRODUCT') | NO   |     | CUSTOMER |                | 
+------------+----------------------------+------+-----+----------+----------------+

EXAMPLE 3: Cast the headers to exactly right size

SELECT CAST('Fiscal Year' AS CHAR(11)),CAST('Location' AS CHAR(50),
CAST('Sales' AS CHAR(25)) 
UNION
SELECT FY, Loc, Sales INTO OUTFILE 'salesreport.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM SalesTable;

If you have any sorts applied to the data you need to turn the data SELECT into a subquery by enclosing it in parentheses.

This post provides an example of a union, a metadata query, and casting, and should be fine for many applications, except for those that want the entire process completely automated. That solution would require some code. Look for it in an upcoming post coming soon to Hot Dorkage.