Date Format for CSV

lotiara's profile image lotiara posted 9 months ago in General Permalink

Hi, I am often given csv files with non MYSQL firendly dates. "14/02/2023 00:01"; d1,d2,etc Is it possible to specify this format to the import routine ? I am always opening with notepad++ and formating correctly "2023-02-1 00:01"; d1,d2,etc thst is very tedious with big csv files.

Any solution ?

bye.

ansgar's profile image ansgar posted 9 months ago Permalink

Not directly in the dialog for importing. But you can write your own LOAD DATA command to inject some more logic for your date fields. Here's an example of HeidiSQL converting FLOAT values from a locale format: "1.000,23" to the format MySQL wants: "1000.23" in two columns. You can easily use some SUBSTR commands to convert your date values to "YYYY-MM-DD HH:ii:ss".

LOAD DATA LOW_PRIORITY
LOCAL INFILE 'some.csv'
INTO TABLE `mytable`
CHARACTER SET UTF8
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@ColVar0, @ColVar1) SET
   `id` = REPLACE(REPLACE(@ColVar0, '.', ''), ',', '.'),
   `rank` = REPLACE(REPLACE(@ColVar1, '.', ''), ',', '.')
;
ansgar's profile image ansgar posted 9 months ago Permalink

Note for columns you like to import without conversion, you just need to add their original column names in the parentheses:

...
(description, title, @ColVar0, @ColVar1) SET
   `id` = REPLACE(REPLACE(@ColVar0, '.', ''), ',', '.'),
   `rank` = REPLACE(REPLACE(@ColVar1, '.', ''), ',', '.')
;
lotiara's profile image lotiara posted 9 months ago Permalink

Thank you I will try it.

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