Mysql – Importing True/False as tinyint failing when last field

MySQL

Here is a line from the file I am loading.

"dev"|"APSET3454"|"apset3454.xxx.xxx.xxx"|"TWS01"|31111|True|False|False|"1"|True|"Win2012R2 64 bit ESC:712482-2267174 IEMID:84520"|"2017-12-20T15:06:15.390+0000"|"WINDOWS"|"9.4.0.04"|"FTA"|"America/Chicago"|2602|"2019-02-27T13:15:08.000+0000"|30|0|""|True|True|False|False|"LINKED"|True

And here is the load command I am using. Seems to work fine except that the last field is always comming in as a zero(false)

LOAD DATA LOCAL INFILE "/autohome/astools/Programs/FTA_api/Workdir//nm2.20190305103959.csv" 
INTO TABLE  fta_status.fta_info COLUMNS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' 
ESCAPED BY '"' LINES TERMINATED BY '\n' 
(TWSINSTANCE,CPUID,NODENAME,FTA_DOMAIN,TCPPORT,@AUTOLINK,@BEHINDFIREWALL,@FULLSTATUS, 
SERVER,@INCLUDEINPLAN,DESCRIPTION,@MODIFIEDDATETIME,OS,VERSION,TYPE,TIMEZONE, 
SYMRUNNUMBER,@TIMESTARTED,FTA_LIMIT,FENCE,ACCESSMETHOD,@TCPLINK,@JOBMANUP,@WRITERUP, 
@WRITERDS,LINKSATUS,@WORKSTATIONINPLAN)  
SET AUTOLINK = (@AUTOLINK = 'TRUE'), 
BEHINDFIREWALL = (@BEHINDFIREWALL = 'TRUE'),FULLSTATUS = (@FULLSTATUS = 'TRUE'), 
INCLUDEINPLAN = (@INCLUDEINPLAN = 'TRUE'), 
MODIFIEDDATETIME = DATE_FORMAT(STR_TO_DATE(@MODIFIEDDATETIME,'%Y-%m-%dT%H:%i:%s'),'%Y-%m-%d %H:%i:%s'), 
TIMESTARTED = DATE_FORMAT(STR_TO_DATE(@TIMESTARTED,'%Y-%m-%dT%H:%i:%s'),'%Y-%m-%d %H:%i:%s'), 
TCPLINK = (@TCPLINK = 'TRUE'),JOBMANUP = (@JOBMANUP = 'TRUE'), 
WRITERUP = (@WRITERUP ='TRUE'),WRITERDS = (@WRITERDS = 'TRUE'), 
WORKSTATIONINPLAN = (@WORKSTATIONINPLAN = 'TRUE');

I do get warnings about the 2 datetime fields

Level: Warning
Code: 1292
Message: Truncated incorrect datetime value: '2017-12-20T15:06:15.390+0000'

So if someone would like to help with the date_format STR_TO_DATE conversion as well 🙂

Best Answer

Your instinct is to use STR_TO_DATE() like you did to convert the string format but will not work "correcty" because of the warning.

Query #1

SELECT STR_TO_DATE('2019-02-27T13:15:08.000+0000','%Y-%m-%dT%H:%i:%s');

| STR_TO_DATE('2019-02-27T13:15:08.000+0000','%Y-%m-%dT%H:%i:%s') |
| --------------------------------------------------------------- |
| 2019-02-27 13:15:08                                             |

Query #2

SHOW WARNINGS;

| Level   | Code | Message                                                            |
| ------- | ---- | ------------------------------------------------------------------ |
| Warning | 1292 | Truncated incorrect datetime value: '2019-02-27T13:15:08.000+0000' |

So you will need to be a little more creative with SUBSTRING(), CONCAT() and a CAST()

Query

SELECT
  CAST (
    CONCAT(
       SUBSTRING('2019-02-27T13:15:08.000+0000', 1, 10)
     , ' '
     , SUBSTRING('2019-02-27T13:15:08.000+0000', 12, 8)
   )
   AS DATETIME  
  ) AS result;

Result

| result              |
| ------------------- |
| 2019-02-27 13:15:08 |

see demo

Related Question