How to handle large CSV files to insert into mysql

问题:

I have a file a csv file (made as .txt) that I am currently parsing right now, but the file is about 350mb uncompressed. When it's zipped, it shows in the zip file as 23mb. My system completely freezes when I try to parse the 350mb file. I store the lines in an array like this. The first row are the headings.

$fh = fopen($inputFile, 'r');
    $contents = fread($fh, filesize($inputFile)); // 5KB
fclose($fh);
//$contents = str_replace('"','',$contents);

$fileLines = explode("\n", $contents); // explode to make sure we are only using the first line.

Then I go through each line to insert it in a loop into mySQL. Since the file is about 350mb, would there be a way to parse it from the .zip file like .zip_filename.txt or would that even make a difference at all?

The file is too large to insert directly into mysql through the import method.

回答1:

Use the built in function fgetcsv:

<?php
$row = 1;
if (($handle = fopen($inputFile, "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        echo "<p> $num fields in line $row: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />\n";
        }
    }
    fclose($handle);
}
?>

Also use multi insert if possible. Instead of running multiple queries:

insert into table (col1, col2) values("row1-col1", "row1-col2");
insert into table (col1, col2) values("row2-col1", "row2-col2");

Building one query like this is much quicker:

insert into table (col1, col2) 
values ("row1-col1", "row1-col2"),
       ("row2-col1", "row2-col2");

By the way, you can also load a file directly into mysql:

load data local infile 'file.csv' into table table_name fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(col1, col2)

回答2:

Consider using LOAD DATA INFILE, that'll allow you to insert the contents of a CSV file directly.