使用PHP将Excel工作表中的日期和时间插入到DB中

问题描述：

I have Excel sheet which has different column,but i need to add some specific column in the data base(email,date and time).The date start from 9/7/14 and every things work well until 9/12/14 ,but when the day become 13 up to end of the month ,I'm getting 1970-01-01 01:00:00 into the data base,again when the month change to 10 ,everything is fine until 10/12/14 and again form 13 I'm getting 1970-01-01 01:00:00 into the data base.

this is my insert.php code

session_start();
$target_dir = $_SESSION["targetDir"];
$file_name = $_SESSION["fileName"];

 ini_set('memory_limit', '512M');
  require_once 'excel_reader2.php';
  require_once 'dbConn.php';
 $data = new Spreadsheet_Excel_Reader($target_dir);
 for($i=0;$i<count($data->sheets);$i++) // Loop to get all sheets in a file.
     {
switch ($i) {
    case 1:
         echo "<p class='font_p'>Rows in Sheet $i : " . count($data->sheets[$i]['cells'])."&nbsp&nbsp&nbsp&nbsp" ;
        $countWithHeader = count($data->sheets[$i]['cells']);
        $finalCount = $countWithHeader - 1;

        for ($j = 1; $j <= count($data->sheets[$i]['cells']); $j++) // loop used to get each row of the sheet
        {
            $data->sheets[$i]['cells'][$j][1];
            $email = mysqli_real_escape_string($connection, $data->sheets[$i]['cells'][$j][1]);
            $first_name = mysqli_real_escape_string($connection, $data->sheets[$i]['cells'][$j][2]);
            $last_name = mysqli_real_escape_string($connection, $data->sheets[$i]['cells'][$j][3]);
            $in_time = mysqli_real_escape_string($connection, $data->sheets[$i]['cells'][$j][5]);
            $out_time = mysqli_real_escape_string($connection, $data->sheets[$i]['cells'][$j][6]);

            $timestampIn = date('Y-m-d H:i:s',strtotime($in_time));
            $timestampOut = date('Y-m-d H:i:s',strtotime($out_time));

            $query = "INSERT INTO study_table (email,last_name,first_name,in_time,out_time)
                      VALUES ('".$email."','".$first_name."','".$last_name."','".$timestampIn."','".$timestampOut."')";

            mysqli_query($connection, $query);
        }
        break;

    case 2:

答

Function strtotime() will return false in your code:

$timestampIn = date('Y-m-d H:i:s', strtotime($in_time));

because $in_time has invalid format (14/09/2014 14:50). Function parses 14/09/2014 as m/d/Y format, but you are trying to use it as d/m/Y. See here for demo.

Easy solution would be to just replace date delimiter / to -, because then format is changed to d-m-Y, which is valid, and strtotime() will know how to parse it. See here for demo and please RTM here to see what are valid date formats.

Best solution to parse unknown format it to use DateTime::createFromFormat() method:

$in_time = DateTime::createFromFormat('!d/m/Y H:i', $in_time);
echo $in_time->format('Y-m-d H:i:s');

demo

使用PHP将Excel工作表中的日期和时间插入到DB中

相关推荐