Hi! If I don't put the tab before the the end of the line in the txt file that contains the data, mySQL does not properly put the data into table using LOAD DATA INFILE. The courious thing is that this only happens with this one file. I was using the same commands for all my other files and this situation has never happened. All the data and file names are true.
I use: version 4.0.1-alpha-nt Microsoft Windows 2000 [Version 5.00.2195] I have the file: d:\Users\APodhorski\DataNash\DB\Tigr\MGI\cmd.sql that contains: USE datanash; DROP TABLE IF EXISTS Tigr_MGI_TCs; CREATE TABLE Tigr_MGI_TCs ( ID int NOT NULL AUTO_INCREMENT, TC varchar( 8 ) NULL, TC_Synonym varchar( 20 ) NULL, PRIMARY KEY ( ID ) ); LOAD DATA INFILE 'd:/Users/APodhorski/DataNash/DB/Tigr/MGI/PreScan-MGI.TCs.tmp' INTO TABLE Tigr_MGI_TCs; Than I execute it using: type cmd.sql | "d:/Users/APodhorski/mysql/bin/mysql.exe" The truth is that I have a perl script that does all this, but I was able to reproduce the problem using Windows command line directly. If there are tabs before the end of the line (e.g. 1\tTC446696\tTC27867\t\n) I got everything OK mysql> SELECT * FROM tigr_mgi_tcs; +----+----------+------------+ | ID | TC | TC_Synonym | +----+----------+------------+ | 1 | TC446696 | TC27867 | | 2 | TC446696 | TC37198 | | 3 | TC446696 | TC39544 | | 4 | TC446696 | TC70404 | +----+----------+------------+ But if there is only a \n at the end of the line (e.g. 1\tTC446696\tTC27867\n), the data gets corrupted and even the display does not proerly work (note the wrong vertical column bars for display) mysql> SELECT * FROM tigr_mgi_tcs; +----+----------+------------+ | ID | TC | TC_Synonym | +----+----------+------------+ | | TC446696 | TC27867 | | TC446696 | TC37198 | | TC446696 | TC39544 | | TC446696 | TC70404 +----+----------+------------+ Below is what the txt data file contains. If you remove the tabs than the trouble occurs. 1 TC446696 TC446696 2 TC446696 TC27867 3 TC446696 TC37198 4 TC446696 TC39544 Regards Adam -- Dr Adam Podhorski CEIT, Centro de Estudios e Investigaciones Técnicas de Gipuzkoa Paseo de Manuel Lardizábal, 15 20018 San Sebastian Spain Tel.: (34) 943 212 800 Fax.: (34) 943 213 076 email: [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
