Include headers when using SELECT INTO OUTFILE?

Question

Is it possible to include the headers somehow when using the MySQL INTO OUTFILE?

Answer 1

You'd have to hard code those headers yourself. Something like:

SELECT 'ColName1', 'ColName2', 'ColName3'
UNION ALL
SELECT ColName1, ColName2, ColName3
    FROM YourTable
    INTO OUTFILE '/path/outfile'

Answer 2

I simply make 2 queries, first to get query output (limit 1) with column names (no hardcode, no problems with Joins, Order by, custom column names, etc), and second to make query itself, and combine files into one CSV file:

CSVHEAD=`/usr/bin/mysql $CONNECTION_STRING -e "$QUERY limit 1;"|head -n1|xargs|sed -e "s/ /'\;'/g"`
echo "\'$CSVHEAD\'" > $TMP/head.txt
/usr/bin/mysql $CONNECTION_STRING -e "$QUERY into outfile '${TMP}/data.txt' fields terminated by ';' optionally enclosed by '\"' escaped by '' lines terminated by '\r\n';"
cat $TMP/head.txt $TMP/data.txt > $TMP/data.csv

Answer 3

I faced similar problem while executing mysql query on large tables in NodeJS. The approach which I followed to include headers in my CSV file is as follows

1. Use OUTFILE query to prepare file without headers

       SELECT * INTO OUTFILE [FILE_NAME] FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED 
       BY '\"' LINES TERMINATED BY '\n' FROM [TABLE_NAME]
   

2. Fetch column headers for the table used in point 1

       select GROUP_CONCAT(CONCAT(\"\",COLUMN_NAME,\"\")) as col_names from 
       INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = [TABLE_NAME] AND TABLE_SCHEMA 
       = [DATABASE_NAME] ORDER BY ORDINAL_POSITION
   

3. Append the column headers to the file created in step 1 using prepend-file npm package

Execution of each step was controlled using promises in NodeJS.

Answer 4

I think if you use a UNION it will work:

select 'header 1', 'header 2', ...
union
select col1, col2, ... from ...

I don't know of a way to specify the headers with the INTO OUTFILE syntax directly.

Answer 5

For complex select with ORDER BY I use the following:

SELECT * FROM (
    SELECT 'Column name #1', 'Column name #2', 'Column name ##'
    UNION ALL
    (
        // complex SELECT statement with WHERE, ORDER BY, GROUP BY etc.
    )
) resulting_set
INTO OUTFILE '/path/to/file';

Answer 6

This is an alternative cheat if you are familiar with Python or R, and your table can fit into memory.

Import the SQL table into Python or R and then export from there as a CSV and you'll get the column names as well as the data.

Here's how I do it using R, requires the RMySQL library:

db <- dbConnect(MySQL(), user='user', password='password', dbname='myschema', host='localhost')

query <- dbSendQuery(db, "select * from mytable")
dataset <- fetch(query, n=-1)

write.csv(dataset, 'mytable_backup.csv')

It's a bit of a cheat but I found this was a quick workaround when my number of columns was too long to use the concat method above. Note: R will add a 'row.names' column at the start of the CSV so you'll want to drop that if you do need to rely on the CSV to recreate the table.