xls and csv import to mysql

importing xls and csv file

[sham1321]

Hai everyone,

Can anyone give me a code for importing xls and csv file to mysql in php,

Thanks in Advance

[janharders]

[dev.mysql.com...] contains all the information you need to import csv into mysql. I don't think you can import xls directly, though you could of course save it as a csv first.

[shruti]

<?php
$host = "localhost";
$user = "#*$!";
$pass = "yyy";
$dbname = "zzz";

$connection = mysql_connect("localhost", "root", "") or die("Cannot connect to MySQL server: " . mysql_error());
$db_selected = mysql_select_db('zzz', $connection);

$data = mysql_query("LOAD DATA LOCAL INFILE 'C:\\\Program Files\\\write whole path to your cvs file(dont remove the three slashes)\\\your_cvs_filename.CSV'
INTO TABLE zzz.tablename
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(a,b,c,d,e)") These are your actual fields name in the table
or die(mysql_error());

?>

[sham1321]

thanks guys,

Am gonna use this import module in an application, i used fopen, the problem is, i am able upload the csv file from same directory but am unable to upload from any other directory
here the code..

<?php
$con=mysql_connect("localhost","root","");
$db=mysql_select_db("test",$con);
//$result = mysql_query($query);
$csv=$_GET['file1'];
echo "the csv is".$csv;
$row = 1;
$handle = fopen($csv,"r");
$field='';
$rowcount=0;
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
$num = count($data);
echo "<p> $num fields in line $row: <br /></p>\n";
if($row==1)
{
for ($c=0; $c < $num; $c++)
{
$field = $field . $data[$c] ." "."VARCHAR(50)";
if($c==$num-1)
{
$field=$field;
}
else
{
$field=$field.",";
}
}
//echo "the val".$field;
$row++;
}
else
{
for ($c=0; $c < $num; $c++)
{
//echo "data".$data[$c] . "<br />\n";
$values=$values . "'".$data[$c]."'";
if($c==$num-1)
{
$values=$values;
}
else
{
$values=$values.",";
}
if($row == 14)
{
$totval = $totval . "(".$values.")";
}
else
{
$totval = $totval . "(".$values.")".",";
echo $totalval;
}
$values='';
$row++;
}
}
echo "The total value is".$totalval;
//echo $totval;
fclose($handle);
if(preg_match("/-/",$csv))
{
//echo "this name has hypon";
$tab1=explode("-",$csv);
$tablename=$tab1[0];
}
else
{
$tab1=explode(".",$csv);
$tablename=$tab1[0];
}

$cat="create table $tablename($field)";
mysql_query($cat);
if(mysql_error()){ echo mysql_error()."<br>"; }
$sql = "insert into $tablename values $totval";
mysql_query($sql);
echo $sql ."<br>\n";
if(mysql_error())
{
echo "this".mysql_error() ."<br>\n";
}
else
{
echo"<h3>You have successfuly updated the whatever ..?<br>
}
?>
please correct my code...

thanks in advance

[eelixduppy]

Is the path to the file in the other directory correct? Are you getting any errors from this PHP script when you try? Please elaborate on "not working"

[sham1321]

Warning: fopen(report.csv) [function.fopen]: failed to open stream: No such file or directory in C:\xampp\htdocs\CEOTOOLs\test\landing.php on line 18

Warning: fgets(): supplied argument is not a valid stream resource in C:\xampp\htdocs\CEOTOOLs\test\landing.php on line 21

Warning: fclose(): supplied argument is not a valid stream resource in C:\xampp\htdocs\CEOTOOLs\test\landing.php on line 25

Warning: fopen(report.csv) [function.fopen]: failed to open stream: No such file or directory in C:\xampp\htdocs\CEOTOOLs\test\landing.php on line 26

Warning: fgetcsv() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\CEOTOOLs\test\landing.php on line 27

Warning: fgetcsv() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\CEOTOOLs\test\landing.php on line 27

Warning: fgetcsv() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\CEOTOOLs\test\landing.php on line 27

Warning: fgetcsv() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\CEOTOOLs\test\landing.php on line 27

Warning: fgetcsv() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\CEOTOOLs\test\landing.php on line 27

i got this

[sham1321]

hai eelixduppy!

ya exactly path to the file in the other directory

[Steerpike]

$csv=$_GET['file1'];
echo "the csv is".$csv;

What is that printing out?

[sham1321]

i just read that data in that file1.. so i used that echo "the csv is" .$csv

[eelixduppy]

If you are absolutely sure that the path is correct, then there may be other problems, too. Do you have safe_mode enabled in your php.ini configuration file? Try setting the path as a static value right now until you get this worked out instead of defining it in the URI:

$csv = '/path/to/file.php';