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

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.

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?

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.

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.

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

$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.

$filename=$_FILES;

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

Should it be any chance on your codde?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.