javamysqlload-data-infile

LOAD DATA INFILE LINES TERMINATD doesn't work with \n


I have a csv file, that I want to import to MySQL, but LINES TERMINATED doesn't work with '\n'. I tried to replace \n with '\r' or '\r\n', but it still doesn't work.

If I open my file in HEX editor it is obvious, that my Java App (that writes this file) works fine ('\n' terminators are highlighted). enter image description here

But when I run

LOAD DATA LOCAL INFILE '/home/vsafonov/testDir/test.csv' 
INTO TABLE express.objs 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES;

I get

enter image description here

If I replace '\n' for new lines with some symbol (for example ';'), LOAD DATA INFILE works fine. But I have no ideas, why it is impossible to load file with '\n' line terminator. Some thoughts?

Table's CREATE TABLE

CREATE TABLE IF NOT EXISTS `test_objs` (
                                      `id` bigint(20) NOT NULL,
                                      `object_id` bigint(20) DEFAULT NULL,
                                      `next_id` bigint(20) DEFAULT NULL,
                                      PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;

My file example:

id,object_id,next_id
"1866227","98363301","156715750"
"293","171","454"
"1890275","171","177646470"

Solution

  • For me with query worked

    LOAD DATA LOCAL INFILE '/home/vsafonov/testDir/test.csv' INTO TABLE express.objs FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES;

    Default terminator worked fine with my '/n'.

    Terminator in my query was accepted literally (didn't understand why).