Import data from CSV - DATE and TIME fields non compatible format

[expired user #10550]'s profile image [expired user #10550] posted 7 years ago in General Permalink

Hi.

I want to insert data from a .csv file into a mySQL table, but there's a field "Date" in my .csv file which is in format 'DD/MM/YYYY' and in order to be inserted into the table it should be in format 'YYYY-MM-DD'.

Also, I have the same issue with another field called "Time" which in my .csv has format 'HH:MM' and in order to be insterted into the table it should be like 'HH:MM:SS' (where in my case SS will be 00 by default, as there isn't provided a value in the .csv file for seconds).

Is it possible to modify the input of "Date" and "Time" fields provided from .csv file, before inserting them into the table, using HeidiSQL?

Thanks in advance.

ansgar's profile image ansgar posted 7 years ago Permalink

The underlying LOAD DATA command doesn't support such things I guess. Although there is a preprocessing logic for local decimal numbers, so probably this is also possible. I would personally go to the MySQL documentation for LOAD DATA and see if you can pass some clause to process your specific date and time format.

Please login to leave a reply, or register at first.