Importing CSV into a mysql DB – PHP

  php

Q(Question):

^— i guess the title says it all.. ^_^

so, how do you do this?

A(Answer):

^— i guess the title says it all.. ^_^

Not really.
Via which medium?
A one-off or regular task?
I could go on

A(Answer):

^— i guess the title says it all.. ^_^

so, how do you do this?

You’ll probably end up choosing one from these 3 approaches
1.) Using the LOAD DATA INFILE statement.
2.) By importing the file as a mysqldump file (If the csv file was created using mysqldump)
3.) By using an import script written in a programming language that can access MyQSL and can read csv files

A(Answer):

oh, yeah, i forgot… I’m using PHP for this job. so how do you do this?

although i have started, I have this form:


<INPUT type="file" name="filename" enctype="multipart/form-data" style="FONT-SIZE: 11px; FLOAT: right; FONT-FAMILY: Tahoma">

and here’s the PHP code


if (isset($_POST['file_submit']))
{
if (!empty($_POST['filename']))
{
$filename = addslashes($_POST['filename']);
echo $filename;
$handle = fopen($filename, "r");
while (($data = fgetcsv($filename, 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);
}
else echo "<B><center><FONT face=Tahoma color=#ff0000 size=3>Filename field empty. Please fill up all the fields.</b></FONT></center><br>";
}

here’s what the echo $filename outputs when i select a file

a.csv

and after that, some error messages.

Warning: fopen(a.csv) [function.fopen]: failed to open stream: No such file or directory in C:\Documents and Settings\jcrequiroso\Desktop\xampplite\htdocs\inve ntory\add.php on line 50

Warning: fgetcsv() expects parameter 1 to be resource, boolean given in C:\Documents and Settings\jcrequiroso\Desktop\xampplite\htdocs\inve ntory\add.php on line 51

Warning: fclose(): supplied argument is not a valid stream resource in C:\Documents and Settings\jcrequiroso\Desktop\xampplite\htdocs\inve ntory\add.php on line 52

i think i have to copy the file to the root folder of the localhost, and then access it, is that right? if so, how can i do this?

A(Answer):

It’s a PHP problem then so I’ll move it to that forum.
P.S The error messages seem to be giving you all the hints.

A(Answer):

Now isn’t your second post much more informative than your first?
As r035198x said the clues are all there.[PHP]$handle = fopen($filename, "r");
while (($data = fgetcsv($filename, 1000, ",")) !== FALSE)[/PHP] You are trying to open a non-existent file.
Then you are trying to read a non-existent file with the file name instead of the handle [PHP]if($handle = fopen($filename, "r"))
while (($data = fgetcsv($handle , 1000, ",")) !== FALSE)
else
echo ‘Failed opening file’.$filename;[/PHP]

A(Answer):

or you could use

$sql = "LOAD DATA LOCAL INFILE 'xfile.txt' INTO TABLE `lettings` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n' IGNORE 1 LINES;";
mysql_query($sql) or die('Error loading data file.<br>' . mysql_error());

LEAVE A COMMENT