CSV Import and special format of DATETIME-field

NoPlayback's profile image NoPlayback posted 8 months ago in Import/Export Permalink

I am trying to import a csv, which unfortunately has a funny format for a DATETIME field: "MM-DD-YYYY HH:MM:SS" See one line as example: 1,AC,AC,A,,00.00,,12-28-2022 19:14:50,1,0,,,0.0,0.0,0.0,0.0,0,0,0,

For sure this line is not correctly converted into the DATETIME-field i have setup in the table.

Is there any way to define which format is used in the CSV so that i would be able to import the complete CSV-Data?

ansgar's profile image ansgar posted 8 months ago Permalink

I would import that date first into a VARCHAR column, and afterwards do the conversion into the actual DATETIME column:

UPDATE mytable SET
  datetimecolumn = CONCAT(
    SUBSTR(textcol, 7, 4), '-',
    SUBSTR(textcol, 1, 2), '-',
    SUBSTR(textcol, 4, 2), ' ',
    SUBSTR(textcol, 12, 8)
NoPlayback's profile image NoPlayback posted 8 months ago Permalink

Wow, thanks for the fast answer. So no way to specify that in the tool... that is would i already expected. But the solution is cool... i was thinking to move the complete import into a python-code but this is much more convenient.

Many thanks !

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