1

I got a nice snippet that runs a query on a table and prints it into an csv format. I've got just one problem with that snippet: Some of the data in the rows is multi line and not a single word.

Now what happening is that the export looks like that:

Msg Name Phone

Message just goes over name and phone and not 'limited' only to message My Name 1800080808

(I want the value of 'msg' to be inside the msg cube and not go all over name and phone.)

Do you have any idea how could I set the whole phrase of a value inside one box?

Snippet:

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;

if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field'].";";
$i++;}
}
$csv_output .= "\n";
$values = mysql_query("SELECT * FROM ".$table."");

while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j].";";
}
$csv_output .= "\n";
}

$filename = $file."_".date("d-m-Y_H-i",time());

header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");

print $csv_output;

Thanks in advance!

hakre
  • 178,314
  • 47
  • 389
  • 754
eek
  • 105
  • 1
  • 9

3 Answers3

1

You could replace all the new lines for a space in your for loop:

$csv_output .= str_replace("\n", " ", $rowr[$j]).";";
Elias Dorneles
  • 19,202
  • 9
  • 63
  • 95
  • I'm not sure if you understood me: if a value is more then one word, he just goes over other 'fields', like there is no actual limiter for each field. – eek Dec 21 '11 at 19:09
  • sorry! well, you wrote "multi line"... you can just delimit them with quotes, then. You shouldn't neet to, though. When you open the CSV file in the spreadsheet application, you should be able to change the delimiters as needed. – Elias Dorneles Dec 21 '11 at 19:13
  • hmm... for me, OpenOffice opens a dialog when opening a CSV, allowing to choose the separator and text delimiters... don't know about other apps, though. check out wikipedia's article on CSV: http://en.wikipedia.org/wiki/Comma-separated_values – Elias Dorneles Dec 21 '11 at 19:28
1

If you need to preserve new lines you can replace them with html line breaks via nl2br or escaped characters (best approach IMO).

$value = nl2br($value);

// or...

$value = str_replace("\r", '', str_replace("\n", "\\n", $value));
John Himmelman
  • 20,084
  • 20
  • 61
  • 79
  • Sorry for not being clear, the problem is not the new-lines, the problem is that if a value is not only a single word there is nothing that limits him into one cube, like all the values is spread with not delimiters, for example if msg is more then one word, I want it to stay in the 'msg' cube and not go over. – eek Dec 21 '11 at 19:21
  • @eek So you want all the values / columns to be aligned, right? – John Himmelman Dec 21 '11 at 19:30
1

You should be able to do this directly in MySQL

Check out this post on SO: How to output MySQL query results in CSV format?

Which references this article: http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/

Hope this helps.

If you prefer to go the other route, just make sure you str_replace() anything inside of a field that is the same as your delimiter, and also str_replace() any characters that have meaning in a csv file, \n and \r etc...

Community
  • 1
  • 1
Matisse VerDuyn
  • 1,128
  • 15
  • 39
  • Sorry for not being clear, the problem is not the new-lines, the problem is that if a value is not only a single word there is nothing that limits him into one cube, like all the values is spread with not delimiters, for example if msg is more then one word, I want it to stay in the 'msg' cube and not go over. – eek Dec 21 '11 at 19:20