Totally random database-ish problem

XML, Perl, Python, and other languages can be discussed here, even if it isn't PHP (We might forgive you).

Moderator: General Moderators

Post Reply
SpiderMonkey
Forum Commoner
Posts: 85
Joined: Fri May 05, 2006 4:48 am

Totally random database-ish problem

Post 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.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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.
SpiderMonkey
Forum Commoner
Posts: 85
Joined: Fri May 05, 2006 4:48 am

Post 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.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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.
SpiderMonkey
Forum Commoner
Posts: 85
Joined: Fri May 05, 2006 4:48 am

Post 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?
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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.
SpiderMonkey
Forum Commoner
Posts: 85
Joined: Fri May 05, 2006 4:48 am

Post by SpiderMonkey »

Thanks. I'd better save it too so I don't have to go through this next time :)
Post Reply