5
Contributors
8
Replies
13
Views
7 Years
Discussion Span
Last Post by crohole
0

how can we parse excel sheet and insert its values into mysql database table using php?

$filename=$_FILES['imp']['tmp_name'];
	 $handle = fopen("$filename", "r");
	 $i=0;
   while (($data = fgetcsv($handle, 1000, "\t")) !== FALSE)
	{
      
		$proid = $data[0];
		$proname = addslashes($data[1]);
		
		$dblquotpara    = '"'."\r\n"; // double quote+CR+LF
	$sglquotpara    = "'"."\r\n"; // single quote+CR+LF
	$flspara        = "\r\n"; //CR+LF
		
	if($data[0]=='')
	{
	
	}
	else
	{	
	
	$auto_no = $data[0];
	$productname = $data[1];
	 $productid= $data[2];
	  $productprice= $data[3];
	   $productquantity= $data[4];
	    $status= $data[5];
		 $productimage= $data[6];
		  $departname= $data[7];
		
	if($productimage !='')
	{
	 $ext=strpos($productimage,'.');
		if($ext !='')
		{
		 $productimage=$productimage;
		}
		else
		{
		 $productimage=$productimage.".jpg";
		}
	}
			if($i>0)
			{
			$sqlpro=mysql_query("select * from product where ((auto_no='$auto_no')||(  	 productname='$productname')||((auto_no='$proid')&&(productname='$productname')))");
		$countpro=mysql_num_rows($sqlpro);
			  if($countpro > '0')
			  {	
			  
			$import = "UPDATE `product` SET  `productname` = '$productname',productid='$productid', `productprice` = '$productprice', `productquantity` = '$productquantity', `status` = '$status', `productimage` = '$productimage', `departname` = '$departname' where `auto_no` = '$auto_no'";
	 $res=mysql_query($import) or die(mysql_error());
	           }
			   else
			   {	
			  	 
			 $import="insert into `product` SET `auto_no` = '$auto_no',`productname` = '$productname',productid='$productid', `productprice` = '$productprice', `productquantity` = '$productquantity', `status` = '$status', `productimage` = '$productimage', `departname` = '$departname' ";		 
			 
			 $res=mysql_query($import) or die(mysql_error());
			 }			 
		 }  
	
	 }
	  $i++;
 }//end of while
     fclose($handle);
	 if($res)
	 {
     $msg=1;
	 }else
	 {
	 $msg=3;
	 }

i am using the code import excel file to mysql. you can change it as your requirements.

0

i am using the code import excel file to mysql. you can change it as your requirements.

can you explian how the functionality works. i mean is it useful for csv files also?

0

can you explian how the functionality works. i mean is it useful for csv files also?

yes . it works fine for csv file also . check it it once. where it work or not . i am uploading .xls files. try for csv.

-1

yes . it works fine for csv file also . check it it once. where it work or not . i am uploading .xls files. try for csv.

ya. i tried as .csv. but i put echo for data[0] i am getting total row once. but .xls files getting only one value.

0
$filename=$_FILES['imagefile']['tmp_name'];
     echo "\nfilename:".$filename;

      $handle = fopen("$filename", "r");

      $i=0;
     echo "\nhandle:".$handle;
      while (($data = fgetcsv($handle, 1000, "\t")) !== FALSE)

      {

       

      $prod1 = $data[0];

      $prod2 = $data[1];

       echo "\nprod1:".$prod1;
	   echo "\nprod2:".$prod2;

      $dblquotpara = '"'."\r\n"; // double quote+CR+LF

      $sglquotpara = "'"."\r\n"; // single quote+CR+LF

      $flspara = "\r\n"; //CR+LF

       

      if($data[0]=='')

      {

       

      }

      else

      {

       

      $col1 = $data[0];

      $col2 = $data[1];

echo "\ncol1:".$col1;

echo "\ncol2:".$col2;

     
      if($i>0)

      {

      

       

      $import = "insert into testing values('$prod1','$prod2')";

      $res=mysql_query($import) or die(mysql_error());

      
      }

       

      }

      $i++;

      }//end of while

      fclose($handle);

      if($res)

      {

      $msg=1;

      }else

      {

      $msg=3;
      }

I am trying this code but getting junk vales in the database , can you please tell why?

0
$filename=$_FILES['imagefile']['tmp_name'];
     echo "\nfilename:".$filename;

      $handle = fopen("$filename", "r");

      $i=0;
     echo "\nhandle:".$handle;
      while (($data = fgetcsv($handle, 1000, "\t")) !== FALSE)

      {

       

      $prod1 = $data[0];

      $prod2 = $data[1];

       echo "\nprod1:".$prod1;
	   echo "\nprod2:".$prod2;

      $dblquotpara = '"'."\r\n"; // double quote+CR+LF

      $sglquotpara = "'"."\r\n"; // single quote+CR+LF

      $flspara = "\r\n"; //CR+LF

       

      if($data[0]=='')

      {

       

      }

      else

      {

       

      $col1 = $data[0];

      $col2 = $data[1];

echo "\ncol1:".$col1;

echo "\ncol2:".$col2;

     
      if($i>0)

      {

      

       

      $import = "insert into testing values('$prod1','$prod2')";

      $res=mysql_query($import) or die(mysql_error());

      
      }

       

      }

      $i++;

      }//end of while

      fclose($handle);

      if($res)

      {

      $msg=1;

      }else

      {

      $msg=3;
      }

I am trying this code but getting junk vales in the database , can you please tell why?

$import = "insert into testing values(' $col1',' $col2')";

and

$prod1 = $data[0];

      $prod2 = $data[1];

       echo "\nprod1:".$prod1;
	   echo "\nprod2:".$prod2;

comment this one.
try it once.
one thing your file must be .xls file, others wise it will take nulls.

Edited by rajabhaskar525: n/a

0

$filename=$_FILES;

So if I put the exel file in C://xampp/htdocs/try

Should it be any chance on your codde?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.