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

Macmacabre

Use $info[]=$result->fetch_array(); otherwise it will have last product details – Faddish

@JohnnyFaldo: Thanks man! Just what I needed it. – Interne

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. – Slub

is anyone else getting the first line and first two blocks of second line blank?? – Soupy

Answer 1

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

Lenzi

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. – Lenzi

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

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 – Macmacabre

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

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

Answer 2

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

Falconet

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

Answer 3

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?

Edentate

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

Superbomb

Answer 5

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.

Zoogloea

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. – Chobot

Answer 6

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

Donative

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. – Oleaceous

Answer 7

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

Keri

This does not account for comma as value. – Acrobatics

This does not account for a lot of things. – Rations

Answer 8

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

Ecumenicist

Answer 9

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

Bismuthic

Answer 10

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

Cromer