133

I'm trying to convert an array of products into a CSV file, but it doesn't seem to be going to plan. The CSV file is one long line, here is my code:

for($i=0;$i<count($prods);$i++) { $sql = "SELECT * FROM products WHERE id = '".$prods[$i]."'"; $result = $mysqli->query($sql); $info = $result->fetch_array(); } $header = ''; for($i=0;$i<count($info);$i++) { $row = $info[$i]; $line = ''; for($b=0;$b<count($row);$b++) { $value = $row[$b]; if ( ( !isset( $value ) ) || ( $value == "" ) ) { $value = "\t"; } else { $value = str_replace( '"' , '""' , $value ); $value = '"' . $value . '"' . "\t"; } $line .= $value; } $data .= trim( $line ) . "\n"; } $data = str_replace( "\r" , "" , $data ); if ( $data == "" ) { $data = "\n(0) Records Found!\n"; } header("Content-type: application/octet-stream"); header("Content-Disposition: attachment; filename=your_desired_name.xls"); header("Pragma: no-cache"); header("Expires: 0"); array_to_CSV($data); function array_to_CSV($data) { $outstream = fopen("php://output", 'r+'); fputcsv($outstream, $data, ',', '"'); rewind($outstream); $csv = fgets($outstream); fclose($outstream); return $csv; }

Also, the header doesn't force a download. I've been copy and pasting the output and saving as .csv

Improve this question
4
  • Use $info[]=$result->fetch_array(); otherwise it will have last product details Commented Oct 28, 2012 at 10:56
  • @JohnnyFaldo: Thanks man! Just what I needed it. Commented Mar 27, 2013 at 19:48
  • Hey! I know this is an old question, but you could answer your own question. It may help people on finding a solution for a similar problem. Commented Mar 16, 2018 at 11:53
  • is anyone else getting the first line and first two blocks of second line blank?? Commented Oct 7, 2018 at 16:44

8 Answers 8

Reset to default
118

Instead of writing out values consider using fputcsv().

This may solve your problem immediately.

Note from comment: I should mention that this will be making a file on your server, so you'll need to read that file's contents before outputting it, also if you don't want to save a copy then you'll need to ùnlink`the file when you are done

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I should mention that this will be making a file on your server, so you'll need to read that file's contents before outputting it, also if you don't want to save a copy then you'll need to ùnlink`the file when you are done.
Thanks for edit Vyktor, my typing is awful today! I'm usually so careful.
could i edit the code I have above to do this? as it's outputting all the fields, and using examples of fputcsv in the manual I can't get it to do this
array_to_CSV($data); function array_to_CSV($data) { $outstream = fopen("php://output", 'r+'); fputcsv($outstream, $data, ',', '"'); rewind($outstream); $csv = fgets($outstream); fclose($outstream); return $csv; }
yes that solved it thank you, have added the solution in the original question
41

This is a simple solution that exports an array to csv string:

function array2csv($data, $delimiter = ',', $enclosure = '"', $escape_char = "\\") { $f = fopen('php://memory', 'r+'); foreach ($data as $item) { fputcsv($f, $item, $delimiter, $enclosure, $escape_char); } rewind($f); return stream_get_contents($f); } $list = array ( array('aaa', 'bbb', 'ccc', 'dddd'), array('123', '456', '789'), array('"aaa"', '"bbb"') ); var_dump(array2csv($list));

Reference

Improve this answer

1 Comment

Brilliant solution! I made one slight adjustment to write a single array to single CSV string: $f = fopen('php://memory', 'w+'); fputcsv($f, $data); rewind($f); return trim(stream_get_contents($f));
4

Try using;

PHP_EOL

To terminate each new line in your CSV output.

I'm assuming that the text is delimiting, but isn't moving to the next row?

That's a PHP constant. It will determine the correct end of line you need.

Windows, for example, uses "\r\n". I wracked my brains with that one when my output wasn't breaking to a new line.

how to write unified new line in PHP?

Improve this answer

Comments

4

I know this is old, I had a case where I needed the array key to be included in the CSV also, so I updated the script by Jesse Q to do that. I used a string as output, as implode can't add new line (new line is something I added, and should really be there).

