Incorrect datetime value while importing in to MySQL table

Question

I'm facing an issue while importing a .csv file into a MySQL table.

mysql> LOAD DATA INFILE '/var/lib/mysql-files/script_output.csv' 
REPLACE INTO TABLE incidents columns terminated by ',' optionally 
enclosed by '"' ignore 1 lines;

ERROR 1292 (22007): Incorrect datetime value: '2021-08-29T04:18:35Z'  
for column 'incident_start' at row 1

But, I can import without an issue with IGNORE command.

mysql> LOAD DATA INFILE '/var/lib/mysql-files/script_output.csv' 
IGNORE INTO TABLE incidents columns terminated by ',' optionally 
enclosed by '"' ignore 1 lines;

Query OK, 0 rows affected, 809 warnings (0.01 sec)  
Records: 266  Deleted: 0  Skipped: 266  Warnings: 809

My requirement is to replace/update the columns in case any update on previous values in the table.

Table Structure

+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| incident       | varchar(12)  | NO   | PRI | NULL    |       |
| description    | varchar(300) | YES  |     | NULL    |       |
| status         | varchar(12)  | YES  |     | NULL    |       |
| urgency        | varchar(7)   | YES  |     | NULL    |       |
| service        | varchar(27)  | YES  |     | NULL    |       |
| trigger        | varchar(25)  | YES  |     | NULL    |       |
| team           | varchar(20)  | YES  |     | NULL    |       |
| incident_start | datetime(6)  | YES  |     | NULL    |       |
| incident_end   | datetime(6)  | YES  |     | NULL    |       |
| resolved_by    | varchar(20)  | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

How we can fix the issue of Incorrect datetime while importing .csv with replace option?

The fastest method I've found for this sort of situation is to open the CSV in Excel, perform some string manipulation to convert the values to what I want (in this case, removing the T and Z characters from dates), then using the updated output as the source ... — matigo
– matigo, Commented Aug 31, 2021 at 7:43
@matigo Or importing the CSV as-is, and doing that conversion in a staging table. — Randolph West
– Randolph West, Commented Sep 2, 2021 at 2:43

Sonu R · Accepted Answer · 2021-08-31 09:48:25Z

I got the solution for this. We just need to remove the STRICT_TRANS_TABLES mode while uploading the csv file. We can use the below commands for that

set session sql_mode = '';
set session sql_mode = 'NO_ENGINE_SUBSTITUTION';

One's it's done, we can upload the data with REPLACE

MariaDB [pagerduty]> LOAD DATA INFILE '/var/lib/mysql/pagerduty/script_output.csv' REPLACE INTO TABLE temp_incidents columns terminated by ',' optionally enclosed by '"' ignore 1 lines;
Query OK, 246 rows affected, 504 warnings (0.004 sec)
Records: 246  Deleted: 0  Skipped: 0  Warnings: 504

Note: The above SQL mode needs to set before uploading the file into the table everytime.

Stack Exchange Network

Incorrect datetime value while importing in to MySQL table

1 Answer 1

Your Answer

Hot Network Questions

Incorrect datetime value while importing in to MySQL table

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions