RE: Regarding the date values loading into the tables from a text file
Hi, From the front end I need to use double slashes i.e (\\) to enter one slash (\) into the MySQL database. I.e, if I enter Gelb\Paha, it stores in the mySQL as GlebPaha, If I enter S\\Greeen, it stores as S\Green in the database. Is there any way in MySQL so that I can enter any number of slases between the name with out escaping with another slash?. If I retrive the same value with the slash (\), not able to display properly in the GUI. Do we need to use any MySQL specific functions to select such values?. Please guide me for a solution here. Thanking you in advance. Thanks, Narasimha The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regarding the date values loading into the tables from a text file
[EMAIL PROTECTED] wrote on 01/16/2006 08:22:00 AM: Hi, I am trying to load the data from a text file into the table. I have two datetime datatype fileds in my table. When I loaded the data into the table using the Load Data INFILE syntax, In the table my date fileds values are loaded as -00-00 00:00:00. The datetime value in the text file is in mm/dd/ hh:mm:ss format and the datetime format in MySQL is in /mm/dd hh:mm:ss format by default. Due to the above difference in the format of datetime, I am getting the loaded datetime value in the table as -00-00 00:00:00. Could any one of you please suggest me for a solution to load the correct datetime values from the text file into the tables in the above scenario. Thanking you in advance. Thanks, Narasimha Use an intermediate table as the destination of your text file input. Create your intermediate table with mostly varchar columns. Especially make your incoming date or datetime fields varchar. Then you do a bulk insert from that table to your original destination table using the STR_TO_DATE() function to convert your mm/dd/yy-type dates into valid MySQL date values. http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html I have almost never been able to perform a direct import from a text file into a production table. Using an intermediate table is just one of those things you should get used to doing when you import data from text files. It not only allows you to correct for formatting differences (like the problem you report) but also to correct for invalid data (a process also known as scrubbing). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
