Re: Is there a way to load non-native Date type with LOAD DATA
Use a command line tool to change the date. I am only familiar with perl and it would be a very short script to change to -MM-DD. At 10:48 PM 9/2/2006, David Perron wrote: I have a pretty large file with a Date column in the format M/D/. Is there a way to either change the Date data type in the table or a method to indicate the date format in the LOAD DATA statement in order to handle this? Thanks in advance for any help! David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Chris Jones 14 Oneida Avenue Toronto, ON M5J 2E3. Tel. 416-203-7465 Fax. 416-946-1005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there a way to load non-native Date type with LOAD DATA
David Perron wrote: I have a pretty large file with a Date column in the format M/D/. Is there a way to either change the Date data type in the table or a method to indicate the date format in the LOAD DATA statement in order to handle this? in VI the following should work depending on the other data in the file :%s/ \(\d\d\)\/\(\d\d\)\/\(\d\d\d\d\)/ \3-\1-\2/ :%s/ \(\d\d\)\/\(\d\)\/\(\d\d\d\d\)/ \3-\1-0\2/ :%s/ \(\d\)\/\(\d\d\)\/\(\d\d\d\d\)/ \3-0\1-\2/ :%s/ \(\d\)\/\(\d\)\/\(\d\d\d\d\)/ \3-0\1-0\2/ this only works if there is a space in front of the dates. If the date is the first thing on the line, replace the first space on each line with a ^ and remove the second space. If the date is quoted, replace both spaces on each line with a quote. If the date is preceded by just a tab, replace both spaces on each line with a \t -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there a way to load non-native Date type with LOAD DATA
I have a pretty large file with a Date column in the format M/D/. Is there a way to either change the Date data type in the table or a method to indicate the date format in the LOAD DATA statement in order to handle this? If you are using MySQL 5.0.3 or greater, you should be able to transform your existing string date datq as you use LOAD DATA statement... Read up on the syntax for utilizing column lists, user variables and a SET clause in conjunction with LOAD DATA --- http://dev.mysql.com/doc/refman/5.0/en/load-data.html Then the you can use the SET clause to transform your existing string date data into MySQL's date type using the STR_TO_DATE(str,format) function --- http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html Basically what you will need to do is specify the column list in your LOAD DATA statement but for your string date data you will want to substitute a user variable, then include that user variable in your SET clause inside the STR_TO_DATE function with the relevant format. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is there a way to load non-native Date type with LOAD DATA
I have a pretty large file with a Date column in the format M/D/. Is there a way to either change the Date data type in the table or a method to indicate the date format in the LOAD DATA statement in order to handle this? Thanks in advance for any help! David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
