PHP array for CSV

Question

I am trying to convert an array of products to a CSV file, but it seems not 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; } 

In addition, the header does not force downloads. I copied and pasted the output and saved as .csv

EDIT

SOLVED PROBLEM:

If someone else was looking for the same, found a better way to do this:

 $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"); 

Answer 1

Instead of writing values, use fputcsv() .

This can solve your problem immediately.

Answer 2

Try using

 PHP_EOL 

To interrupt each new line at the output of the CSV.

I suppose the text is split but not going to the next line?

This is a constant PHP. It will determine the correct end of the line you need.

Windows, for example, uses "\ r \ n". I made out my brains with this when my result did not break on a new line.

How to write a single new line in PHP?

Answer 3

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

 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 did not lend themselves to a flat CSV format, I created an empty column with the sub-array key to serve as a descriptive “entry” to the next level of data. Output Example:

 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 can easily remove this "intro" (descriptive) column, but in my case I repeated the column headers, i.e. inbound_leads, in each submatrix, so it gave me a break / title preceding the next section. Delete:

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

after is_array () combines the code even more and removes the extra column.

Since I wanted both the header line and the data line, I pass the two variables to the function and upon completion of the function call I end both using PHP_EOL:

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

Yes, I know that I leave an extra comma, but for brevity, I could not handle it here.

Answer 4

I know this is old, I had a case when I needed an array key to include in the CSV, so I updated the Jesse Q script to do this. I used the line as output, since implode cannot add a new line (a new line is what I added, and should really be there).

Note that this only works with single value arrays (key, value) . but can be easily updated to handle multidimensional (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 ; } 

Answer 5

Arrays of data are converted to csv 'text / csv' format using the built-in php function. Fputcsv takes care of commas, quotation marks, etc.
look at
https://coderwall.com/p/zvzwwa/array-to-comma-separated-string-in-php
http://www.php.net/manual/en/function.fputcsv.php

Answer 6

This is a simple solution that exports an array to a 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)); 

Answer 7

I use this function to convert php array to csv. It also works for a multidimensional array.

 public function array_2_csv($array) { $csv = array(); foreach ($array as $item=>$val) { if (is_array($val)) { $csv[] = $this->array_2_csv($val); $csv[] = "\n"; } else { $csv[] = $val; } } return implode(';', $csv); }