0

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

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.

E.S
  • 536
  • 1
  • 9
  • 20
pretyv5
  • 105
  • 1
  • 12

1 Answers1

0

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');
Zafar Malik
  • 6,734
  • 2
  • 19
  • 30
  • thanks zafar but i needs this to be done using task scheduler as i am using this code in c# – pretyv5 May 20 '14 at 09:29
  • If you are getting this csv file from exporring data from mysql then there should not be an issue as csv will made in correct format, but if you are getting it from any other external source then you can guide to make csv in correct format..if it is not possible then you can first convert date format in your csv in your script and then you can upload new file...You can try the code mentioned in main body (but it is not completely checked by me) so check your end – Zafar Malik May 20 '14 at 10:06
  • first confirm your script will execute on linux server...? – Zafar Malik May 20 '14 at 10:07