PHP MySQL from Excel

[pertrai1]

Need help if possible. I have a table in my database that is storing a date as 01/01/2010. For the sake of my program this is stored as varchar. I am using an importer tool to import from excel. When I import from excel it is not adding the date correctly into the database. Is it possible to take a date column from excel and have it imported into mysql to stay the way I want it to using varchar even though the column in excel is a date column? Is there php code that will handle this for me. Right now it is importing it as 00/2424/10 when the date is 06/24/2010. Please let me know if it is possible to do this and provide php code example as I am a beginner in php code.

 

Thank you

 

Rob

[Psycho]

You should store dates as, well, dates. If your program needs to display dates in a specific format then you format it after you extract it from the database. You are creating a problem where there shouldn't be one to begin with.

[PFMaBiSmAd]

What exact method are you currently using to import the data?

[pertrai1]

I have changed the start_date column to DATE. I am using excel reader to read the imported file. I am still having problems getting the date to enter into the database correctly. I have the column in excel set as yyyy-mm-dd and then have the following code to insert it:

 

$linearray_dummy[$i]=$edata->sheets[0]['cells'][$i];
$start = mysql_real_escape_string($linearray_dummy[$i][8],$loggedin);

[ChemicalBliss]

Why not export from excel to a csv, then use php's native csv functions to pop it into the db, you can perform checks etc at the same time then.

 

This is what i had to do when updating an old cash register .

 

-cb-

[pertrai1]

The code allows for import of csv or excel and the excel feature is much easier than having to export as csv and then import. Everything works great except trying to get the column with the date to import correctly into the database.

 

$z=0;
$succs=0;
$edata = new Spreadsheet_Excel_Reader();
$edata->setOutputEncoding('CP1251');
if($_FILES['csvfile']['tmp_name'])
	{
		$edata->read($_FILES['csvfile']['tmp_name']);
	}
error_reporting(E_ALL ^ E_NOTICE);
for ($i = 1; $i <= $edata->sheets[0]['numRows']; $i++)
{
if($i == 1)
	{
		$linearray_dummy[$i]=$edata->sheets[0]['cells'][$i];

if($linearray_dummy[$i][25]=="" || $linearray_dummy[$i][25]!="approved")// for getting correct array index
	{
		echo databaseFailure('File Format is wrong','Some headings are missing in your current file as per the sample file. Please Try Again.');
		if(file_exists($csvfile))
			{
				unlink($csvfile);
			 }
	}
} else {

	if($edata->sheets[0]['numCols']>25)
		{
			for ($j=25; $j <= $edata->sheets[0]['numCols']; $j++)
				{
					if( $edata->sheets[0]['cells'][$i][$j]!="")
						{
							$lno[$z]=$i;
							$z++;
							break;
						}

				}
		}
}
}

if(count($lno)>0)
{
	echo '<div class="ui-state-error ui-corner-all form-handling">
		<a href="#" id="close" class="ui-icon ui-icon-circle-close errors-close">Close</a>
		<h1>Database Mismatch</h1><ul>';
			for($n=0;$n<count($lno);$n++)
				{
					echo "<li>#Line: <font color='red'>". $lno[$n]."</font></li>";
				}
		'</ul></div>';
}
if(count($Totalerror)!=0)
{
echo '<div class="ui-state-error ui-corner-all form-handling">
		<a href="#" id="close" class="ui-icon ui-icon-circle-close errors-close">Close</a>
		<h1>Database Mismatch</h1><ul>';
			for($i=0;$i<count($Totalerror);$i++)
			  {
				  echo  '<li><strong>#Line: <font color="red">'.$Actualline[$i].'</font>---<font color="#A52A2A">'.$Totalerror[$i].'</font></strong></li><br>';
			  }
		'</ul></div>';
}

