Import CSV to MySQL with long text fields containing line breaks

Question

I'm trying to import a CSV file (';' separated) to a MySQL table. Normaly, I use a SQL like:

LOAD DATA LOCAL INFILE 'local-path/file.csv' 
INTO TABLE tmp_table
CHARACTER SET latin1
FIELDS TERMINATED BY ';'

It always works fine to me, but now I have a CSV with a text field including many '\n','\r', and stuff like that. When I make the import, apparently, MySQL interprets the line breaks as a new field, spoiling all import.

I'd like to know if exists some method to indicate to MySQL to ignore any other symbol except the ';'.

I'm using MySQL workbench to make the import. My CSV have 3 fields (locale,id,terms) and my table have columns locale - VARCHAR(5), id- VARCHAR(50) and terms - TEXT (here is the problem).

Here I found some similar problems, but apparently is not the same thing, I'm not looking for multiple delimiters, I just want for a way to ignore \n and \r from text fields when I'm importing the CSV.

How is `LOAD` supposed to know when each "line" is finished? — Rick James, Aug 09 '15 at 22:00
@RickJames. Please, correct me if I wrong, but I think that with a three-column table and a CSV separated by ';', the `LOAD` must take a new line every three fields, right? Actually, I don't know how `LOAD` works, but I imagine it to be so. — James, Aug 10 '15 at 11:58
Try `LINES TERMINATED BY ''` or `LINES TERMINATED BY ';'`; I suspect that leaving out the clause defaults it to `LINES TERMINATED BY '\n'`. Suggest you experiment. — Rick James, Aug 10 '15 at 12:13

score 2 · Accepted Answer · edited Dec 17 '18 at 01:33

2

According to the MySQL docs, you can set both the field and line delimiters in the LOAD DATA statement:

[{FIELDS | COLUMNS}
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
]
[LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]

That should enable you to import the TEXT data. Just be careful in choosing your delimiters!

edited Dec 17 '18 at 01:33

Paul White

67,511
25
368
572

answered Nov 25 '18 at 19:47

Rob Gravelle

113
6

I solved the problem some years ago, but your answer is right and it will help someone. – James Nov 26 '18 at 14:47
This answer would be so awesome if it included a working example because so far it's just a quote of the documentation and as far as I am concerned, the StackExchange community mostly saves hours of time of reading the docs – Ki Jéy Mar 13 '20 at 16:30

Import CSV to MySQL with long text fields containing line breaks

1 Answers1