How to export data to csv in mysql without the header?

Question

MySQL has the functionality to export the data to a CSV file with following statement

SELECT 
    *
FROM
    person
INTO OUTFILE 'person.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ',' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

But by default it will has the column name as the CSV header, so how to remove the header in this statement?

I reviewed the mysql reference but seems these is no such info.

I don't think there's a way to do it automatically, you'll have to edit the file afterward, or write code in a client language to do it. — Barmar, Sep 22 '17 at 03:40
BTW, it's probably not a good idea to do this with `select *`, in case you change the order of columns in the table. Use explicit column names in the select list. — Barmar, Sep 22 '17 at 03:41
For a very large csv file, ~1GB, it is really hard to open and edit it. — Liping Huang, Sep 22 '17 at 03:41
Yep, `select *` is just a sample, in real statement, I added the concrete fields indeed, thanks for the reminder. — Liping Huang, Sep 22 '17 at 03:43

score 1 · Answer 1 · answered Sep 22 '17 at 03:47

1

2 options are provided in this post : How can I suppress column header output for a single SQL statement?)

1 : invoke mysql with -N flag will skip all column headers

2 : fake it

select column1 as '', column2 as '' from some_table;

answered Sep 22 '17 at 03:47

Lululicious

31
2

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

After complete the csv file, seems for the huge records, the big csv file has no header, so if you want to add the header, following:

Several users asked about including headers, i.e., column names or variable names, in the "INTO OUTFILE" syntax.

One approach is to use the "--column-names" option in the mysql invocation:

mysql --column-names -e 'SELECT * FROM mysql.user' > test.dat

(This creates a tab-delimited file test.dat with column names in the first row followed by the query results.)

score -1 · Answer 3 · edited Sep 26 '19 at 05:03

-1

SELECT 
    *
FROM
    person
INTO OUTFILE 'person.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ',' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

change FIELDS ENCLOSED BY '"' line
with OPTIONALLY ENCLOSED BY '"'

edited Sep 26 '19 at 05:03

Lord Elrond

13,430
7
40
80

answered Sep 26 '19 at 02:30

Bayyou Kelana

1

How to export data to csv in mysql without the header?

3 Answers3

1 : invoke mysql with -N flag will skip all column headers

2 : fake it