15

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".

Community
  • 1
  • 1
datasn.io
  • 12,564
  • 28
  • 113
  • 154
  • Check this out (it's a workaround): [Export CSV file from MySQL with headings in first row](http://homepage.mac.com/kelleherk/iblog/C711669388/E20060511141025/index.html) – Leniel Maccaferri Jan 04 '11 at 01:30

2 Answers2

24

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!

Amos
  • 868
  • 1
  • 10
  • 22
7

I think this does what you want. I'm using mysql 5.1.60, this outputs field names on the first line. This will use "\t" as the field separator, I'm not sure how to ask for a comma instead.

echo "SELECT * FROM brand;" | mysql -uXXX -pXXX databasename > brand.tsv
Blake Miller
  • 805
  • 11
  • 16
  • 1
    That doesn't quote fields containing the comma character, so will - depending on the nature of your data, of course - potentially generate an invalid CSV file. – Bobby Jack Jul 02 '13 at 15:28
  • @BobbyJack If the separator is tab then there's no need to worry about commas anymore... – rloth Jan 20 '17 at 14:22