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.
Totally random database-ish problem
Moderator: General Moderators
-
SpiderMonkey
- Forum Commoner
- Posts: 85
- Joined: Fri May 05, 2006 4:48 am
-
jamiel
- Forum Contributor
- Posts: 276
- Joined: Wed Feb 22, 2006 5:17 am
- Location: London, United Kingdom
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.
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
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.
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.
-
SpiderMonkey
- Forum Commoner
- Posts: 85
- Joined: Fri May 05, 2006 4:48 am
-
SpiderMonkey
- Forum Commoner
- Posts: 85
- Joined: Fri May 05, 2006 4:48 am