Import Multiple Excel Files to Database

[cvken]

I'm looking at phpexcel and phpexcelreader to import data from numerous excel files to a database.

Anyone have any experience with this. Phpexcel has issues with timeouts and phpexcelreader will read one file perfectly and the rest it appears to concotenate the data in the cell?

I've posted some questions to there boards but they are not frequented that often.

 

If I used phpexcel I have an issue with a catchable fatal error.

 

I'm going to try and not leave a book here so for now I'm just fishing to see if someone has some experience with either class prior to posting a book worth of code.

The phpexcelreader code I will post here with the issue as I would prefer it as it returns the info so quickly.

function findRange($file,$value1,$value2)
{

$reader = new Spreadsheet_Excel_Reader();
//$reader->setOutputEncoding('CP1251');
$reader->setOutputEncoding("UTF-8");

$reader->read($file);
$x = 0;
	foreach($reader->sheets as $k=>$data)
	{
    	foreach($data['cells'] as $row)
    	{
        	foreach($row as $cell)
        	{
		echo "$cell " ;
		//If ($cell === $value1) { $x = 1; }
		//If ($x === 1 ) { echo "$cell " ;	} 
		//If ($cell === $value2) { $x = 0; break; }
        	}
        If ($x === 1 ) { echo "<br>"; }
    	}
	}
echo "<P>";
unset($reader);
}

 

This return the data from 1 file fine, but when looping through files the second and subsequent files return this kind of garabage

 

FINANCIA Ret Ret Co Amo BEGINNING WO 0 0 Ret Ret Co Amo FINANCIAL Rece

In each space is a box which I apparently cannot past here.

 

Any help or advice on which class to go with would be appreciated.

There is no option to import anything other than excel.

[cvken]

hate to but running out of ideas here

<bump>

[shadiadiph]

It's easier to import an excel file as a csv file just need to change the extension then use the fgets function and insert it line by line using  for example

 

$csvcontent = fopen("$file", "r");
while (!feof($csvcontent)) {

$line = fgets($csvcontent);

FORMAT THE LINES REMOVE AND REPLACE ANY APHOSTROPHIES IN LINE AND COMMAS WITH THE HTML VALUES ' ETC
INSERT TO DATA BASE

} // END WHILE LOOP
fclose($csvcontent);

[cvken]

Forgive the question, but to be clear:

But litterally just rename the file test.xls to test.csv and use the import csv function?

Sadly opening and saving the file as csv is not an option due to the large number we are dealing with.

[shadiadiph]

sorry to hear that I just wrote a code that inserts over 150mb of data from 4 csv files to the database in under 2 minutes.

Over 4 million rows. Sorry i don't know about .xls files you might be able to use the above code i added in theory it should work remember to use fclose($csvcontent); after the while loop you can actually run the same script before by adding $row=0; before the while loop and $row++; in the loop to get the number of rows in the file first then insert it using the same format again then run a check if the number of rows is the same as the number inserted to the database.

 

Just try it using the code above doesn't have to be csv to work.

 

Although you have numerous books in one excel file probably not so simple i had a hell of a time with timeouts and memory_limit needs to be inserting row by row not sheet at a time and need to ini_set the timeout to a higher value.

 

Another hint i can give you if it is a big excel file test it on a small one trying to echo tens of thousands of lines your browser will crash everytime

 

 

[cvken]

Thanks will give it a try: is fopen valid with xls?

[cvken]

That looks like a complete mess to translate using fopen--but it might be how I have to go.

One other quick question I'd like to throw out there on this topic.

 

The code below throws this error:

PHP Catchable fatal error: Object of class PHP Excel Worksheet Row could not be converted to string in on line 132

 

function findRange($file,$value1,$value2)
{
$objReader = PHPExcel_IOFactory::createReader('Excel5');//excel2003
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($file); 
$objWorksheet = $objPHPExcel->getActiveSheet();
$rowNumber = 0;
$x = 0; 
$y = 0;
foreach ($objWorksheet->getRowIterator() as $row) 
{
  $y++;
  $cellIterator = $row->getCellIterator();
  $cellIterator->setIterateOnlyExistingCells(false); // This loops all cells, even if it is not set.  By default, only cells that are set will be         
  foreach ($cellIterator as $cell) 
  {
	$searchedvalue = $cell->getValue();
	if ($searchedvalue===$value1) 
		{ 
		$cellcoord = $cell->getCoordinate(); 
		$row = str_replace("N","",$cellcoord)+3;
		$arr[0] = $cellcoord;
		}		
	if ($searchedvalue===$value2) 
		{ 
		$cellcoorde = $cell->getCoordinate();
		$arr[1] = $cellcoorde;
		break;
		}
	if (isset($cellcoord)) { 
			$row ++;
			echo $objPHPExcel->getActiveSheet()->getCellByColumnAndRow(17, $row)->getValue().'<br>';
			}			
  } 
}
unset($objReader);
unset($objPHPExcel);
unset($objWorksheet);
}

 

Anyone see any reason why.

If I don't loop through files it tends to work.  This indicates a memory issue but I've got all the timeout settings set as high as I can.

Am I correct in this.

[cvken]

IT is actuallly throwing a similar error if I try with just one file

PHP Catchable fatal error: Object of class PHPExcel_Worksheet_Row could not be converted to string in C:\inetpub\wwwroot\Classes\PHPExcel\Worksheet.php on line 980

[cvken]

I've found a way around by using the following for loop's:

for ($row = 1; $row <= $highestRow; ++$row) 
    {
      for ($col = 0; $col <= $highestColumnIndex; ++$col) 
      { 
        ....
     }
}

apparently it's a way faster way to loop through the data.  Hopefully if anyone else is trying to process a lot of excel sheets this will help them.