在从csv导入到mysql期间，在执行命令期间遇到致命错误 [英] Fatal error encountered during command execution while importing from csv to mysql

问题描述

我试图导入csv文件到MySQL数据库一切正常，除非datetime不加载。

I am trying to import csv file to MySQL database everything works fine except datetime does not load.

我的csv文件datetime格式是 19/05/2014 14:44:44'，MySQL日期格式为'2014-02-18 09:44:30'要导入它'0000-00-00 00:00:00'我尝试了不同的方式，但我不明白我会错在哪里。

My csv file datetime format is '19/05/2014 14:44:44' and MySQL date format is '2014-02-18 09:44:30' while trying to import it comes up as '0000-00-00 00:00:00' I tried different ways but i don't understand where am I going wrong.

LOAD DATA LOCAL  INFILE 'I:/finaltest12.csv' INTO TABLE tickets  FIELDS  terminated by ',' ENCLOSED BY '\"'  lines terminated by '\n' IGNORE 1 LINES (SiteId,@var1,Serial,DeviceId,AgentAID,VehicleRegistration,CarPark,SpaceNumber,GpsAddress,VehicleType,VehicleMake,VehicleModel,VehicleColour,IssueReasonCode,IssueReason,NoticeLocation,Points,Notes)  SET DateTime = STR_To_DATE(@var1,'%yyyy-%MM-%dd %H:%i:%s')";
            MySqlCommand cmd1 = new MySqlCommand(strLoadData, conn);

当我运行这个我得到上面的错误遇到致命错误。

when i run this i get the above error fatal error encountered.

您可以在excel中打开csv，然后以mysql格式更改日期时间，然后再以csv格式保存并上传。

You can open your csv in excel then change date time in mysql format and then again save in csv format and upload.

您可以在脚本中按照下面的操作：

You can do as per below in your script:

LOAD DATA LOCAL  INFILE 'I:/finaltest12.csv' INTO TABLE tickets  FIELDS  terminated by ',' ENCLOSED BY '\"'  lines terminated by '\n' IGNORE 1 LINES (SiteId,@var1,Serial,DeviceId,AgentAID,VehicleRegistration,CarPark,SpaceNumber,GpsAddress,VehicleType,VehicleMake,VehicleModel,VehicleColour,IssueReasonCode,IssueReason,NoticeLocation,Points,Notes) SET DateTime = STR_To_DATE(@var1,'%d-%m-%Y %H:%i:%s');

这篇关于在从csv导入到mysql期间，在执行命令期间遇到致命错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！