How to import date?

PHPMaker General Discussion (PHPMaker)
1

I am literally stuck with this “DATE” or “DATETIME” Import issue.I am trying to import data into a table having only 2 columns Number =>[NO] & Date => [DT].I have tried all possible way to format DATE in import file csv, xls, xlsx. (dd-mm-yyyy “%d-%m-%Y”, mm-dd-yyyy “%m-%d-%Y”, yyyy-mm-dd “%Y-%m-%d”)I am getting below error while i dont add:$row[“DT”] = ExecuteScalar(“SELECT STR_TO_DATE(‘DT’,‘%d-%m-%Y’)”);to row_import.LOG.(without STR_TO_DATE )
C:\wamp64\www\hcdl_vendor\models\DateList.php:2325:
‘row1’ => string ‘An exception occurred while executing ‘UPDATE date SET NO = ?, DT = ? WHERE NO = 1186359425’ with params [1186359425, “29-10-2020”]:
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘29-10-2020’ for column ‘DT’ at row 1’ (length=252)AFAIK error log is showing date format dd-mm-yyyy, so now i added the above STR_TO_DATE to row_import. “”$row[“DT”] = ExecuteScalar(“SELECT STR_TO_DATE(‘DT’,‘%d-%m-%Y’)”);“”
and now i am not getting any value in the DATE Column and its “null” value in F12 network output as below.
i search the “null” value, it says that if given DATE and STR_TO_DATE format donot match it gives “null” or “0000-00-00” value.LOG. (with STR_TO_DATE )
C:\wamp64\www\vendor\models\DateList.php:2325:
array (size=2)
‘NO’ => int 1186359425
‘DT’ => nulli can’t understanding why this is happning.
my PHP-7.3.5 and mysql-5.7.31any help would be greatly appreciated.thanks.

2

The data in your imported file should be in yyyy-mm-dd format (e.g. “2020-10-29”, not “29-10-2020”) so your database can understand, if not, you need to convert them by Row_Import server event (see the topic Server Events and Client Scripts in the help file).

3

Thanks,the “yyyy-mm-dd” trick worked & no need to modify “row_import”.But as i mentioned my import file is from another server & its default dd-mm-yyyy. also i have approx 120+ users on & off uploading same data freuently in default format dd-mm-yyyy.
now its very deficult to change format to all 120+ user’s desktop to support this import.so i am still facing issue for NULL value with dd-mm-yyyy format with row_import modification to as below.
----------Error LOG-----------
C:\wamp64\www\hcdl_vendor\models\DateList.php:2325:
array (size=2)
‘NO’ => int 1186359425
‘DT’ => null-----row_import mod with dd-mm-yyyy format (29-10-2020) ----------------------

function Row_Import(&$row, $cnt)
{
    //return false; // Return false to skip import
    $row["DT"] = ExecuteScalar("SELECT STR_TO_DATE('DT' ,'%d-%m-%Y')");
    Log($cnt); // Import record count
    var_dump($row); // Import row
    return true;
}

i m getting NULL value with above modification for dd-mm-yyyy import format.as i mentioned before if i remove “STR_TO_DATE” from row_import it gives me an error " Incorrect datetime value: ‘29-10-2020’ for column ‘DT’ ", so date format is ‘%d-%m-%Y’, but somehow row_import doesnt understand it and giving me NULL.please help if any other way around.

4

$row[“DT”] = ExecuteScalar(“SELECT STR_TO_DATE(‘DT’ ,‘%d-%m-%Y’)”);

Your SELECT query seems wrong. Is your SELECT DT field from the old database? If so it is already a date field and you should not use STR_TO_DATE, which takes a string input and converts to SQL server date format. The internal SQL data is format independent. You need to convert to string and back only when data format needs to change.If both DT fields are Date fields then try a plain:
$row[“DT”] = ExecuteScalar(“SELECT DT”);

5

Check if the date/month string contain zero or not, that is 1-1-2020 or 01-01-2020 format.

6

SRanade wrote:

$row[“DT”] = ExecuteScalar(“SELECT DT”);

@SRanade, no. sorry, my bad… I didn’t mean copying from another ‘DB’. i just import CSV file containing 2 column ‘1’=>NO ‘2’=>‘DT’
no internal ‘DB’ data import for this table.
if my code is wrong, can you please suggest the code to convert input date in SQL formate.

@sangnandar, yes it contain zeros. i.e. 01-10-2020.

Thanks.

7

This actually can be achieved so easily by using “Row_Import” server event under the List Page.

All you need to do is to convert the date by using PHP date() function from any date format to “yyyy-mm-dd”.Simply put this following code in “Row_Import” server event, right before the return true line:

// assume the date field name is DT as your case above
$var = $row["DT"];
$row["DT"] = date("Y-m-d", strtotime($var));
8

Thanks

your comments,

// assume the date field name is DT as your case above
$var = $row["DT"];
$row["DT"] = date("Y-m-d", strtotime($var));

was very much helpful.
magically these are the exact lines I needed.

9

Hi, I have three columns for date RecievedDate, ScheduleDate, and CompletionDate.
Suggest to me what will be the syntax.

10

You should always refer to the previous code above that already given, for example:

// assume the date field name is RecievedDate as your case above
$varRecievedDate = $row["RecievedDate"];
$row["RecievedDate"] = date("Y-m-d", strtotime($varRecievedDate ));

// and do the similar code for the other two fields ...
11

Thanksit is working fine with CSV but whenever I upload an Excel file date/time does not give the correct date time. Can you please help me on this topic.I am using MySQL Db to store data where date/time is yyyy-mm-dd HH:mm:ss, in Excel file date/time also in yyyy-mm-dd HH:mm:ss but while upload excel file it is not giving proper date/time.

12

It actually depends on how the value of Date/Time in your File Excel are defined. Double check your Excel file, and you may post the data sample of Date/Time from that Excel file.

13

If you use v2024, read Server Events for Import → Row_Import → Example 2.

14

I tried method mentioned in example 2 , It return only date , i want date and time
i tried below mentioned syntax to inlcude time but not getting proper date time
\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDHHmmss);

15

You may adjust this part:

$row["RecievedDate"] = date("Y-m-d", strtotime($varRecievedDate ));

to:

$row["RecievedDate"] = date("Y-m-d H:i:s", strtotime($varRecievedDate ));

so that the Time also will be included.

16

I already tried
$row[“RecievedDate”] = date(“Y-m-d H:i:s”, strtotime($varRecievedDate ));
didn’t got proper date time it is returning 1970-01-01 00:00:00 or some number like 6435 in year

17

dellcentro wrote:

i tried below mentioned syntax to inlcude time but not getting proper date time
\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDHHmmss);

You have wrong code, there is no NumberFormat::FORMAT_DATE_YYYYMMDDHHmmss (see PhpSpreadsheet source) . You may want to correct and post your complete code in your server event for discussion.Be reminded that the example is for Excel dates (stored as sequential serial numbers). If your dates are stored as string in your Excel file, the example doesn’t apply.