CODEWITHSUNDEEP
mysqlcsv
Abderrahmane TAHRI JOUTI
Abderrahmane TAHRI JOUTI

Reputation: 4273

MySQL LOAD DATA LOCAL INFILE does nothing when first line is enclosed by "

The following MySql Query does absolutely nothing

LOAD DATA
LOCAL INFILE 'path/to/file.csv'
REPLACE INTO
TABLE `db`.`table`
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
    `id`,
    `name`
);

The CSV file is as follow :

"id","name"
"1","Lorem"
"2","Ipsum"
"3","Dolor"
"4","Sit"
"5","Amet"
"6","consectetur "
"7","adipisci"
"8","velit"
"9","sed"
"10","quia"

I have checked the line separator which is LF, and the file encoding is UTF-8.

I have noticed that if I empty the first line of the csv, or remove the double quotes on the first line, the query does actually affect 10 rows.

Do you have any idea why this is happening ? Because I would rather I did not have to edit the first line.

Upvotes: 1

Views: 1652

Answers (1)

Red Boy
Red Boy

Reputation: 5739

Problem is with your Escaped By '"', as first quote(") encounters, its considered as Escape. Just get rid of it, it will work.

LOAD DATA
LOCAL INFILE 'path\\to\\file.csv'
REPLACE INTO
TABLE `db`.`table`
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
    `id`,
    `name`
);

Upvotes: 2

Related Questions