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
Import not liking .CSV file dates in format mm/dd/yyyy
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;
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!
Oh well. Just a thought. Still...thanks so much again for your workaround!
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.