Load infile data for a huge file (without warning, without errors and without lines)

I tried the following script:

LOAD DATA LOCAL INFILE 'myfile.csv'
    REPLACE INTO TABLE `mydb`.`mytable`
    CHARACTER SET latin1 FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES (`field1`, `field1`, `field1`, `field1`, `field1`, `field1`);

when I use a file of 500K records, it works, but when I try to write a csv file of 4 million records, it returns:

Query OK, 0 rows affected (2.79 sec) 
Records: 0  Deleted: 0  Skipped: 0  Warnings: 0

And, of course, nothing will be added in 2.70 seconds!

My RAM is 4 GB and my input file (large) is 370 MB.

Can anyone suggest a solution?

+3
source share
1 answer

It is possible that the line ending in a large file is not "\ r \ n".

Change the format LINES TERMINATED BY '\r\n'to '\n'.

+5
source

All Articles