Page 1 of 1
Totally random database-ish problem
Posted: Fri Jun 30, 2006 4:21 am
by SpiderMonkey
Trying to upload a CSV file into a MySQL database, and I'm having trouble with dates.
The dates in the file are formatted DD/MM/YY but the database wants them YYYY-MM-DD instead.
What I'm trying to do at the moment is loading the file into OpenOffice spreadsheet, and telling it to reformat the dates. It performs the operation just fine but it only changes the way the dates are displayed, not the actual underlying data. Its very fustrating seeing the end result on the screen but not being able to save it.
Posted: Fri Jun 30, 2006 4:39 am
by jamiel
Well, I don't know much about OpenOffice Calc , but one idea is to create a new table in MySQL, and do a LOAD DATA INFILE with your date column just being a normal varchar rather than datetime. Then write a little php script to grab the data, convert the date column to the format you want it, and insert it back into your preferred table correctly.
I am sure there will be an easier way or suggestion coming up, but if noone else has any just do that.
Posted: Fri Jun 30, 2006 4:59 am
by SpiderMonkey
Yeah, I thought about that, but it sounds like such a hassle for a simple operation doesn't it?
Seeing as OpenOffice is clearly demonstrating to me that it can do the calculation involved (because it succesfully reformats the data) it is very fustrating that it seems determined not to do what I want it to.
Worse still, I did this same thing about 3 months ago and can't remember how. The data may have been in a different format though.
Posted: Fri Jun 30, 2006 5:04 am
by jamiel
Well, depending on what other data you have, you could try a quick hack and replace all instances of '/' with '-' using sed or vim

. Sorry I only know how to do what you want in Excel not OO.
Posted: Fri Jun 30, 2006 5:05 am
by SpiderMonkey
jamiel wrote:Well, depending on what other data you have, you could try a quick hack and replace all instances of '/' with '-' using sed or vim

. Sorry I only know how to do what you want in Excel not OO.
Well, I've got a very old version of Excel on one of the Macs here, whats the way you know?
Posted: Fri Jun 30, 2006 5:20 am
by jamiel
Old Date:
2006/06/21
Formula (Provided one date is Row A1):
=VALUE(LEFT(A1,4)&"-"&MID(A1,6,2)&"-"&RIGHT(A1,2))
New Date:
2006-06-21
Do that on one row then when you copy that formula, Excel should automatically increment the rows to A2, A3 etc.
Posted: Fri Jun 30, 2006 5:32 am
by SpiderMonkey
Thanks. I'd better save it too so I don't have to go through this next time
