homepage Welcome to WebmasterWorld Guest from 23.23.12.202
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
xls and csv import to mysql
importing xls and csv file
sham1321

5+ Year Member



 
Msg#: 3699670 posted 9:25 am on Jul 16, 2008 (gmt 0)

Hai everyone,

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

Thanks in Advance

 

janharders

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 3699670 posted 9:40 am on Jul 16, 2008 (gmt 0)

[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

5+ Year Member



 
Msg#: 3699670 posted 12:46 pm on Jul 16, 2008 (gmt 0)

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

5+ Year Member



 
Msg#: 3699670 posted 4:14 am on Jul 17, 2008 (gmt 0)

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

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



 
Msg#: 3699670 posted 4:19 am on Jul 17, 2008 (gmt 0)

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

5+ Year Member



 
Msg#: 3699670 posted 4:58 am on Jul 17, 2008 (gmt 0)

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

5+ Year Member



 
Msg#: 3699670 posted 9:38 am on Jul 17, 2008 (gmt 0)

hai eelixduppy!

ya exactly path to the file in the other directory

Steerpike

5+ Year Member



 
Msg#: 3699670 posted 9:42 am on Jul 17, 2008 (gmt 0)

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

What is that printing out?

sham1321

5+ Year Member



 
Msg#: 3699670 posted 9:45 am on Jul 17, 2008 (gmt 0)

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

eelixduppy

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



 
Msg#: 3699670 posted 12:03 pm on Jul 17, 2008 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved