MySQL – INTO OUTFILE and headers

[adsense id=”0514458240″ width=”468″ height=”60″]

This post shows how one can add headers to the contents being exported using the MySQL feature OUTFILE. If we look at a typical case using OUTFILE:

mysql> select * into outfile '/tmp/demo.txt' fields terminated by ',' optionally enclosed by '"' from t1;
Query OK, 8 rows affected (0.00 sec)

mysql> quit
Bye
$ cat /tmp/demo.txt 
"ronan4",100,115
"ronan4",155,116
"ronan4",225,250
"ronan4",300,275
"ronan4",325,350
"ronan4",1,2
"ronan4",1,3
"t13",7,6

The data is correct but as headers are missing this may require additional work if we are going to attempt to import this into an Excel file by adding in the headers. Alternatively we can use UNION operation to first add the headers followed by the data.

mysql> select 'Name', 'position', 'points'
    -> union
    -> select * into outfile '/tmp/demo.csv' 
    -> fields terminated by ','
    -> optionally enclosed by '"'
    -> from t1;
Query OK, 9 rows affected (0.00 sec)
$ cat /tmp/demo.csv
"Name","position","points"
"ronan4","100","115"
"ronan4","155","116"
"ronan4","225","250"
"ronan4","300","275"
"ronan4","325","350"
"ronan4","1","2"
"ronan4","1","3"
"t13","7","6"

There is one caveat to this. What is we want to order the data. By adding an order by clause we will end up getting the following:

mysql> select 'Name', 'position', 'points'
    -> union
    -> select * into outfile '/tmp/demo.csv' 
    -> fields terminated by ','
    -> optionally enclosed by '"'
    -> from t1
    -> order by col2;
Query OK, 9 rows affected (0.00 sec)

mysql> quit
Bye
$ cat /tmp/demo.csv 
"ronan4","1","2"
"ronan4","1","3"
"ronan4","100","115"
"ronan4","155","116"
"ronan4","225","250"
"ronan4","300","275"
"ronan4","325","350"
"t13","7","6"
"Name","position","points"

Not exactly what we were looking for. Using the order by in this way causes the header and the data to be merged and sorted. By placing the data SELECT statement within parenthesis will sort the data but leave the headers untouched as follows:

mysql> select 'Name', 'position', 'points'
    -> union
    -> (select * into outfile '/tmp/demo.csv' 
    -> fields terminated by ','
    -> optionally enclosed by '"'
    -> from t1
    -> order by col2);
Query OK, 9 rows affected (0.00 sec)

mysql> quit
Bye
$ cat /tmp/demo.csv 
"Name","position","points"
"ronan4","100","115"
"ronan4","155","116"
"ronan4","225","250"
"ronan4","300","275"
"ronan4","325","350"
"ronan4","1","2"
"ronan4","1","3"
"t13","7","6"