c_shelswell Posted February 26, 2009 Share Posted February 26, 2009 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!! Link to comment https://forums.phpfreaks.com/topic/147023-solved-generating-delimited-csv-data-with-php/ Share on other sites More sharing options...
micah1701 Posted February 26, 2009 Share Posted February 26, 2009 it doesn't solve you're problem but instead of using commas to deliminate your columns, you could use Tabs. instead of "," use "\t" Link to comment https://forums.phpfreaks.com/topic/147023-solved-generating-delimited-csv-data-with-php/#findComment-771852 Share on other sites More sharing options...
c_shelswell Posted February 26, 2009 Author Share Posted February 26, 2009 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 Link to comment https://forums.phpfreaks.com/topic/147023-solved-generating-delimited-csv-data-with-php/#findComment-771863 Share on other sites More sharing options...
PFMaBiSmAd Posted February 26, 2009 Share Posted February 26, 2009 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. Link to comment https://forums.phpfreaks.com/topic/147023-solved-generating-delimited-csv-data-with-php/#findComment-771867 Share on other sites More sharing options...
c_shelswell Posted February 26, 2009 Author Share Posted February 26, 2009 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 Link to comment https://forums.phpfreaks.com/topic/147023-solved-generating-delimited-csv-data-with-php/#findComment-771871 Share on other sites More sharing options...
premiso Posted February 26, 2009 Share Posted February 26, 2009 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. Link to comment https://forums.phpfreaks.com/topic/147023-solved-generating-delimited-csv-data-with-php/#findComment-771877 Share on other sites More sharing options...
c_shelswell Posted February 26, 2009 Author Share Posted February 26, 2009 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 Link to comment https://forums.phpfreaks.com/topic/147023-solved-generating-delimited-csv-data-with-php/#findComment-771890 Share on other sites More sharing options...
premiso Posted February 26, 2009 Share Posted February 26, 2009 Premiso -that's a great idea I have my days Link to comment https://forums.phpfreaks.com/topic/147023-solved-generating-delimited-csv-data-with-php/#findComment-771893 Share on other sites More sharing options...
PFMaBiSmAd Posted February 26, 2009 Share Posted February 26, 2009 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. Link to comment https://forums.phpfreaks.com/topic/147023-solved-generating-delimited-csv-data-with-php/#findComment-771900 Share on other sites More sharing options...
c_shelswell Posted February 26, 2009 Author Share Posted February 26, 2009 Got the " on the data cheers PFMaBiSAd but I can't see where you mean the leading " on the header line? Cheers Link to comment https://forums.phpfreaks.com/topic/147023-solved-generating-delimited-csv-data-with-php/#findComment-771911 Share on other sites More sharing options...
PFMaBiSmAd Posted February 26, 2009 Share Posted February 26, 2009 I'm pretty sure the posted code gives this now - heading1","heading2","heading3... It should be - "heading1","heading2","heading3... Link to comment https://forums.phpfreaks.com/topic/147023-solved-generating-delimited-csv-data-with-php/#findComment-771924 Share on other sites More sharing options...
c_shelswell Posted February 26, 2009 Author Share Posted February 26, 2009 Cheers have eventually got it working! Thanks for all your help Link to comment https://forums.phpfreaks.com/topic/147023-solved-generating-delimited-csv-data-with-php/#findComment-771935 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.