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 '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
  • 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 '15 at 12:13

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 '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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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