Welcome to WebmasterWorld Guest from 54.226.60.235

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

xls and csv import to mysql

importing xls and csv file

     

sham1321

9:25 am on Jul 16, 2008 (gmt 0)

5+ Year Member



Hai everyone,

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

Thanks in Advance

janharders

9:40 am on Jul 16, 2008 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



[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

12:46 pm on Jul 16, 2008 (gmt 0)

5+ Year Member



<?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

4:14 am on Jul 17, 2008 (gmt 0)

5+ Year Member



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

4:19 am on Jul 17, 2008 (gmt 0)

WebmasterWorld Senior Member eelixduppy is a WebmasterWorld Top Contributor of All Time 5+ Year Member



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

4:58 am on Jul 17, 2008 (gmt 0)

5+ Year Member



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

9:38 am on Jul 17, 2008 (gmt 0)

5+ Year Member



hai eelixduppy!

ya exactly path to the file in the other directory

Steerpike

9:42 am on Jul 17, 2008 (gmt 0)

5+ Year Member



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

What is that printing out?

sham1321

9:45 am on Jul 17, 2008 (gmt 0)

5+ Year Member



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

eelixduppy

12:03 pm on Jul 17, 2008 (gmt 0)

WebmasterWorld Senior Member eelixduppy is a WebmasterWorld Top Contributor of All Time 5+ Year Member



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';
 

Featured Threads

Hot Threads This Week

Hot Threads This Month