| CSV into MySQL for Dates - formatting
|
tec4
msg:4394201 | 11:38 pm on Dec 4, 2011 (gmt 0) |
Had a quick question,
I have a very large CSV file with dates formatted like 4/29/2005 and would like to insert these values into my MySQL database.
It currently truncates my value and eliminates it by replacing it with 0000-00-00 (currently using the Date format). Is there a way for me to modify either the CSV or DB to correctly transfer the date correctly?
|
g1smd
msg:4394204 | 11:57 pm on Dec 4, 2011 (gmt 0) |
You'll need YYYY-MM-DD format.
If your text editor has RegEx edit capability then you could easily reformat the data.
Replace * with whatever is immediately before and after the date: comma, quote, space, whatever.
First pass: *([0-9][0-9])/([0-9][0-9])/([0-9][0-9][0-9][0-9])* => *\3-\1-\2*
Second pass: *([0-9])/([0-9][0-9])/([0-9][0-9][0-9][0-9])* => *\3-0\1-\2* [edited by: g1smd at 12:03 am (utc) on Dec 5, 2011]
|
tec4
msg:4394205 | 11:59 pm on Dec 4, 2011 (gmt 0) |
Oh okay,
I will try to figure that out then, thanks :)
|
tec4
msg:4397350 | 9:00 am on Dec 13, 2011 (gmt 0) |
Now that is pretty awesome :)
Took me a couple min to figure out how to get the reg-ex working in the actual editor, but that is really neat...didn't even know it was possible. lol - a huge time saver!
Thanks for coming back and adding those couple lines, they helped a ton!
|
|
|
