1

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?

2
  • 2
    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
    Aug 31 at 7:43
  • @matigo Or importing the CSV as-is, and doing that conversion in a staging table. Sep 2 at 2:43
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.