importing a csv into the db

[eluser]ray73864[/eluser]
I suggest not using these php based libraries for importing a csv file into a db (especially a mysql db).

I had to do csv import for a client and the csv file could hold anywhere up to 5000 products, no matter how streamlined i made it, i still had to do a 'select' query to see if it existed first and if so just update otherwise insert.

if you are using mysql then might i suggest the native 'load data infile' that mysql has, it allows you to import a csv file directly into a database table and even with 5000 products (like i had) takes a couple of seconds compared to a couple of minutes.

Example code from what i was using:

Code:

if ( !copy("/home/http/capelite/test2.csv", "/tmp/capelite_item.csv") )
            $data['message'] = "Could not copy CSV file to temporary directory ready for importing.";
        
        $query = $this->db->query("LOAD DATA INFILE ? REPLACE INTO TABLE item FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\n' (id, product_id, @dummy, @dummy, item_code, @dummy, @retail_price, @dummy, item_status, @dummy) SET retail_price = substring(@retail_price, 2, 100)",array('/tmp/capelite_item.csv'));
        
        if ($query) {
            $data['message'] = "All items imported successfully.";
        } else {
            $data['message'] = "Import failed.";
        }
        
        unlink ("/tmp/capelite_item.csv");

basically the file may or may not have to be copied to /tmp first (i had to with mine because the mysql server couldn't read from the web directory where the csv file was being stored.

next i tell it that i want to 'replace' if the record already exists.
then, i tell it how the fields are terminated and that the fields could optionally contain double quotes (for strings), and i tell it what the rows are terminated by.

Then you name each DB column with the columns in the csv file using @dummy for any column in the csv file that won't go into your DB table, you can use 'SET' for doing fancy things with the column, such as stripping the $ sign from a currency field and then you have to tell it where the csv file is.

the mysql website has a lot more information but that is the basic gist of it all.

Oh, you also don't have to put every column from the CSV in, if you have a CSV file with 20 columns and a DB table with 5 columns and those 5 columns are the first 10 in your csv file, then ignore the other 10.

I should point out that when i tested it, that csv file had over 20 thousand rows in it, i was forever hitting the php memory limit at about 6000 rows and even using usleep() it was still going to take several hours to import them all, LOAD DATA INFILE did the whole thing in under 30secs.