Sunday, October 19, 2008

MySql LOAD DATA LOCAL INFILE Problem

If you upload data without line terminator in the file, Data will upload perfectly but you compare field with query will not give result. particularly with last field and local file created with Excel->CSV. Data in the Mysql Table shows exact but \r\n not there so it will not compare and you will not get result of sql query.

If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINES TERMINATED BY '\r'.

Php Example:
$sql = "LOAD DATA LOCAL INFILE '".@mysql_escape_string($this->file_name).
"' INTO TABLE `".$this->table_name.
"` FIELDS TERMINATED BY '".",' LINES TERMINATED BY "."'"."\\r\\n"."' IGNORE 3 LINES";

No comments: