Convert array to CSV and force download

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

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

Erenor Paz · Accepted Answer · 2021-06-03 10:56:54Z

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

edited Jun 3, 2021 at 10:56

Erenor Paz

3,1915 gold badges40 silver badges48 bronze badges

answered Oct 28, 2012 at 10:48

Martin Lyne

3,0952 gold badges24 silver badges29 bronze badges

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

5 Comments

Martin Lyne Over a year ago

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 Over a year ago

Thanks for edit Vyktor, my typing is awful today! I'm usually so careful.

JohnnyFaldo Over a year ago

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 Over a year ago

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 Over a year ago

yes that solved it thank you, have added the solution in the original question

Rishabh Bhatnagar · Accepted Answer · 2021-04-22 19:12:51Z

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

edited Apr 22, 2021 at 19:12

Rishabh Bhatnagar

6306 silver badges13 bronze badges

answered Dec 21, 2018 at 9:37

trank

1,06212 silver badges8 bronze badges

1 Comment

dougB Over a year ago

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

Community · Accepted Answer · 2017-05-23 10:31:10Z

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?

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Oct 28, 2012 at 10:51

Martyn Shutt

1,71418 silver badges26 bronze badges

Comments

vard · Accepted Answer · 2015-12-11 16:56:01Z

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

Jesse Q · Accepted Answer · 2014-08-08 03:16:10Z

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.

answered Aug 8, 2014 at 3:16

Jesse Q

1,7113 gold badges14 silver badges22 bronze badges

1 Comment

LWC Over a year ago

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.

geisterfurz007 · Accepted Answer · 2018-02-12 06:44:19Z

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

edited Feb 12, 2018 at 6:44

geisterfurz007

5,9246 gold badges37 silver badges59 bronze badges

answered Feb 11, 2018 at 17:53

avrahamm

1071 silver badge6 bronze badges

1 Comment

geisterfurz007 Over a year ago

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.

Jay · Accepted Answer · 2022-05-16 21:39:28Z

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

answered May 16, 2022 at 21:39

Jay

171 bronze badge

2 Comments

Doctore Over a year ago

This does not account for comma as value.

472084 Over a year ago

This does not account for a lot of things.

miken32 · Accepted Answer · 2025-08-01 18:50:20Z

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

Collectives™ on Stack Overflow

Convert array to CSV and force download

8 Answers 8

5 Comments

1 Comment

Comments

Comments

1 Comment

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

5 Comments

1 Comment

Comments

Comments

1 Comment

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related