Export CSV file from MySQL with headings in first row


A tip posted by Heywood in the MySQL manual on how to export headings in your CSV export....

When using the SELECT ... INTO OUTFILE syntax, use a UNION to add headers. Here's an example for CSV output:
SELECT 'Fiscal Year','Location','Sales'
UNION
SELECT FY, Loc, Sales INTO OUTFILE 'salesreport.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM SalesTable;
This will add the text headers Fiscal Year, Location and Sales to your fields. Only caveat is with an ORDER BY statement, if you don't want your headers sorted along with your data you need to enclose it in parenthesis:
SELECT 'Fiscal Year','Location','Sales'
UNION
{SELECT FY, Loc, Sales INTO OUTFILE 'salesreport.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM SalesTable
ORDER BY Sales DESC);

Posted: Thu - May 11, 2006 at 02:10 PM        

Published by