Skip empty rows

[Seth Watson]

I am importing an Excel sheet into mysql and attempting to skip empty
rows. The empty rows have formulas, but are not pulling data in
Excel.

The following is importing into database fine, but the script has an
error. It's not making it to the close connection. I think the error
is in my attempt to not import empty rows.

Any ideas?

Thanks in advance!

// include class file
include 'reader.php';

// initialize reader object
$excel = new Spreadsheet_Excel_Reader();

// read spreadsheet data
$excel->read('upload/BpData.xls');

// attempt a connection
try {
$pdo = new PDO('localhost', 'username', 'password');
} catch (PDOException $e) {
die("ERROR: Could not connect: " . $e->getMessage());
}

// iterate over spreadsheet rows and columns
// convert into INSERT query
$sql = "INSERT IGNORE INTO Sheet1 (AgId, TmId, TmMbr, prStatus,
prMonth, prDate, prType, prSubtype, prName, prSource, prQty, prMode,
prPrem, prComm) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
if ($stmt = $pdo->prepare($sql)) {
$x=2;
while($x<=$excel->sheets[1]['numRows']) {
if (empty($excel->sheets[1]['cells'][$x][1])) {//attempt to not
import blank rows
continue;
}
$stmt->bindParam(1, $excel->sheets[1]['cells'][$x][1]);
$stmt->bindParam(2, $excel->sheets[1]['cells'][$x][2]);
$stmt->bindParam(3, $excel->sheets[1]['cells'][$x][3]);
$stmt->bindParam(4, $excel->sheets[1]['cells'][$x][4]);
$stmt->bindParam(5, $excel->sheets[1]['cells'][$x][5]);
$stmt->bindParam(6, $excel->sheets[1]['cells'][$x][6]);
$stmt->bindParam(7, $excel->sheets[1]['cells'][$x][7]);
$stmt->bindParam(8, $excel->sheets[1]['cells'][$x][8]);
$stmt->bindParam(9, $excel->sheets[1]['cells'][$x][9]);
$stmt->bindParam(10, $excel->sheets[1]['cells'][$x][10]);
$stmt->bindParam(11, $excel->sheets[1]['cells'][$x][11]);
$stmt->bindParam(12, $excel->sheets[1]['cells'][$x][12]);
$stmt->bindParam(13, $excel->sheets[1]['cells'][$x][13]);
$stmt->bindParam(14, $excel->sheets[1]['cells'][$x][14]);
if (!$stmt->execute()) {
echo "ERROR: Could not execute query: $sql. " . print_r($pdo-
>errorInfo());
}
$x++;
}
} else {
echo "ERROR: Could not prepare query: $sql. " . print_r($pdo-
>errorInfo());
}

// close connection
unset($pdo);

//kill file
unlink('upload/BpData.xls');

//redirect
header( 'Location: result2.php' );

[Nyle Davis]

Seth,

Did you find a solution?  I get over 5,000 emails daily so just saw this

What I would do is look at the "Value" of the cell.  Formulas always start with "=" so PHP line of code should be something like:

if (left(trim(cell.value),1) == "=") { continue; }

Hope that helps if you are still stuck!

Nyle E. Davis
AKA: Durwood
AKA: OldManRiver
AKA: TBotNik (The BotNik)
AKA: The Red Neck Santa
davis...@gmail.com
davi...@mail.com
(214)-779-6918 Cell