loading csv file into mysql db via php

[webent]

Hi, I have several different category files that I load into a mysql db, the problem is that some of them have 40,000 and sometimes more lines with about 30 columns, it loads pretty fast at first, but then it slows to a crawl, like one line every couple or few seconds...

 

I realize there are a few options, such as use phpmyadmin, but that just wouldn't work consider how the script I have has all of the columns going to predefined fields... There is breaking the csv file up into several pieces, but wow what a lot of work...

 

Anybody have any solutions that may work that I'm just overlooking?

[unrelenting]

Here is a script I have used in the past on smaller csv files. Just setup the table in your database and upload the csv file to a folder. Then just edit the filename portion of this script as well as the tablename and the column names and put it in the same folder as the csv file then let it rip. Keep in mind that it has the truncate line in it that will empty the table if you have data in it that you don't want to lose. You will need to remove the truncate portion or back up your table.

 

<?php
$filename= "filename.csv";
$handle = fopen("$filename", "r");
$truncate="TRUNCATE TABLE tablename";
mysql_query($truncate) or die(mysql_error());
while (($data = fgetcsv($handle, 5000, ",")) !== FALSE)
{
$import="INSERT into tablename(column0,column1,column2,column3,column4,column5,column6,column7) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]')";
mysql_query($import) or die(mysql_error());
}
fclose($handle);
?>

[cooldude832]

mysql can do the work for you actually

 

http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

[webent]

@ unrelenting, Thank you, but that's currently what I'm already using... pretty much the cause of my problems...

 

@ cooldude832, Thank you, I'll look into that, see if it will work for me... Just curious though, does it do things like match certain columns to certain fields to populate? Then as far as using that function to update, can you tell it to update the columns to fields via a particular field, ie. the id or sku?

 

How do you feel about INSERT versus LOAD DATA INFILE that I found on a separate page from the one you sent me... ?