Import not liking .CSV file dates in format mm/dd/yyyy

mbabcock_mslc posted 6 years ago in Import/Export
I'm trying to import data (many columns) and the date fields in this CSV file are formatted like 12/31/2010. I get the following in the Warnings afterwards:

Data truncated for column 'frst_dt' at row 1

...and for the next 64 rows (warning limit set to 64).

Visually looking at data after import: dates are all zeros (0000-00-00).

How do I get HeidiSQL to recognize the MM/DD/YYYY format in my import files?

ansgar posted 6 years ago
I would use VARCHAR columns for these columns, then import these as simple string values. Afterwards you can fix your dates via UPDATE/CONCAT.

<do the import>
UPDATE mytable SET datecol=CONCAT_WS('-', SUBSTR(datecol, 7, 4), SUBSTR(datecol, 1, 2), SUBSTR(datecol, 4, 2));
mbabcock_mslc posted 6 years ago
neat workaround. I'll try it. Thanks!
mbabcock_mslc posted 6 years ago
Anse -- this worked. Thanks again. I would suggest, though, a future enhancement request/feature that accounts for this date format, thus getting it inputted right the first time without need for re-work. ???
ansgar posted 6 years ago
I think this is too special for being implemented in HeidiSQL. Please keep in mind, you have this format in your file, other users may have totally different date formats, and tomorrow you say you have a file with another date format in it.
mbabcock_mslc posted 6 years ago
Really? Ok. I just figured this was one of the "usual American" kind of things that might not be so outside the USA's borders due to customs/defaults, but within the USA, it might be prevalent. And if that's the case, then a lot of users in the USA would benefit from it. Then again, perhaps this is a rare situation (but I wouldn't think so).

Oh well. Just a thought. Still...thanks so much again for your workaround!
ansgar posted 6 years ago
I think the above ALTER/UPDATE fix is much more powerful here.
ypaty posted 5 years ago
A better solution is to reformat the date in excel in half a second in to a yyyy-mm-dd formate and import right into the date field.
mbabcock_mslc posted 5 years ago
Bringing Excel into the equation means another tool required. If you've got the latest Excel, fine, but if you've got an earlier version where there's a row limit of well less than a million, this option isn't valid. (And for my data, there's more than a million rows.) I'd like to not introduce more links in the chain if at all possible.

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