change format of date fields during LOAD DATA INFILE?

change format of date fields during LOAD DATA INFILE?

am 15.10.2006 03:39:54 von Ferindo Middleton

------=_Part_77147_1788869.1160876394277
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_77147_1788869.1160876394277--

Re: change format of date fields during LOAD DATA INFILE?

am 15.10.2006 19:59:46 von mos

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. 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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

RE: change format of date fields during LOAD DATA INFILE?

am 16.10.2006 17:14:14 von Jerry Schwartz

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
>




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: change format of date fields during LOAD DATA INFILE?

am 16.10.2006 18:52:18 von Ferindo Middleton

------=_Part_94105_26806077.1161017538560
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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 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
> >
>
>
>
>

------=_Part_94105_26806077.1161017538560--

Re: change format of date fields during LOAD DATA INFILE?

am 25.10.2006 15:41:19 von Paul DuBois

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org