PHP Array to CSV

Question

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

EDIT

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

Use $info[]=$result->fetch_array(); otherwise it will have last product details — GBD, Oct 28 '12 at 10:56
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. — Gustavo Straube, Mar 16 '18 at 11:53
is anyone else getting the first line and first two blocks of second line blank?? — mach2, Oct 07 '18 at 16:44

score 115 · Accepted Answer · edited Jun 03 '21 at 10:56

115

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

edited Jun 03 '21 at 10:56

Erenor Paz

3,061
4
37
44

answered Oct 28 '12 at 10:48

Martin Lyne

3,157
2
22
28

1

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. – Martin Lyne Oct 28 '12 at 10:50
Thanks for edit Vyktor, my typing is awful today! I'm usually so careful. – Martin Lyne Oct 28 '12 at 11:08
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 – JohnnyFaldo Oct 28 '12 at 11:44
10

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; } – JohnnyFaldo Oct 28 '12 at 11:48
yes that solved it thank you, have added the solution in the original question – JohnnyFaldo Nov 03 '12 at 21:30

score 35 · Answer 2 · edited Apr 22 '21 at 19:12

35

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

edited Apr 22 '21 at 19:12

Rishabh Bhatnagar

551
4
12

answered Dec 21 '18 at 09:37

trank

952
11
8

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)); – dougB Sep 09 '22 at 02:58

score 5 · Answer 3 · edited May 23 '17 at 10:31

5

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?

edited May 23 '17 at 10:31

Community

1
1

answered Oct 28 '12 at 10:51

Martyn Shutt

1,671
18
25

score 3 · Answer 4 · edited Dec 11 '15 at 16:56

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 ;
}

score 2 · Answer 5 · answered Aug 08 '14 at 03:16

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.

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](https://stackoverflow.com/a/61447426/4829915). — LWC, Apr 26 '20 at 20:14

score 1 · Answer 6 · edited Feb 12 '18 at 06:44

1

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

edited Feb 12 '18 at 06:44

geisterfurz007

5,292
5
33
54

answered Feb 11 '18 at 17:53

avrahamm

79
6

1

A link to a solution is welcome, but please ensure your answer is useful without it: [add context around the link](//meta.stackexchange.com/a/8259) 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.](//stackoverflow.com/help/deleted-answers) – geisterfurz007 Feb 12 '18 at 06:43

score 1 · Answer 7 · answered May 16 '22 at 21:39

1

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

answered May 16 '22 at 21:39

Jay

19
1

2

This does not account for comma as value. – Doctore Jun 03 '22 at 12:00
This does not account for a lot of things. – 472084 Apr 18 '23 at 14:14

score 0 · Answer 8 · answered Jun 19 '20 at 08:40

It worked for me.

 $f=fopen('php://memory','w');
 $header=array("asdf ","asdf","asd","Calasdflee","Start Time","End Time" );      
 fputcsv($f,$header);
 fputcsv($f,$header);
 fputcsv($f,$header); 
 fseek($f,0);
 header('content-type:text/csv'); 
 header('Content-Disposition: attachment; filename="' . $filename . '";');
 fpassthru($f);```

score 0 · Answer 9 · answered Nov 07 '22 at 11:01

I use this simple function to create every single array entry as csv:

function arrayToCsv($fields, $delimiter = ",", $enclosure = "\"", $escapeChar = "\"")
{
    $fp  = fopen('php://temp', 'r+');
    fputcsv($fp, $fields, $delimiter, $enclosure, $escapeChar);
    rewind($fp);
    $ret = fgets($fp);
    fclose($fp);
    return $ret;
}

score 0 · Answer 10 · answered Nov 08 '22 at 19:29

You can try below code to export csv from array using fputcsv

ob_start();
$outputCsv = fopen('php://output', 'w');
fputcsv($outputCsv, ['column 1', 'column 2' 'column 3'], ",");
fputcsv($outputCsv, ['','',''], ",");

fputcsv($outputCsv, ['value 1', 'value 2' 'value 3'], ",");
fputcsv($outputCsv, ['value 11', 'value 21' 'value 31'], ",");
fputcsv($outputCsv, ['value 12', 'value 22' 'value 31'], ",");

header('Cache-Control: max-age=0');
header("Expires: 0");
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-type: application/csv');
header('Content-Disposition: attachment;filename="doc_logs.csv"');
header("Content-Transfer-Encoding: binary");
fpassthru($outputCsv);
fclose($outputCsv);

PHP Array to CSV

EDIT

10 Answers10

Linked

Related