CODEWITHSUNDEEP
mysqlsqlcsvsql-insertload-data-infile
Alvaro
Alvaro

Reputation: 41595

LOAD DATA INFILE - Problem with optional quotes and commas

I was using:

LOAD DATA LOCAL INFILE '$filePath'
     INTO TABLE demo
     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
     LINES TERMINATED BY '\r\n'

And I found out I had troubles when importing rows with cells containing JSON strings:

0, demo,"{""Name""=>""demo""}", 29, ...

Notice the "{""Name""=>""demo""}" part.

So I removed OPTIONALLY ENCLOSED BY '"' and it fixed the problem. However now I'm having troubles with a field with the value "Korea, Republic of"

13,"Korea, Republic of",demo,...

Is there any way I can manage these two cases at the same time?

Note: the input file is external to me, so I can't "re-structure" it.

Upvotes: 1

Views: 377

Answers (1)

GMB
GMB

Reputation: 222462

Does it work if you specify " as the escape character?

LOAD DATA LOCAL INFILE '$filePath'
     INTO TABLE demo
     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
     LINES TERMINATED BY '\r\n'

Actually this seems to match one specific documentation note:

To avoid ambiguity, occurrences of the ENCLOSED BY character within a field value can be doubled and are interpreted as a single instance of the character. For example, if ENCLOSED BY '"' is specified, quotation marks are handled as shown here:

"The ""BIG"" boss"  -> The "BIG" boss`
The "BIG" boss      -> The "BIG" boss
The ""BIG"" boss    -> The ""BIG"" boss

Upvotes: 1

Related Questions