How to SELECT INTO OUTFILE with header labels enclosed by quotes but not body fields?

Question

I do not believe this question is a duplicate.

I want rows in the body to be "OPTIONALLY ENCLOSED BY" double quotes. Numerical values should not be enclosed. That's easy to do without a header. But when you include a header using UNION, MySQL now treats every column as a string type and encloses all the values in quotes.

You can add a header to SELECT INTO OUTFILE like this:

SELECT "id", "numerical_values", "string_values" #header section of csv
UNION ALL
SELECT `id`, `numerical_values`, `string_values` #body section of csv
INTO OUTFILE "/tmp/values.csv"
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM `values_table`

Again, if I omit the header, only string_values will be enclosed by quotes. With the header, all columns are seen as string columns and will be enclosed.

I tried this:

SELECT '"', "id", ',"', "numerical_values", ',"', "string_values", '"' 
#added quotes/commas to header
UNION ALL
SELECT `id`, ',', `numerical_values`, ',"', `string_values`, '"'
#added commas, and add quotes around string_values
INTO OUTFILE "/tmp/values.csv"
FIELDS TERMINATED BY "" ENCLOSED BY '' #empty values for fields terminated and enclosed
# empty because manually selected in query
LINES TERMINATED BY "\n" 
FROM `values_table`

I thought that would work, but I ended up with what looks like a bunch of extra whitespace. This is from an file I just created with the 2nd method. The header row ends at "string7". The 1st line of data ends after "207". I just put part of the 3rd line.

"         num1","                                          string1","       num2","                                          string2","       num3","                                                                        string3","       num4","                                                                        string4","       num5","                                                                        string5","       num6","                                                                        string6","      string7""
33.95     ," 1023                                         ",  7.50      ,"  207-1023                                     ",  26.95     ,"2 1023                                                                     ",23.00     ,"3Wx4Hx4D                                                                   ",19.00     ,"1023                                                                       ",  0.00      ,"UPC: 123456789012                                                          "," 207      "       
40.95     ," 1058                                         ",  9.00      ,

possible duplicate of [Include headers when using SELECT INTO OUTFILE?](http://stackoverflow.com/questions/5941809/include-headers-when-using-select-into-outfile) — Kenyakorn Ketsombut, Apr 14 '15 at 03:20
@buttle butkus, thanks for asking, was good to find an answer to this. — AdrianBR, Sep 28 '15 at 07:42
@AdrianBR this question has been viewed 10,918 times, so I'm glad it finally helped someone! ;) — Buttle Butkus, Sep 28 '15 at 21:08

score 11 · Accepted Answer · answered Nov 04 '12 at 09:07

11

SELECT '"id"', '"numerical_values"', '"string_values"'
UNION ALL
SELECT id, numerical_values, CONCAT('"', REPLACE(string_values, '"', '""'), '"')
INTO OUTFILE '/tmp/values.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM values_table

answered Nov 04 '12 at 09:07

eggyal

122,705
18
212
237

Works perfectly. I wish there was an easier way, but this does the job. Thanks! – Buttle Butkus Nov 04 '12 at 09:55
@eggyal, thx, saved me from having to write a separate script. – AdrianBR Sep 28 '15 at 07:42

How to SELECT INTO OUTFILE with header labels enclosed by quotes but not body fields?

1 Answers1