Help Please! Excel to MySQL import script whaky

[mmcculley]

I need some help with a script that wrote to import an excel spreadsheet into mysql. It is supposed to update records with the same phone number and insert when there is no match.

This is meant to eliminate duplicates within the database. It doesn't work quite right though.

<?php

function CheckNumbers($FirstNumber)
{
$sql = "
SELECT Count(`DNCList`.`DNCNumber`)
FROM `DNCList`
WHERE `DNCList`.`DNCNumber` = " . $FirstNumber;
$result = mysql_query($sql) or die('Error in function CheckNumbers()');
$CheckNumbers = mysql_fetch_array($result);
$CheckNumbersResult = $CheckNumbers[0];
return $CheckNumbersResult;
} 

error_reporting(E_ALL);

$mtime = microtime();
$mtime = explode(' ', $mtime);
$mtime = $mtime[1] + $mtime[0];
$starttime = $mtime;

require_once 'Excel/reader.php';
$InsertCount = 0;
$UpdateCount = 0;
$TotalCount = 0;
$Company = $_POST['Company'];
$SalesList = $_POST['SalesList'];
$Source = $_POST['txtProspSource'];
$NumRecords = $_POST['NumRecords'];
$Descriptor = $_POST['Descriptor'];
$Expiration = $_POST['Expiration'];
$Imported = 1;
//$sqlInsertArray = array();
$FileName = basename($_FILES['UploadFile']['name']);
$uploaddir = "/files/";
$uploadfile = $uploaddir . basename($_FILES['UploadFile']['name']);
$formatted_date = DateTimeSQLtoUnix(DateUSAtoSQL($_POST['txtDate']));
$formatted_datec = DateUSAtoSQL(date("m/d/Y", time()));
$DateRange = DateUSAtoSQL($_POST['txtDate']);
$sqlInsert = "INSERT INTO tblSalesDailyProspectListsInd (idPartner, txtDate, txtProspCust, txtProspHomePhone, txtProspWorkPhone, txtProspSource, txtProspComments, txtProspAddress, txtProspCity, txtProspState, txtProspZip, txtProspEmailAddress, bolImported, txtImportKey, created_date) VALUES ";

$sqlUpdate = "";

if ($_POST['Scrub'] == 'true') {
$Scrub = 'true';
} else {
$Scrub = 'false';
}
$Begining = mydate('F j, Y', $DateRange);

echo '</div><div id="content" style="font-size: 14px; text-align:left; width:100%;">';

