CODEWITHSUNDEEP
phpphpexcel
itsmecidz
itsmecidz

Reputation: 93

I need to import an excel file into database using PHPExcel the issue is with the date

I need to import an excel file into database using PHPExcel the issue is with the date,The date can be in any format in excel file.

when the date in excel file is in this format 12/12/2012 (m/d/y) it save correctly into database but if the format date in excel file is this 24/11/2014(d/m/y) it upload blank when 24/12/2012(d/m/y) the value is 2036-03-01

$data = $objWorksheet->getCellByColumnAndRow($col, $row);
if(PHPExcel_Shared_Date::isDateTime($data)){
  $cellValue = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
  $dateValue = PHPExcel_Shared_Date::ExcelToPHP($cellValue);

  $dob =  date('Y-m-d',$dateValue);                                         
} else{
  $datedata = "NULL";               
}

hope you can help me guyz

Upvotes: 1

Views: 493

Answers (2)

ivan.sim
ivan.sim

Reputation: 9268

The important thing here to be reminded of is that Excel stores dates and times as serial date or serial date-time. This is the number of days since Jan 0, 1900 plus a fraction of a 24 hour day. This article contains a good explanation.

The fact that you are getting a negative serial date like -454654643 probably mean that the formatt type of the cells in your Excel spreadsheet is incorrect.

There are two things you need to verify:

  1. In Excel, use Ctrl + ` to reveal the serial date value of each date cell. None of these values should be negative. Then use Ctrl + ` to switch back to the default formatted-date view. (The ` key is the one above the tab key on your keyboard).
  2. Right-click on the date cells, navigate to Format Cells > Number > Date > Type, and ensure that the date format specified in Type matches your intent. So if your dates are to be interpreted as d/m/y, make sure the Type matches. And likewise, for m/d/y.

Hope this helps.

Upvotes: 2

Siniseus
Siniseus

Reputation: 121

I would try formatting the data from the SELECT statement you are using. Let the DB do the time work. Let the PHP script to the real work.

Here is a link to the MYSQL Reference guide that explains all the many way to play with date and time on the database side.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

What you are looking for is 

SELECT date_format('2014-10-13 22:00:00, '%m-%d-%Y') from <table> WHERE <column> = <data>

this will output 10-13-2014

From here you should be able to figure out the rest.

Upvotes: 2

Related Questions