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.

Answer 1

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!