SEO

#1 (**permalink**) 02-27-2008, 09:33 PM

Is there a way to change the format of date fields MySQL is expecting when
LOADing data from a file? I have no problem with the format MySQL saves the
date but most spreadsheet programs I use don't make it easy to export text
files with date fields in the format YYYY-MM-DD even if I formated the field
that way on-screen.

It would be great if you could tell MySQL on the command line to expect
dates in the format Month/Day/Year or something like that and be able to
interpret that and convert the date to the format it's expecting on the fly.

--
Ferindo

#2 (**permalink**) 02-27-2008, 09:33 PM

At 08:39 PM 10/14/2006, Ferindo Middleton wrote:
>Is there a way to change the format of date fields MySQL is expecting when
>LOADing data from a file? I have no problem with the format MySQL saves the
>date but most spreadsheet programs I use don't make it easy to export text
>files with date fields in the format YYYY-MM-DD even if I formated the field
>that way on-screen.
>
>It would be great if you could tell MySQL on the command line to expect
>dates in the format Month/Day/Year or something like that and be able to
>interpret that and convert the date to the format it's expecting on the fly.
>
>--
>Ferindo

Ferindo,
If you don't want to change the input file to the proper date
format, then you'll need to read the data into a temporary table and
manipulate the string date into a MySQL date 'yyyy-mm-dd'. I belive MaxDb
has the ability to change the date format before loading data. There used
to be a page where you could submit suggestion but I was only able to come
up with this one: http://www.mysql.com/company/contact/. I think MySQL AB
deliberately hides the suggestions page.<g> There is also a comment by
Remco Wendt at http://dev.mysql.com/doc/refman/5.0/en/load-data.html which
shows you how to load European dates that may be of help to you.

Mike

#3 (**permalink**) 02-27-2008, 09:34 PM

I just tested it with Excel, as it will save the date as seen if you save
the worksheet to a text file. I do this quite a bit, actually, to put
spreadsheet data into MySQL. Often I use Excel macros to construct entire
UPDATE or INSERT statements, and save those into a text file for MySQL to
inhale.

I can't speak for OpenOffice.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

> -----Original Message-----
> From: Ferindo Middleton [mailto:ferindo.middleton@gmail.com]
> Sent: Saturday, October 14, 2006 9:40 PM
> To: mysql
> Subject: change format of date fields during LOAD DATA INFILE?
>
> Is there a way to change the format of date fields MySQL is
> expecting when
> LOADing data from a file? I have no problem with the format
> MySQL saves the
> date but most spreadsheet programs I use don't make it easy
> to export text
> files with date fields in the format YYYY-MM-DD even if I
> formated the field
> that way on-screen.
>
> It would be great if you could tell MySQL on the command line
> to expect
> dates in the format Month/Day/Year or something like that and
> be able to
> interpret that and convert the date to the format it's
> expecting on the fly.
>
> --
> Ferindo
>

#4 (**permalink**) 02-27-2008, 09:34 PM

I was using OpenOffice... And I couldn't get it to keep the format
yyyy-mm-dd I saw on screen in that format when I went to save it as a text
file.... I was able to I suppose this should be reported to their developers
as an enhancement.

There's no way to get MySQL to accept dates in a different format when
performing the operation on the command line though?

Ferindo

On 10/16/06, Jerry Schwartz <jschwartz@the-infoshop.com> wrote:
>
> I just tested it with Excel, as it will save the date as seen if you save
> the worksheet to a text file. I do this quite a bit, actually, to put
> spreadsheet data into MySQL. Often I use Excel macros to construct entire
> UPDATE or INSERT statements, and save those into a text file for MySQL to
> inhale.
>
> I can't speak for OpenOffice.
>
> Regards,
>
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
>
> > -----Original Message-----
> > From: Ferindo Middleton [mailto:ferindo.middleton@gmail.com ]
> > Sent: Saturday, October 14, 2006 9:40 PM
> > To: mysql
> > Subject: change format of date fields during LOAD DATA INFILE?
> >
> > Is there a way to change the format of date fields MySQL is
> > expecting when
> > LOADing data from a file? I have no problem with the format
> > MySQL saves the
> > date but most spreadsheet programs I use don't make it easy
> > to export text
> > files with date fields in the format YYYY-MM-DD even if I
> > formated the field
> > that way on-screen.
> >
> > It would be great if you could tell MySQL on the command line
> > to expect
> > dates in the format Month/Day/Year or something like that and
> > be able to
> > interpret that and convert the date to the format it's
> > expecting on the fly.
> >
> > --
> > Ferindo
> >
>
>
>
>

#5 (**permalink**) 02-28-2008, 05:04 AM

At 21:39 -0400 10/14/06, Ferindo Middleton wrote:
>Is there a way to change the format of date fields MySQL is expecting when
>LOADing data from a file? I have no problem with the format MySQL saves the
>date but most spreadsheet programs I use don't make it easy to export text
>files with date fields in the format YYYY-MM-DD even if I formated the field
>that way on-screen.
>
>It would be great if you could tell MySQL on the command line to expect
>dates in the format Month/Day/Year or something like that and be able to
>interpret that and convert the date to the format it's expecting on the fly.

If you're using MySQL 5.0 or higher, you can read a column into a user
variable and use SET to reformat the column value before inserting it
into the table. Example:

LOAD DATA LOCAL INFILE 'newdata.txt'
INTO TABLE t (name,@date,value)
SET date = STR_TO_DATE(@date,'%m/%d/%y');

The format string depends on the format of your input data, of course.

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode