MySQL export to CSV - fields contain " but are not escaped by "

Question

I have some tables that contain fields with double quotes in them, but they are part of a name or abbreviation for "inches", the " do NOT escape the field.

I need to export this to a CSV file but MySQL keeps breaking the fields at the " and creating extra columns in the CSV. How can I handle " within a field value?

Code:

select
   makers.maker_name,
   baits.bait,
   depth_line.depth,
   depth_line.lineout a
from makers
   inner join baits on makers.maker_id = baits.maker_id
   inner join depth_line on depth_line.bait_id = baits.bait_id
order by
   makers.maker_name asc,
   baits.bait,
   depth_line.depth asc
into outfile '/tmp/muskie.csv'
fields terminated by ',';

Sample data from the baits table:

wchiquito · Answer 1 · 2016-04-29T19:15:01.863

I can't reproduce the problem.

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.12    |
+-----------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `table0`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `table0` (
    ->   `column0` VARCHAR(255) DEFAULT NULL,
    ->   `column1` VARCHAR(255) DEFAULT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `table0`
    ->   (`column0`, `column1`)
    -> VALUES
    ->   ('"inches"', 'inches'),
    ->   ('4"', 'Lure name, 8", char, char'),
    ->   ('"VALUE 1"', 'NEW VALUE 1');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->   `column0`,
    ->   `column1`
    -> FROM
    ->   `table0`
    -> INTO OUTFILE '/path/to/file/muskie.csv'
    -> FIELDS TERMINATED BY ',';
Query OK, 3 rows affected (0.00 sec)

File: /path/to/file/muskie.csv

"inches",inches
4",Lure name\, 8"\, char\, char
"VALUE 1",NEW VALUE 1

UPDATE

Perhaps it can be useful to add the parameter OPTIONALLY ENCLOSED BY '"':

SELECT
  `column0`,
  `column1`
FROM
  `table0`
INTO OUTFILE '/path/to/file/muskie.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

File: /path/to/file/muskie.csv

"\"inches\"","inches"
"4\"","Lure name, 8\", char, char"
"\"VALUE 1\"","NEW VALUE 1"

My data in the field in question looks more like: '4"' or 'Lure name, 8"'...so there is a single " within the field somewhere. — C. Gombola, Apr 29 '16 at 17:40
I'm sorry, the double quote is not my problem, it is the comma within the field that is causing me pain. So a single field may contain 'char,char'. The query is splitting the field on the comma. — C. Gombola, Apr 29 '16 at 17:45

score 0 · Answer 2 · answered May 24 '23 at 05:35

Assuming you need a CSV that can be read properly by Excel, the best solution I found is to escape the double quotes as you're exporting the CSV.

DELIMITER $$
CREATE FUNCTION `csv_escape`(`text` TEXT)
RETURNS TEXT
READS SQL DATA
DETERMINISTIC
BEGIN
    RETURN REPLACE(`text`, '"', '""');
END $$
DELIMITER ;

SELECT id, csv_escape(your_text_column)
INTO
    OUTFILE '/foo/bar/baz.csv'
    CHARACTER SET utf8mb4
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    ESCAPED BY ''
    LINES TERMINATED BY '\n'
FROM your_table;

To build the OUTFILE path, see my answer here.

For a comparison between MySQL and Excel CSV formats, see the table in this answer.

MySQL export to CSV - fields contain " but are not escaped by "

2 Answers2

Linked