Hi Everyone,

I am going crazy trying to search for solutions to this problem so I thought I'd ask here for help!

I am trying to upload a csv file through a web form and enter that info into a database. Seems like it should be pretty simple and it should be available out there but I've been losing my mind searching for this.

Please let me know if you have any help or if you can point me in the right direction!

Thanks,
Stephen

Try with file process function to read line by line and insert to your database with SQL "INSERT"

That's the way you can do, it's so easy!

Here's a nifty way to iterate a very large CSV file and import into your DB without loading the whole thing into memory:

PHP Code:

<?php

$len = 4096;
$delimiter = ",";
$enclosure = "\"";

$conn = mysql_connect(/* connection info */);
mysql_query("BEGIN");
try {
    $lines = new SplFileObject("/home/my/file/path.csv");
    foreach ($lines as $line) {
        $fh = fopen('php://memory', 'rw');
        fwrite($fh, $line);
        rewind($fh);
        $result = fgetcsv( $fh, $len, $delimiter, $enclosure );
        fclose($fh);
        foreach ($result as &$value) {
            $value = "'".mysql_real_escape_string($value)."'";
        }
        $values = implode(", ", $result);
        mysql_query("INSERT INTO my_table(col1, col2, col3, ...) VALUES ({$values})");
    }
    mysql_query("COMMIT");
} catch (Exception $e) {
    mysql_query("ROLLBACK");
    print($e->getMessage());
}
?>

This way, you get each line one at a time as an array, rather than one huge array with the CSV data.

It allows you to parse and handle very large CSV files without running out of memory - I've imported 1.6 GB CSVs (with millions of records) this way!!!

Also has good error handling to either succeed or fail the entire transaction... so you won't get duplicate records if it fails and you have to restart.
Good luck!

another possibility, once the file is copied to the server, is to use the
LOAD DATA INFILE command:
http://dev.mysql.com/doc/refman/5.5/en/load-data.html

LOAD DATA INFILE can be used to read files obtained from external sources. For example, many programs can export data in comma-separated values (CSV) format, such that lines have fields separated by commas and enclosed within double quotation marks, with an initial line of column names. If the lines in such a file are terminated by carriage return/newline pairs, the statement shown here illustrates the field- and line-handling options you would use to load the file:

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name   
FIELDS TERMINATED BY ',' ENCLOSED BY '"'   
LINES TERMINATED BY '\r\n'   IGNORE 1 LINES;

Hi Smoseley,
I've did exactly what you said here, but it still memory limit exceeded error. Here is my code -

PHP Code:

$this->query("BEGIN");
try{
    
    $lines = new SplFileObject($file_path);
    foreach($lines as $key=>$line){
        
        if($key==0)
            continue;
        
        $fh = fopen("php://memory", "rw");
        fwrite($fh, $line);
        rewind($fh);
        $row = fgetcsv($fh, $len, $delimiter, $enclosure);
        fclose($fh);
        
        $row = array_combine($fields, $data);
        $sql =     $this->prepare_sql($table, $row);
        $this->query($sql);
    }

    $this->query("COMMIT");
    
} catch(Exception $e){
    $this->query("ROLLBACK");
    
} 


Any idea???

You're running out of memory because of the MySQL Transaction.

Get rid of the lines with BEGIN, COMMIT, and ROLLBACK and see how it works for ya.

Cheers