Please note, this only works with single value arrays (key, value). but could easily be updated to handle multi-dimensional (key, array()).

function arrayToCsv( array &$fields, $delimiter = ',', $enclosure = '"', $encloseAll = false, $nullToMysqlNull = false ) { $delimiter_esc = preg_quote($delimiter, '/'); $enclosure_esc = preg_quote($enclosure, '/'); $output = ''; foreach ( $fields as $key => $field ) { if ($field === null && $nullToMysqlNull) { $output = ''; continue; } // Enclose fields containing $delimiter, $enclosure or whitespace if ( $encloseAll || preg_match( "/(?:${delimiter_esc}|${enclosure_esc}|\s)/", $field ) ) { $output .= $key; $output .= $delimiter; $output .= $enclosure . str_replace($enclosure, $enclosure . $enclosure, $field) . $enclosure; $output .= PHP_EOL; } else { $output .= $key; $output .= $delimiter; $output .= $field; $output .= PHP_EOL; } } return $output ; }
Improve this answer

Comments

2

In my case, my array was multidimensional, potentially with arrays as values. So I created this recursive function to blow apart the array completely:

function array2csv($array, &$title, &$data) { foreach($array as $key => $value) { if(is_array($value)) { $title .= $key . ","; $data .= "" . ","; array2csv($value, $title, $data); } else { $title .= $key . ","; $data .= '"' . $value . '",'; } } }

Since the various levels of my array didn't lend themselves well to a the flat CSV format, I created a blank column with the sub-array's key to serve as a descriptive "intro" to the next level of data. Sample output:

agentid fname lname empid totals sales leads dish dishnet top200_plus top120 latino base_packages G-adriana ADRIANA EUGENIA PALOMO PAIZ 886 0 19 0 0 0 0 0

You could easily remove that "intro" (descriptive) column, but in my case I had repeating column headers, i.e. inbound_leads, in each sub-array, so that gave me a break/title preceding the next section. Remove:

$title .= $key . ","; $data .= "" . ",";

after the is_array() to compact the code further and remove the extra column.

Since I wanted both a title row and data row, I pass two variables into the function and upon completion of the call to the function, terminate both with PHP_EOL:

$title .= PHP_EOL; $data .= PHP_EOL;

Yes, I know I leave an extra comma, but for the sake of brevity, I didn't handle it here.

Improve this answer

1 Comment

This seems like a specific answer to a very general question and you admittedly left over extra commas (due to the real issue of using strings instead of arrays) as well as didn't quote the header line and had some extra unneeded calls. I reshaped all of this inside an answer to another question.
0

Arrays of data are converted into csv 'text/csv' format by built in php function fputcsv takes care of commas, quotes and etc..
Look at
https://coderwall.com/p/zvzwwa/array-to-comma-separated-string-in-php
http://www.php.net/manual/en/function.fputcsv.php

Improve this answer

1 Comment

A link to a solution is welcome, but please ensure your answer is useful without it: add context around the link so your fellow users will have some idea what it is and why it’s there, then quote the most relevant part of the page you're linking to in case the target page is unavailable. Answers that are little more than a link may be deleted.
0

The easiest way to create csv file from an array is to use implode() function:

<?php $arr = array('A','B','C','D'); echo implode(",",$arr); ?>

The output of the above code will give: A,B,C,D

Improve this answer

2 Comments

This does not account for comma as value.
This does not account for a lot of things.
0

Originally posted as an edit to the question

PROBLEM RESOLVED:

If anyone else was looking for the same thing, found a better way of doing it:

$num = 0; $sql = "SELECT id, name, description FROM products"; if($result = $mysqli->query($sql)) { while($p = $result->fetch_array()) { $prod[$num]['id'] = $p['id']; $prod[$num]['name'] = $p['name']; $prod[$num]['description'] = $p['description']; $num++; } } $output = fopen("php://output",'w') or die("Can't open php://output"); header("Content-Type:application/csv"); header("Content-Disposition:attachment;filename=pressurecsv.csv"); fputcsv($output, array('id','name','description')); foreach($prod as $product) { fputcsv($output, $product); } fclose($output) or die("Can't close php://output");
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.