distal-attribute
distal-attribute
distal-attribute
distal-attribute

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

User, date Message
Written by mbabcock_mslc
3 years ago
Category: Import/Export
7 posts since Thu, 01 Mar 12
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?

Thanks,
--Mike
Written by ansgar
3 years ago
5023 posts since Fri, 07 Apr 06
I would use VARCHAR columns for these columns, then import these as simple string values. Afterwards you can fix your dates via UPDATE/CONCAT.

ALTER TABLE `mytable` CHANGE COLUMN `datecol` `datecol` VARCHAR(20) NULL DEFAULT NULL;
<do the import>
UPDATE mytable SET datecol=CONCAT_WS('-', SUBSTR(datecol, 7, 4), SUBSTR(datecol, 1, 2), SUBSTR(datecol, 4, 2));
ALTER TABLE `mytable` CHANGE COLUMN `datecol` `datecol` DATE NULL DEFAULT NULL;

Written by mbabcock_mslc
3 years ago
7 posts since Thu, 01 Mar 12
neat workaround. I'll try it. Thanks!
Written by mbabcock_mslc
3 years ago
7 posts since Thu, 01 Mar 12
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. ???
Written by ansgar
3 years ago
5023 posts since Fri, 07 Apr 06
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.
Written by mbabcock_mslc
3 years ago
7 posts since Thu, 01 Mar 12
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!
Written by ansgar
3 years ago
5023 posts since Fri, 07 Apr 06
I think the above ALTER/UPDATE fix is much more powerful here.
Written by ypaty
2 years ago
1 posts since Fri, 14 Sep 12
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.
Written by mbabcock_mslc
2 years ago
7 posts since Thu, 01 Mar 12
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.