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!! Quote 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" Quote 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 Quote 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. Quote 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 Quote 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. Quote 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 Quote 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 Quote 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. Quote 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 Quote 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... Quote 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 Quote 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
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.