Re: change format of date fields during LOAD DATA INFILE?
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 -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/[EMAIL PROTECTED]
RE: change format of date fields during LOAD DATA INFILE?
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:[EMAIL PROTECTED] 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 -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/[EMAIL PROTECTED]
Re: change format of date fields during LOAD DATA INFILE?
I was using OpenOffice... And I couldn't get it to keep the format -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 [EMAIL PROTECTED] 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:[EMAIL PROTECTED] ] 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 -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
Re: change format of date fields during LOAD DATA INFILE?
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 -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 '-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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
