5

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??

  • Could you provide (say) three lines of the file? – Vérace Feb 10 '16 at 18:13
  • Thanks. I'm not allowed to post the data, but I can say the data rows import without issue until the file size exceeds 100MB and more than 100,000 records. – STWilson Feb 10 '16 at 18:19
  • Yeah - always a bummer about real data :-). Have you tried on different versions of MySQL and/or with Linux? – Vérace Feb 10 '16 at 18:33
  • Thank you again. Good idea. I tried on Linux MySQL version 5.1.73, same result: 100000 rows inserted. Inserted row id: 200028 (Query took 11.2536 sec) No rows inserted and no errors on large file of 300,000+ records: 0 rows inserted. (Query took 1.6017 sec) – STWilson Feb 10 '16 at 19:01
  • Have you tried 5.6 or 5.7 with Linux? 5.1 is almost as old as I am! – Vérace Feb 10 '16 at 19:05
2

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.

  • So, is your problem solved? What do you do with lines that get "the silent treatment"? – Vérace Feb 12 '16 at 13:38
  • Yes, I need to first check the CSV format and make the correct TERMINATED BY parameter in the query. Thanks for your ideas. – STWilson Feb 12 '16 at 14:00
  • 1
    "sed" could be very useful. – Vérace Feb 12 '16 at 17:48
  • 2
    @Vérace Yes, problem solved in Windows env with Powershell using this test: if (([System.IO.File]::ReadAllBytes("C:\temp\reallyBigFile.csv") | Select-Object -First 1000) -Contains 10) – STWilson Feb 12 '16 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.