CODEWITHSUNDEEP
phpcsv
JohnnyFaldo
JohnnyFaldo

Reputation: 4161

PHP Array to CSV

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

Upvotes: 132

Views: 295396

Answers (11)

MiMFa
MiMFa

Reputation: 1174

Try this solution, to save an array of everything into Comma Separated Values (CSV), Tab Separated Values, etc. files:

function ToSeparatedValuesFile($cells, $path = null, $delimiter = ',', $enclosure = '"', $eol = "\n") :string {
    $path = $path;
    $fstream = fopen($path, 'r+b');
    foreach ($cells as $fields) fputcsv($fstream, $fields, $delimiter, $enclosure, "\\", $eol);
    fclose($fstream);
    return $path;
}

You need to call ToSeparatedValuesFile function and enjoy...

ToSeparatedValuesFile($data, $path);

Reference

Enjoy...

Upvotes: 0

smdhkv
smdhkv

Reputation: 187

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

Upvotes: 0

Marco
Marco

Reputation: 3651

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

Upvotes: 0

Jay
Jay

Reputation: 19

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

Upvotes: 1

Martin Lyne
Martin Lyne

Reputation: 3065

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

Upvotes: 115

trank
trank

Reputation: 1022

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

Upvotes: 39

Amir Khan
Amir Khan

Reputation: 221

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

Upvotes: 0

avrahamm
avrahamm

Reputation: 109

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

Upvotes: 1

J nui
J nui

Reputation: 190

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

Upvotes: 3

Jesse Q
Jesse Q

Reputation: 1671

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.

Upvotes: 2

Martyn Shutt
Martyn Shutt

Reputation: 1706

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?

Upvotes: 5

Related Questions