mysql LOAD DATA INFILE generates dummy data

Question

@OpenCart 1.5.5 I'm trying to export some rows from certain tables from source DB using this command, for example:

mysql_query("
  SELECT `group`,`key`,`value`,`serialized` 
  INTO OUTFILE '".$outputDir ."/".$dbPrefix ."setting.csv' FIELDS TERMINATED BY ',' 
  OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM `".$dbPrefix."setting` 
  WHERE `group` = 'banner' OR `group` = 'bestseller' 
  OR `group` = 'carousel' OR ` .....
")

After I delete these tables I try to import this file to the target DB using the following command:

mysql_query("
  LOAD DATA INFILE '". $outputDir ."/". $dbPrefix ."setting.csv'   
  INTO TABLE `".$tDbPrefix."setting` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED 
  BY '\"' LINES TERMINATED BY '\n'
")

The problem is it generates dummy data with weird formatting; for example:

 <li class="contact-2 cle"...

I use UTF-8 encoding and databases are identical.

Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide. — Jay Blanchard, Nov 05 '14 at 13:43

score 0 · Answer 1 · answered Nov 05 '14 at 14:15

i found out the problem ... i had to define the tables i'm importing the right syntax is

mysql_query("LOAD DATA INFILE '". $outputDir ."/". $dbPrefix ."setting.csv'   INTO TABLE `".$tDbPrefix."setting` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' (`group`,`key`,`value`)");

thats why it used to generate dummy data and by defining the imported tables it works all fine

mysql LOAD DATA INFILE generates dummy data

1 Answers1