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).
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
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"
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).