sqlmysqlcsvexportheader-row

MySQL dump into CSV text files with column names at the top?

Possible Duplicate:
How to export / dump a MySql table into a text file including the field names (aka headers or column names)

I use this SQL snippet to dump a table into CSV text files:

SELECT * FROM brand INTO OUTFILE "e:/brand.csv" FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY "\n";

However this approach doesn't add the column names at the beginning of the CSV file. My question is how to select all the column / field names as well, just like what phpMyAdmin does when you export the table and select "Put fields names in the first row".

Solution

  • I figured out a way around having to manually enter those names as long as you're running MySQL 5 or higher. Here it is, written as a bash script for running on a unix command line:

    DBNAME=<database_name>
TABLE=<table_name>

FNAME=/path/to/output/dir/$(date +%Y.%m.%d)-$DBNAME.csv

#(1)creates empty file and sets up column names using the information_schema
mysql -u <username> -p<password> $DBNAME -B -e "SELECT COLUMN_NAME FROM information_schema.COLUMNS C WHERE table_name = '$TABLE';" | awk '{print $1}' | grep -iv ^COLUMN_NAME$ | sed 's/^/"/g;s/$/"/g' | tr '\n' ',' > $FNAME

#(2)appends newline to mark beginning of data vs. column titles
echo "" >> $FNAME

#(3)dumps data from DB into /var/mysql/tempfile.csv
mysql -u <username> -p<password> $DBNAME -B -e "SELECT * INTO OUTFILE '/var/mysql/tempfile.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' FROM $TABLE;"

#(4)merges data file and file w/ column names
cat /var/mysql/tempfile.csv >> $FNAME

#(5)deletes tempfile
rm -rf /var/mysql/tempfile.csv

    While not the most graceful solution, i'm sure it can be compressed into a single line by someone who knows SQL and/or bash a little better than me...

    What it does is:

    1. uses MySQL's information schema to create an empty CSV w/ column headers
    2. appends an extra newline to that empty CSV so your data will begin appearing a new line
    3. uses a pretty standard "SELECT * INTO OUTFILE..." query to create a CSV full of data
    4. appends the data file onto the file w/ column headers
    5. deletes the (temporary) data file

    Good luck, and if you clean it up, post your results!