MySQL: INTO OUTFILE in csv generates invalid csv format

Question

I am trying to export sql query result to csv file it works but the out csv data does not look good and strange.

MySQL Table Download Link: https://www.dropbox.com/s/vtr215bcxqo3wsy/data.sql?dl=0

CSV Generated by sql query:

Download Link to original Generated CSV File: https://www.dropbox.com/s/fnjf7ycmh08hd22/data.csv?dl=0

I am using following code:

$query = <<<EOL
 SELECT * FROM data  ORDER BY  FN ASC limit 3 
 INTO OUTFILE 'folder/data.csv'
 FIELDS TERMINATED BY ','
 OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
EOL;

$result = mysql_query($query);

Why csv format is looking so weird and unacceptable? If I try same code for some other table then everything works like charm then what's wrong?

Answer 1

See final answer below

It looks like your lines are terminated by \\n and it is throwing the extra slashes in random places.

Instead try a double slash followed by an n (\\n) and see what happens:

$query = <<<EOL
 SELECT * FROM data  ORDER BY  FN ASC limit 3 
 INTO OUTFILE 'folder/data.csv'
 FIELDS TERMINATED BY ','
 OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\\n'
EOL;

$result = mysql_query($query);

EDIT

Final Answer

Another observation: I noticed that in your PROP_TYPE field, there are \r\n characters. Is there any way you can filter them out in your query using the REPLACE() function?

I know you are looking for a solution that is SQL based, and this is a hard issue because of the massive amount of data. Hope this leads you to the correct solution.

As you mentioned, using update data set PROP_TYPE = replace(PROP_TYPE, '"','') fixed the issue.

Answer 2

Consider simply using PHP to connect to MySQL, run query, then output to csv.

<?php

$host="localhost";
$username="user";
$password="password";
$database="dbName";

# open connection
try {
    $dbh = new PDO("mysql:host=$host;dbname=$database",$username,$password);
}
catch(PDOException $e) {  
    echo $e->getMessage();  
}
$sql = "SELECT * FROM data ORDER BY FN ASC limit 3;";
$STH = $dbh->query($sql);    

$STH->setFetchMode(PDO::FETCH_ASSOC);  

while($row = $STH->fetch()) {
    # write to csv file
    $fs = fopen("folder/data.csv","a");
        fputcsv($fs, $row);
    fclose($fs);    
}

# close connection
$dbh = null;

?>

Answer 3

Finally I fixed my issue.

Actually @Terry is right. There was some issue with a field PROP_TYPE in table. PROP_TYPE field had double quotes " in its values that was causing issue. For example

PROP_TYPE

"Value 1"

"Value 2" ....

So first of all I had to remove extra double quotes using update data set PROP_TYPE = replace(PROP_TYPE, '"','') so now my issue is fixed.

Thanks all of you for your efforts.

I really appreciate.