19

In MySql's interpreter, it's very easy to dump a table to the screen along with its field names.

There seems to be no simple way to export a table to a tab-delimted or CSV outfile including its column headers.

I'm trying to do this using only SQL or the Linux command line, without writing a program in another language.

Thank you

Maksym Gontar
  • 22,765
  • 10
  • 78
  • 114
Dan Goldstein
  • 24,229
  • 18
  • 37
  • 41

6 Answers6

21

Piping the query to the commandline client outputs a tab separated list with the column names as the first line

$ echo "select * from surveys limit 5" | mysql -uroot -pGandalf surveys
phone   param1  param2  param3  param4  p0      p1      p2      p3      audio4  code    time
XXXXXXXXX       2008-07-02      11:17:23        XXXXXXXX        SAT     -       -       -       -       -       ERROR   2008-07-02 12:18:32
XXXXXXXXX       2008-07-02      11:22:52        XXXXXXXX        SAT     -       -       -       -       -       COLGADO 2008-07-02 12:04:29
XXXXXXXXX       2008-07-02      11:41:29        XXXXXXXX        SAT     -       -       -       -       -       COLGADO 2008-07-02 12:07:22
XXXXXXXXX       2008-07-02      12:16:19        XXXXXXXX        SAT     1       1       1       9       XXXXXXXXX_4.wav     OK      2008-07-02 16:14:27
XXXXXXXXX       2008-07-02      08:21:25        XXXXXXXX        SAT     1       1       1       1       XXXXXXXXX_4.wav     OK      2008-07-02 12:29:40
Vinko Vrsalovic
  • 330,807
  • 53
  • 334
  • 373
  • 3
    It's a hack rather than mysql provided function. I guess it may not work for other OSes. – janetsmith Aug 03 '11 at 01:02
  • 2
    It's not a hack, per se. It of course may not work on systems that do not provide both a sane command line interface and the mysql command line client. – Vinko Vrsalovic Aug 04 '11 at 21:34
  • Will this properly quote (escape) column names and data? – LarsH Apr 17 '15 at 18:04
  • I used in this way, thanks @VinkoVrsalovic echo "select user_pass from wp_users"| mysql -uroot -proot wp_database > passwordList.txt – aiffin Jan 22 '18 at 11:07
9

This little script should do it:

-- 1. choose the table and the output file here / this should be the only input

select 'mytable' into @tableName;
select 'c://temp/test.csv' into @outputFile;

-- 2. get the column names in a format that will fit the query

select group_concat(concat("'",column_name, "'")) into @columnNames from information_schema.columns
where table_name=@tableName;

-- 3. build the query

SET @query = CONCAT(
"select * from
((SELECT ",@columnNames,")
UNION
(SELECT * FROM `",@tableName,"`)) as a
INTO OUTFILE '", @outputFile, "'");

-- 4. execute the query

PREPARE stmt FROM @query;
EXECUTE stmt;
cafe876
  • 171
  • 2
  • 1
  • 1
    Will the columns as ordered by "SELECT * ..." be in the same order as the columns as listed from the information_schema.columns query? Not trolling here - just curious... – Chris Markle Oct 22 '12 at 21:06
  • @cafe876: Thanks for this solution. I just wonder why the apostrophe ` is needed. If I remove the both apostrophes the query does not work. If I want to add a constraint (e.g.: SELECT 'x > 1' INTO @constr) at this line: (SELECT * FROM `",@tableName,"` WHERE ",@cosntr,)) as a" -> this does not work. Any idea? – giordano Sep 12 '13 at 13:46
  • @giordano: If you want to get an answer, you'll probably need to better define "does not work"; e.g. what error message did you see? You may also need to ask a separate question. However the backtick allows you to use column names that contain unusual characters. – LarsH Apr 17 '15 at 18:03
4

I achieved that in this way:

echo "select * from table"| mysql database -B -udbuser -puser_passwd | sed s/\\t/,/g > query_output.csv

The -B option of mysql separates the columns by tabs, which are converted into commas using sed. Note that the headers are generated too.

aizquier
  • 557
  • 5
  • 12
1

You can do this with the mysqldump command. Have a look at the --tab and --xml options.

Dana the Sane
  • 14,762
  • 8
  • 58
  • 80
  • I tested this and it did not give satisfactory results. Namely "sudo mysqldump --user=root --pass=mypass -T /home/mydir mydb mytable" yields "mysqldump: Got error: 1: Can't create/write to file '/home/mydir/mytable.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'" – Dan Goldstein Apr 21 '09 at 17:24
  • You should not need to use sudo for this. Just make sure you're running mysqldump from an account that has permission to write to the output folder, that seems to be what the error is. – Dana the Sane Apr 21 '09 at 17:46
  • 1
    Ok, got this to work by creating a directory with 777 permissions. However, it did not output the column names, just the data. – Dan Goldstein Apr 22 '09 at 06:37
  • Make sure you check the top of the file, generally these tools dump the schema first then the data. If you want a tab delimited file with the columns in the header, you may have to use --xml and perform a 2nd conversion step. – Dana the Sane Apr 22 '09 at 08:15
0

I have created a procedure to automate the exporting of the contents of a larger number of tables to .csv file by using SELECT ... INTO OUTFILE. Please refer to the following if you have need for something like this

http://lifeboysays.wordpress.com/2012/06/23/mysql-how-to-export-data-to-csv-with-column-headers/.

It uses the method described by cafe876, but will work for one or a whole series of tables, plus you can set the delimiter and quote character to be used.

Ollie
  • 544
  • 4
  • 22
Lifeboy
  • 177
  • 8
  • Wasn't able to find the file afterward... haha. – thekingoftruth May 01 '13 at 08:11
  • [thekingoftruth](https://stackoverflow.com/users/241367/thekingoftruth): If you look at the script, you'll find that output defaults to "/tmp". The file will be there, but you can specify any location for example you want like this: CALL export_important_tables('"',',','/home/steviewonder/'); And if you downvoted because of this, please remove the downvote, will you? – Lifeboy Nov 26 '18 at 09:05
0

I used the above command and modified according to my requirement.
I needed to get passwords column from the wordpress mysql database in a text file , to do that i used the following below command

echo "select user_pass from wp_users"| mysql -uroot -proot wp_database > passwordList.txt
aiffin
  • 509
  • 4
  • 11