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

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.

  • 1
    How is LOAD supposed to know when each "line" is finished?
    – Rick James
    Aug 9, 2015 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, 2015 at 11:58
  • 1
    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, 2015 at 12:13

1 Answer 1


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

    [TERMINATED BY 'string']
    [ESCAPED BY 'char']
    [STARTING BY 'string']
    [TERMINATED BY 'string']

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

  • I solved the problem some years ago, but your answer is right and it will help someone.
    – James
    Nov 26, 2018 at 14:47
  • 2
    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
    – Mouradif
    Mar 13, 2020 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.