how to parse excel sheet in php

Question

indoreankita 0 Newbie Poster

16 Years Ago

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

mysql php

5 Contributors
8 Replies
278 Views
3 Weeks Discussion Span
Latest Post 16 Years Ago Latest Post by crohole

Dani AI

Generated 9 Months Ago

Quick diagnosis and what caused the “junk” rows: fgetcsv reads plain-text CSV/TSV lines — it will produce garbage if you feed it a binary Excel file (old .xls is a BIFF binary format). That explains and seeing only one value or junk when they uploaded a real .xls while using fgetcsv. Use fgetcsv only for true text CSV/TSV files. (php.net)

Practical options (pick one by file type):

If the file is CSV/TSV: keep using fgetcsv or SplFileObject but check delimiter, encoding (UTF‑8/BOM) and line endings first. (php.net)
If the file is .xls/.xlsx: use a spreadsheet library that understands Excel formats (do not try to parse .xls with fopen/fgetcsv). The modern choice is PhpSpreadsheet (successor to PHPExcel). Example (very short) showing the reader + row loop:

use PhpOffice\PhpSpreadsheet\IOFactory;
$spreadsheet = IOFactory::load($path);
$sheet = $spreadsheet->getActiveSheet();
foreach ($sheet->getRowIterator() as $row) {
  // gather cell values and push to DB
}

See PhpSpreadsheet for install/usage. (github.com)

Database and safety notes: avoid the old mysql_* API (it was deprecated/removed); use PDO or mysqli with prepared statements to prevent SQL injection and to be compatible with modern PHP. Example pattern: prepare an INSERT once, then execute it per row inside a transaction or batch. Also handle uploads safely (use is_uploaded_file() / move_uploaded_file() and validate MIME/extension with finfo_file() or mime_content_type()). (php.net)

Performance/troubleshooting tips: for very large CSVs consider LOAD DATA INFILE for bulk import, or process in chunks (PhpSpreadsheet supports read-filters / chunked reads) to avoid memory exhaustion. Always check $_FILES['yourfield']['tmp_name'] and that your form used enctype="multipart/form-data". If a post here used $filename = $_FILES; that’s incorrect — use the tmp_name entry or move the uploaded file to a known path first. (dev.mysql.com)

Summary: if uploads are CSV, fix delimiters/encoding and use fgetcsv; if they are true Excel files, switch to PhpSpreadsheet + PDO, and use chunking or LOAD DATA for large batches.

All 8 Replies

pritaeas 2,276 Most Valuable Poster

16 Years Ago

rajabhaskar525 1 Junior Poster

16 Years Ago

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.

Reply to this topic

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.

muralibobby2015 17 Posting Pro · Answer 1 · 2009-12-03T18:59:31+00:00

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?

rajabhaskar525 1 Junior Poster · Answer 2 · 2009-12-03T19:14:54+00:00

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.

muralibobby2015 17 Posting Pro · Answer 3 · 2009-12-03T19:20:37+00:00

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.

indoreankita 0 Newbie Poster · Answer 4 · 2009-12-04T18:31:23+00:00

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

rajabhaskar525 1 Junior Poster · Answer 5 · 2009-12-04T18:38:30+00:00

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

crohole 0 Light Poster · Answer 6 · 2009-12-29T20:10:36+00:00

$filename=$_FILES;

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

Should it be any chance on your codde?

how to parse excel sheet in php

Dani AI

Recommended Answers Collapse Answers

All 8 Replies

Recommended Answers