Add column headers to a MySQL Outfile

Unfortunately the MySQL SELECT INTO OUTFILE command doesn’t support an option to output the headers of the result set you are exporting. A feature request has been open for over 2 years to sort this with no apparent activity.

A few people have had the idea of using a UNION ALL to include the headers but this becomes tedious with large queries. Tools like sqlyog are handy but can painfully slow for large result sets due to RBAR methods.

For large result sets I like to first run;

# Create temp table
CREATE TEMPORARY TABLE tmp_MyTable
ENGINE = MEMORY
SELECT col1, col2, col3, col4 # etc
FROM myTable
INNER JOIN myTable2
	ON myTable.Id = myTable2.Id;
 
# Get the column info
DESCRIBE tmp_MyTable;

This will output the result set we want to export into a temporary table. We can then get the column headers using the DESCRIBE command. Then I export my result set to a file using the OUTFILE command.

SELECT *
INTO OUTFILE '/home/user/export.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM tmp_MyTable;

Now it’s roll your sleeves up time with the Linux command-line and sed.

sed -i '1iOne,Two,Three,Four,Five' /home/user/export.csv

This command will add a single header line, to the file previously exported, and should end up looking something like below…

One,Two,Three,Four,Five
1,2,3,4,5
1,2,3,4,5
1,2,3,4,5

Leave a Reply

Current ye@r *