homepage Welcome to WebmasterWorld Guest from 54.242.231.109
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
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!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved