1

I am trying to export my MySQL db to Excel. I wrote a PHP script to format a txt file as:

$res = mysql_query("select Data1, Data2 FROM my Table");
while ($row = mysql_fetch_assoc($res)) {
 // Remove tabs chars
 $row["Data1"] = str_replace("\t","", $row["Data1"]);
 $row["Data2"] = str_replace("\t","", $row["Data2"]);
 // Output to file
 echo "\"" . $row["Data1"] . "\" \t \"" . $row["Data2"] . "\"\n";
}

I copy the output and save it as a txt file, which I then import to Excel using "tab" as the delimiter. However, since both Data1 and Data2 fields contain newlines chars it seems like Excel treats it like a new cell instead of creating a new line in the current cell.

Anyway I can import the file so both fields will have new lines and still be on one cell?

EDIT: Even when trying to import this simple text file:

"hello","bye
now"

Excel creates a row with two columns: "hello" , "bye" - and then a new row with "now"

Thanks!

Joel

bluish
  • 26,356
  • 27
  • 122
  • 180
Joel
  • 5,949
  • 12
  • 42
  • 58
  • Take a look at [these responses](http://stackoverflow.com/questions/1241220/generating-csv-file-for-excel-how-to-have-a-newline-inside-a-value) to a similar question – Mark Baker Feb 10 '11 at 09:51

1 Answers1

0

Why don't you use pure SQL?

like

SELECT Data1, Data2
FROM mytable
INTO OUTFILE '/tmp/export.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
Bill the Lizard
  • 398,270
  • 210
  • 566
  • 880
Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
  • I am not using this since I am manipulating the Data fields before outputting them (did not include it in the example because it is long). The real problem is that Excel is not importing the new lines properly (same will happen with your suggestion). But thanks for that info, I was not aware it was possible with MySQL :) – Joel Feb 10 '11 at 09:29
  • This doesn't help a bit to solve the question. Not a real answer. – dxvargas Apr 26 '12 at 08:46