if (!move_uploaded_file($_FILES['UploadFile']['tmp_name'], $uploadfile)) {
echo "Possible file upload attack!\n";
} else {
$OpenDisplay = "<div id='Head'><h3>File<strong>(" . $uploadfile . ")</strong> is valid, and was successfully uploaded.</h3></div>";
if ($Scrub == 'true') {
$OpenDisplay .= "<div id='Head'><h3>Scrubbing DNC numbers.</h3></div>";
} else {
$OpenDisplay .= "<div id='Head'><h3>Not Scrubbing DNC numbers.</h3></div>";
}
echo $OpenDisplay;
require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read($uploadfile);
error_reporting(E_ALL ^ E_NOTICE);
$i = 2;
$daily = 0;
$DroppedNumbers = 0;
echo $data->sheets[0]['numRows']."<br>";
while ($i <= $data->sheets[0]['numRows']) {
foreach($SalesList as $ID) {
$partner = $ID;
//			echo $partner;
for($m = 1; $m <= $NumRecords; $m++) {
//				echo "Row ".$i."<br>";
if ($i <= $data->sheets[0]['numRows']) {
if ($data->sheets[0]['cells'][$i][2] <> '') {

$CustName = mysql_real_escape_string($data->sheets[0]['cells'][$i][2]);
if (strlen($data->sheets[0]['cells'][$i][7]) <= 0){ 
$HomeNumber = '0';
}else{
$HomeNumber = "CONV(replace(replace(replace(replace(replace(replace('".$data->sheets[0]['cells'][$i][7]."',' ',''),'-',''),'(',''),')',''),'_',''),'.',''),10,10)";
}
if (strlen($data->sheets[0]['cells'][$i][8]) <= 0){ 
$WorkNumber = '0';
}else{
$WorkNumber = "CONV(replace(replace(replace(replace(replace(replace('".$data->sheets[0]['cells'][$i][8]."',' ',''),'-',''),'(',''),')',''),'_',''),'.',''),10,10)";
}
$Comments = $Descriptor." ".mysql_real_escape_string($data->sheets[0]['cells'][$i][3])." ". mysql_real_escape_string($data->sheets[0]['cells'][$i][4])." ".mysql_real_escape_string($data->sheets[0]['cells'][$i][5])." ".mysql_real_escape_string($data->sheets[0]['cells'][$i][6]);
$Address = mysql_real_escape_string($data->sheets[0]['cells'][$i][9]);
$City = mysql_real_escape_string($data->sheets[0]['cells'][$i][10]);
$State = mysql_real_escape_string($data->sheets[0]['cells'][$i][11]);
$Zip = mysql_real_escape_string($data->sheets[0]['cells'][$i][12]);
$Email = mysql_real_escape_string($data->sheets[0]['cells'][$i][13]);

if ($Scrub == 'true') {
$NumberResults1 = CheckNumbers($HomeNumber);
$NumberResults2 = CheckNumbers($WorkNumber);

if (!($NumberResults1 > 0 && $NumberResults2 > 0) && !(strlen($data->sheets[0]['cells'][$i][7]) < 7 && strlen($data->sheets[0]['cells'][$i][8]) < 7) && !($NumberResults1 > 0 && strlen($data->sheets[0]['cells'][$i][7]) < 7) && !($NumberResults2 > 0 && strlen($data->sheets[0]['cells'][$i][8]) < 7) && !($data->sheets[0]['cells'][$i][7] == '0' || $data->sheets[0]['cells'][$i][8] == '0')) {


$sqlUpdate = " UPDATE
`tblSalesDailyProspectListsInd`
SET
`txtProspSource`='$Source',
`txtProspComments`=CONCAT_WS('/n/r',`txtProspComments`,'$Comments'),
`bolImported`='$Imported',
`txtImportKey`='$FileName',
`Expiraton_Date`='$Expiration'
WHERE
(";
if ($data->sheets[0]['cells'][$i][7] <> 0)
{
$sqlUpdate .= "`txtProspHomePhone`= $HomeNumber OR `txtProspWorkPhone`= $HomeNumber ";
}
if ($data->sheets[0]['cells'][$i][7] <> 0 && $data->sheets[0]['cells'][$i][8] <> 0)
{
$sqlUpdate .= " OR ";
}
if ($data->sheets[0]['cells'][$i][8] <> 0)
{
$sqlUpdate .= "`txtProspHomePhone`= $WorkNumber OR `txtProspWorkPhone`= $WorkNumber ";
}
$sqlUpdate .= ");";
//echo $sqlUpdate."<br>";
mysql_query($sqlUpdate);
//it will try to update, you can the use mysql_affeted_rows to see if a row was affected.
if(mysql_affected_rows > 0)
{
/*
Append Data to Current Record.
*/
$UpdateCount++;
echo "Duplicate Record - Append Record<br>";
//									$num = mysql_affected_rows();
//									echo "Insert $num<br>";
}else{
//execute the insert statement
//									$sqlInsertArray[] ="('$partner','$DateRange','$CustName',$HomeNumber,$WorkNumber,'$Source','$Comments','$Address','$City','$State','$Zip','$Email','$Imported','$FileName','$formatted_datec')";
$sqlInsert2 ="('$partner','$DateRange','$CustName',$HomeNumber,$WorkNumber,'$Source','$Comments','$Address','$City','$State','$Zip','$Email','$Imported','$FileName','$formatted_datec')";
$sqlInsertString = $sqlInsert.$sqlInsert2;
mysql_query($sqlInsertString) or die(mysql_error());
$InsertCount++;
}
} else {
echo "Not a Valid # - Skip record<br>";
$DroppedNumbers++;
/*
Skip Record.
*/
--$m;
}
} else {
if ($data->sheets[0]['cells'][$i][7] <> '' || $data->sheets[0]['cells'][$i][8] <> '') {
$sqlUpdate = "
UPDATE
`tblSalesDailyProspectListsInd`
SET
`txtProspSource`='$Source',
`txtProspComments`=CONCAT_WS('/n/r',`txtProspComments`,'$Comments'),
`bolImported`='$Imported',
`txtImportKey`='$FileName',
`Expiraton_Date`='$Expiration'
WHERE
(";
if ($data->sheets[0]['cells'][$i][7] <> 0)
{
$sqlUpdate .= "`txtProspHomePhone`= $HomeNumber OR `txtProspWorkPhone`= $HomeNumber ";
}
if ($data->sheets[0]['cells'][$i][7] <> 0 && $data->sheets[0]['cells'][$i][8] <> 0)
{
$sqlUpdate .= " OR ";
}
if ($data->sheets[0]['cells'][$i][8] <> 0)
{
$sqlUpdate .= "`txtProspHomePhone`= $WorkNumber OR `txtProspWorkPhone`= $WorkNumber ";
}
$sqlUpdate .= ");";
//echo $sqlUpdate."<br>";
mysql_query($sqlUpdate);
//it will try to update, you can the use mysql_affeted_rows to see if a row was affected.
if(mysql_affected_rows <=0)
{
//execute the insert statement
//	$sqlInsertArray[] ="('$partner','$DateRange','$CustName',$HomeNumber,$WorkNumber,'$Source','$Comments','$Address','$City','$State','$Zip','$Email','$Imported','$FileName','$formatted_datec')";
$sqlInsert2 ="('$partner','$DateRange','$CustName',$HomeNumber,$WorkNumber,'$Source','$Comments','$Address','$City','$State','$Zip','$Email','$Imported','$FileName','$formatted_datec')";
$sqlInsertString = $sqlInsert.$sqlInsert2;
mysql_query($sqlInsertString) or die(mysql_error());
$InsertCount++;
}else{
/*
Append Data to Current Record.
*/
$UpdateCount++;
echo "Duplicate Record - Append Record<br>";
//	$num = mysql_affected_rows();
//	echo "Insert $num<br>";
}
} else {
echo "Not a Valid # - Skip record<br>";
/*
Skip Record.
*/
--$m;
}
} // Scrub or Not
} // Has Name
$i++;
} // Rows Check
} // For # of records/salesperson Loop
} // Foreach Salesperson Loop
} // While loop for files records
} // Actual File
// Run Inserts
//$sqlValues = implode(',',$sqlInsertArray);
//$sqlInsertString = $sqlInsert.$sqlValues;
//// echo $sqlInsertString;
//mysql_query($sqlInsertString) or die(mysql_error());
//$RealInsertCount = mysql_affected_rows();

$DateList[] = mydate('F j, Y', $DateRange);
$DailyList .= "<div id='Head'>" . $daily . " Records imported for " . mydate('F j, Y', $DateRange) . "</div>";
$DateRange = DateUSAtoSQL(DateAdd('d', 1, DateTimeSQLtoUnix($DateRange)));
$daily = 0;
//$timer->debug();
//$timer->showtime();
// echo $DailyList;
$TotalCount = $UpdateCount + $InsertCount + 1;
$lastkey = count($DateList)-1;
$xls_num_rows = $data->sheets[0]['numRows'];
$missed = $data->sheets[0]['numRows'] - $TotalCount;
$Last = $DateList[$lastkey];
$mtime = microtime();
$mtime = explode(" ", $mtime);
$mtime = $mtime[1] + $mtime[0];
$endtime = $mtime;
$totaltime = ($endtime - $starttime);
$EndDisplay = "
<div id='Head'><h3>$TotalCount of $xls_num_rows total records were imported from the XLS upload, <br />
with $NumRecords Records/Salesperson/Day for a time period <br />
from $Begining to $Last.<br /> $missed records failed import criteria.<br>
$InsertCount Records Inserted.<br>
$UpdateCount Records Updated.<br>
$DroppedNumbers numbers were dropped for being on the DNC List.<br>
It took " . $totaltime . " seconds for import.</h3></div>";

echo $EndDisplay;
$mailMessage = $OpenDisplay . $EndDisplay; // $DailyList . $EndDisplay;
// SendEmailAttachment($mailMessage, $Company, $uploadfile);
// SendEmail($mailMessage, $Company);
unlink($uploadfile);

?>

[thepeccavi]

Firstly, this query looks dodgy...

$sql = "
SELECT Count(`DNCList`.`DNCNumber`)
FROM `DNCList`
WHERE `DNCList`.`DNCNumber` = " . $FirstNumber;

It may be better with

$sql = "
SELECT Count(`DNCList`.`DNCNumber`)
FROM `DNCList`
WHERE `DNCList`.`DNCNumber` = '" . $FirstNumber . "'";

Unfortunately, I do not know alot about data conversion or excel, personally, OpenOffice.org Calc is nice because it stores everything as a zipped lot of xml files...

If you get open office an open this Excel doc in it, then save as a .ods, rename to a .zip and unzip it... you will find a few xml files... this could be very useful for you...

Hope that I have helped!

[mmcculley]

Thanks for the reply thepeccavi.

But if you look closer (and I'll admit it probably isn't the most prof way to do it, but that function is actually passed a string like this

CONV(replace(replace(replace(replace(replace(replace('".$data->sheets[0]['cells'][$i][8]."',' ',''),'-',''),'(',''),')',''),'_',''),'.',''),10,10)"; 
} 

$HomeNumber = "CONV(replace(replace(replace(replace(replace(replace('".$data->sheets[0]['cells'][$i][7]."',' ',''),'-',''),'(',''),')',''),'_',''),'.',''),10,10)"; 
.
.
.
$NumberResults1 = CheckNumbers($HomeNumber); 

So the quotes added to the function would break the query, making it search for the string instead of the cleaned up number.

Mostly though it seems to be in the PHP logic I have instead of the database side of it, but hey what do I know, its broke right?

[thepeccavi]

I'm sorry but this is not my area, maybe you could try my sudjestion of converting to the OpenDocument format, I find that, from a techie's point of view, this is much more useful...

Apart from this, there is no more help I can give you.

Sorry about that, I really must read up about file format conversion with PHP, one day I may have to do it myself.....

[mmcculley]

Yeah, I need to look into XML, never have taken the time to see what all I can do with it. Not even sure where to begin I guess. How much easier is it to use xml in php as opposed to flat text or even db of some sort?

[thepeccavi]

XML... never worked with it myself much...

The good thing about XML is that it can be directly printed to a page, and, with an XSL stylesheet, be presented like that! Also, it should be easier to parse with PHP... there are functions built just for XML so they should help!

[bogu]

mmcculley wrote:

It doesn't work quite right though.

What isnt doing right?
I'm sorry but I cant test your script, I dont have your xls class reader ...