MySQL LOAD DATA INFILE – Handling Large CSV Records

csvimportMySQL

The following query imports 100,000 perfectly, but larger files nothing happens. No errors, no rows loaded.

Any clues you can offer are appreciated. I spent hours looking into max file and memory limits and need help. Thanks!

LOAD DATA LOCAL INFILE 'C:\\temp\\masterplay\\bigone.csv'
INTO TABLE mytable
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(`MARK`,`SERIAL NUMBER`, ...thirty some columns...)

0 rows inserted. (Query took 2.2385 sec)

The above performs perfectly and FAST on 100,000 records, does nothing on 350,000 records. When the CSV is broken down to 100,000+ records, the import works.

Example CSV file size is 314,946 records. Average 551 characters per row. Max row length is 575. Versions: MYSQL 5.6.17, PHP 5.5.12 WAMPSERVER 2.5, Windows 10 Pro 16 GB RAM. Unable to determine failure mode, so no reference to cutoff on file.

Per suggestions below, tried same SQL on other versions and Linux, same problem.

UPDATE:

Ideas below focused me on the source CSV files. Thus far, I discovered resaving the CSV (even though it checks good using CSVed application and appears identical in name and content) somehow fixes the file so that it will import. I have hundreds of large files to process and yet do not understand what the difference is.

The files are comma separated with no text delimiters. After I re-save the file, it still appears to be identical in content but roughly 300K larger file size. Does a CSV file type have a header or meta data that makes a difference??

Best Answer

Files checked with hex editor and answer revealed.

My CSVs differed in that rows either terminated with:

Hex 0D CARRIAGE RETURN (\r), or

Hex 0D CARRIAGE RETURN (\r) AND 0A LINE FEED (\n)

This is the line of code:

LINES TERMINATED BY '\r\n'

or

LINES TERMINATED BY '\r'

In my case, taking out the \n took care of it - either CSV imports perfectly.

If this line does not match the CSV, you get the silent treatment. The SQL import will run without error yet no rows will import.

Related Question