for ($i = 2; $i <= $edata->sheets[0]['numRows']; $i++)
{
$linearray_dummy[$i]=$edata->sheets[0]['cells'][$i];
if($edata->sheets[0]['numCols']==25)
	{
		$id = $linearray_dummy[$i][1];
		$title = mysql_real_escape_string(stripslashes($linearray_dummy[$i][2]),$loggedin);
		$permname = mysql_real_escape_string(strtolower($linearray_dummy[$i][2]),$loggedin);
		$permalink = trim(preg_replace("/[^\w]+/","-",html_entity_decode($permname)), '-');
		$start = mysql_real_escape_string($linearray_dummy[$i][8],$loggedin);
		$d_for = explode('/', $start);
		$day = $d_for[1];
		$month = $d_for[0];
		$year = $d_for[2];
		$end = mysql_real_escape_string($linearray_dummy[$i][9],$loggedin);
		$description = mysql_real_escape_string(stripslashes($linearray_dummy[$i][4]),$loggedin);
		$website = mysql_real_escape_string(stripslashes($linearray_dummy[$i][10]),$loggedin);
		$contact = mysql_real_escape_string(stripslashes($linearray_dummy[$i][11]),$loggedin);
		$phone = mysql_real_escape_string(stripslashes($linearray_dummy[$i][13]),$loggedin);
		$email = mysql_real_escape_string(stripslashes($linearray_dummy[$i][12]),$loggedin);
		$location = mysql_real_escape_string(stripslashes($linearray_dummy[$i][14]),$loggedin);
		$address = mysql_real_escape_string(stripslashes($linearray_dummy[$i][15]),$loggedin);
		$city = mysql_real_escape_string(stripslashes($linearray_dummy[$i][16]),$loggedin);
		$state = mysql_real_escape_string(stripslashes($linearray_dummy[$i][17]),$loggedin);
		$zip = mysql_real_escape_string(stripslashes($linearray_dummy[$i][18]),$loggedin);
		$club = $linearray_dummy[$i][19];
		$breed = $linearray_dummy[$i][20];
		$association = $linearray_dummy[$i][21];
		$active = $linearray_dummy[$i][22];
		$featured = $linearray_dummy[$i][23];
		$suggest = $linearray_dummy[$i][24];
		$approved = $linearray_dummy[$i][25];
		$fields1= Array("id","title","permalink","description","day","month","year","start_date","end_date","website","contact","contactEmail","contactPhone","location","address","city","state","zip","clubId","breedId","associationId","is_active","featured","ejsuggest","approved");
		$linearray_orginal = Array("","$title","$permalink","$description","$day","$month","$year","$start","$end","$website","$contact","$email","$phone","$location","$address","$city","$state","$zip","$club","$breed","$association","$active","$featured","$suggest","$approved");
		$fields = implode(",",$fields1);
		$linemysql = implode("','",$linearray_orginal);

	if($addauto){
	//$query = "insert into $databasetable values('','$linemysql');";
	$query = "INSERT INTO calendar VALUES('','$linemysql');";
	} else {
		if($edata->sheets[0]['numCols']==25)
		{
			//$query = "insert into $databasetable($fields) values('$linemysql');";
			$query = "INSERT INTO calendar($fields) VALUES('$linemysql');";

			$queries .= $query . "\n";
			if(@mysql_query($query))
			{
			$succs++;
			}
			$rowcount++;
		}
	}
}
}

if(file_exists($csvfile))
{
unlink($csvfile);
}
}

if($succs!=0)
{
if($succs>1)
{
	echo databaseSuccess('File Import Success','Successfully added your <font color="red"><b>'.$succs.'</b></font> records</font>');
} else {
	echo databaseSuccess('File Import Success','Successfully added your <font color="red"><b>'.$succs.'</b></font> record</font>');
}
} else {
	echo databaseFailure('File Import Failure','No records where added to the system.');
}

[PFMaBiSmAd]

You should echo the date value after you receive it in your php code to see exactly what you are receiving.

 

The fact that you are exploding on the '/' would indicate it might not be a value that mysql can parse. The actual separator being '/' is not a problem but if the fields are not in the expected order or with leading zero's in the month and day fields, mysql cannot parse it correctly into a DATE column.

 

Why are you exploding the start date and also inserting the resulting day, month, and year? That results in duplicate data and once you have a DATE value in your table you can do anything you want with it directly in any query using the built-in mysql date/time functions.

[pertrai1]

Hi, thank you for the reply. I am exploding because I have a separate column for day month year for a calendar feature. I will do the mentioned echo and see what I come up with. Thank you for taking the time.

[PFMaBiSmAd]

Actually, now that I see your code, based on the explode, you are receiving a m/d/yyyy or mm/dd/yyyy format.

 

To convert that to a yyyy-mm-dd DATE value in your php code, you would need to do the following -

 

 $start = date('Y-m-d',strtotime($start)); // convert m/d/yyyy or mm/dd/yyyy into a yyyy-mm-dd format

[pertrai1]

Ok, for a date coming from excel as 06/23/10 it is comes back as 40354. What is happening here and how can I make the appropriate changes? Thank you in advance for help.

 

Rob

[PFMaBiSmAd]

That's the number of days since Jan 1, 1900 (Windows) or Jan 1, 1904 (Mac.)

 

Earlier in this thread you were probably getting a more usable value, before you changed the excel column date format. I would recommend changing it back to what you had so that you get a m/d/yyyy value.

 

For the current excel column format, you will need the formula to convert the number of days since Jan, 1, 1900 (or 1904 depending on Operating System) into a usable date value.