Export to CSV file using PHP

Question

I am using this code to export selected data from MySQL to CSV using PHP, I am facing a little problem that when data exported to the csv file it looks like below:

First line in its correct place, but starting from the second row data shifts to the right one space, if I open the csv file in Excel, I can see the most left cell after the first row is empty.

parts   destination 
===================     
1   9.71504E+11 
1   9.71504E+11
1   96656587662
1   9.71504E+11

This is my code :

$values =mysql_query( "SELECT parts,destination from log");

$rown = 0;
$row = array();
$row[] = 'parts';
$row[] = 'destination'."\n";
$data .= join(',', $row)."\n";

while( $row_select = mysql_fetch_assoc($values) ) {
  if($rown++==0)
    $row = array();

  $row[] = $row_select['parts'];
  $row[] = $row_select['destination']."\n";
}
$data .= join(',', $row)."\n";

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: text/csv");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $data;
exit();

Would you please help?

Regards,

are you sure if its skipping a cell ? or it may be due to alignment of the cells in excel ? — Arfeen
– Arfeen, Commented Apr 25, 2012 at 12:10
When I open the csv file in notepad it looks like this: parts,destination1,971504324,1,971503324324,1,9665623423,1,9715034234 there is no space between destination and "1" for the first row — alkhader
– alkhader, Commented Apr 25, 2012 at 12:20
The problem is the comma after the destination values. See my answer. — ChrisPatrick
– ChrisPatrick, Commented Apr 25, 2012 at 12:21
please, please, please fputcsv() will solve many potential problems with writing CSV files, and it's built into PHP already — Mark Baker
– Mark Baker, Commented Apr 25, 2012 at 12:25

Berry Langerak · Accepted Answer · 2012-04-25 12:16:56Z

First line in its correct place, but starting from the second row data shifts to the right one space, if I open the csv file in Excel, I can see the most left cell after the first row is empty.

I've found out the hard way that writing CSV is more complex than one would imagine. Take, for example, escaping the values so that the delimiter is properly escaped inside values. I've created a simple class that utilises fwritecsv( ) instead of trying to render CSV myself:

EDIT: I've changed the example to your usage scenario.

<?php
/**
 * Simple class to write CSV files with.
 * 
 * @author Berry Langerak <[email protected]>
 */
class CsvWriter {
    /**
     * The delimiter used. Default is semi-colon, because Microsoft Excel is an asshole.
     * 
     * @var string
     */
    protected $delimiter = ";";

    /**
     * Enclose values in the following character, if necessary.
     * 
     * @var string
     */
    protected $enclosure = '"';

    /**
     * Constructs the CsvWriter.
     */
    public function __construct( ) {
        $this->buffer = fopen( 'php://temp', 'w+' );
    }

    /**
     * Writes the values of $line to the CSV file.
     * 
     * @param array $line 
     * @return CsvWriter $this
     */
    public function write( array $line ) {
        fputcsv( $this->buffer, $line, $this->delimiter, $this->enclosure );
        return $this;
    }

    /**
     * Returns the parsed CSV.
     * 
     * @return string
     */
    public function output( ) {
        rewind( $this->buffer );
        $output = $this->readBuffer( );
        fclose( $this->buffer );

        return $output;
    }

    /**
     * Reads the buffer.
     * @return type 
     */
    protected function readBuffer( ) {
        $output = '';
        while( ( $line = fgets( $this->buffer ) ) !== false ) {
            $output .= $line;
        }
        return $output;
    }
}


/**
 * Example usage, in your scenario:
 */
$values = mysql_query( "SELECT parts,destination from log" );

$csv = new CsvWriter( );
$csv->write( array( 'parts', 'destination' ) ); // write header.

while( $line = mysql_fetch_assoc( $values ) ) {
    $csv->write( $line );
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: text/csv");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");

print $csv->output( );

exit();

h00ligan · Accepted Answer · 2012-04-25 12:21:20Z

2

Like @ChrisPatrick points out, the line

$data .= join(',', $row)."\n";

is causing the problem, you should move this to inside the while loop and then you'll get rid of the cell skipping.

answered Apr 25, 2012 at 12:21

h00ligan

1,49110 silver badges18 bronze badges

Comments

ChrisPatrick · Accepted Answer · 2012-04-25 12:44:52Z

1

The problem is in the following line

$data .= join(',', $row)."\n";

You're concatenating all the elements in $row with commas. So the destination of one row is concatenated with the parts of the next row with a comma in between, so you get something like this:

$data = $row_select['parts'] . ',' . $row_select['destination']."\n" . ',' . $row_select['parts'] etc...

The comma after the newline creates an empty cell at the beginning of the row.

UPDATE It should work with the following

while( $row_select = mysql_fetch_assoc($values)){
$row = array();
$row[] = $row_select['parts'];
$row[] = $row_select['destination']."\n";
$data .= join(',', $row);
}

edited Apr 25, 2012 at 12:44

answered Apr 25, 2012 at 12:17

ChrisPatrick

9848 silver badges19 bronze badges

3 Comments

alkhader Over a year ago

it will generate a syntax error Parser error "')' expected in function call (join)."

ChrisPatrick Over a year ago

Missed a semicolon, fixed it now.

alkhader Over a year ago

The syntax error generated from this line $data .= join(',' $row) there should be a comma between the ',' and $row

Collectives™ on Stack Overflow

Export to CSV file using PHP

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related