[SOLVED] Generating delimited csv data with php

[c_shelswell]

Hi i'm having problem that i'm sure is very easy but i don't know the answer. I'm trying to create a csv file with php but some of the fields i'm creating contain commas already. I thought i had to add "$data", like that with the speech marks round it. But whatever i do it's not opening great in excel.

 

My code is below:

$q = "select * from main_site_languages limit 1";

$r = mysql_query($q) or die (mysql_error()); 
$headings = mysql_fetch_assoc($r);

$res = mysql_query("select * from main_site_languages");

$csv = "";

foreach ($headings as $col => $v)
{
	$csv .= strtoupper($col).",";
	$h[] = $col;
}

$csv = substr_replace($csv,"", -1);
$csv .= "\n";

while ($row = mysql_fetch_array($res, MYSQL_ASSOC))
{
	$csv .= '"'.$row[$h[0]].'","'.$row[$h[1]].'","'.$row[$h[2]].'","'.$row[$h[3]].'","'.$row[$h[4]].'","'.$row[$h[5]].'","'.$row[$h[6]].'","'
				.$row[$h[7]].'","'.$row[$h[8]].'","'.$row[$h[9]].'","'.$row[$h[10]].'","'.$row[$h[11]].'","'.$row[$h[12]].'","'.$row[$h[13]].'","'
				.$row[$h[14]].'","'.$row[$h[15]].'","'.$row[$h[16]].'","'.$row[$h[17]].'","'.$row[$h[18]].'","'.$row[$h[19]].'","'.$row[$h[20]].'","'
				.$row[$h[21]].'","'.$row[$h[22]]."\n";
}

header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="main_site_languages_' . date("Y-m-d") . '.csv"');
  	print $csv;
    exit;

 

any help would be great I'm racking my brains!! 

[micah1701]

it doesn't solve you're problem but instead of using commas to deliminate your columns, you could use Tabs.  instead of ","  use "\t"

 

 

[c_shelswell]

Cheers but that still shifts data to the next column the first time it encounters a comma in the field.

 

Surely I don't have to str_replace all commas in the data do i?

 

Cheers

[PFMaBiSmAd]

It would help if you give an example of what the data looks like and what the expected results should be and what the output is now.

[c_shelswell]

no worries this is just one line from the database:

(4, '[pro]To remove an item just click the ''REMOVE'' button. <br />This will remove one item only.', '[amt]Pour supprimer un article, cliquez sur le "Supprimer" bouton.<br /> Cela permettra d''éliminer un point seulement.', '[amt]So entfernen Sie einen Artikel klicken Sie einfach auf den "Entfernen"-Taste. <br />Dadurch wird nur ein Element.', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'show_cart.php', 'no'),

 

You can see the 3rd record has a comma in the field. Each $row is one of these fields.

 

Thanks again

[premiso]

Why not open excel add a few records with commas then export it as a csv and see how they handle commas. They may have an escape character you need to add.

 

[c_shelswell]

Premiso -that's a great idea i now feel like an idiot for not even thinking of that!! Cheers.

 

Seems they add double quotes round the data and if there's already a double quote in the data they quote that too ie "" so i think i'll use a str_replace on it.

 

Cheers

[premiso]

Premiso -that's a great idea

 

I have my days

[PFMaBiSmAd]

Temporarily comment out the two header() statements so that you can see what $csv contains. At a minimum, you are missing a leading " on the header line that will throw everything off. You are also missing a " at the end of each data line, before the \n.

[c_shelswell]

Got the " on the data cheers PFMaBiSAd but I can't see where you mean the leading " on the header line?

 

Cheers

[PFMaBiSmAd]

I'm pretty sure the posted code gives this now -

 

heading1","heading2","heading3...

 

It should be -

 

"heading1","heading2","heading3...

[c_shelswell]

Cheers have eventually got it working!

 

Thanks for all your help