Import CSV to MySQL – Handling Long Text Fields with Line Breaks

csvimportMySQL

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.

Best Answer

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!

Related Solutions

Mysql – Import CSV file into Mysql with Multiple Delimiters/Field Separators

If you're on *nix - check out the tools sed, awk, grep and split and related (or even vi). As mentioned perl could do it, so could python or PHP (or C or Java or ...) This looks more like a task for programming tools rather than a database. That's not to say you couldn't do this using PL/SQL or T-SQL or , but sometimes the most suitable tool is not within the database server (and in this case, certainly not within MySQL).

MySQL Import – How to Import Multiple CSV Fields into One MySQL Field

The following LOAD DATA sentence loads the CSV file, ignoring the first line and inserting into the desired fields, but changing the format of the Date and Time columns into a proper datetime type:

mysql> LOAD DATA INFILE '/tmp/alarms.csv' 
       INTO TABLE alarms 
       COLUMNS TERMINATED BY ',' 
       IGNORE 1 LINES 
       (@Date, @Time, ALARM_01, PT001, TEMP_AMB) 
       SET DateTime = STR_TO_DATE(CONCAT(@Date, ' ', @Time), '%d/%m/%Y %H:%i:%s');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM alarms;
+----+---------------------+----------+--------+----------+
| id | DateTime            | ALARM_01 | PT001  | TEMP_AMB |
+----+---------------------+----------+--------+----------+
|  1 | 2014-08-14 14:23:21 |        0 | 130.00 |    20.00 |
|  2 | 2014-08-14 14:23:25 |      101 | 128.09 |    20.10 |
+----+---------------------+----------+--------+----------+
2 rows in set (0.00 sec)

Your fields and datatypes may vary slightly, with no effect on the import query.

Best Answer

Related Solutions

Mysql – Import CSV file into Mysql with Multiple Delimiters/Field Separators

MySQL Import – How to Import Multiple CSV Fields into One MySQL Field

Related Question