PHP - Creating CSV file inserts empty row at the end

Question

I am trying to create a CSV file in my php script. It creates the file but each time it inserts an empty row at the end.

My code is below:

$csv_output .= "Header 0,";
$csv_output .= "Header 1,";
$csv_output .= "Header 2,";
$csv_output .= "Header 3,";
$csv_output .= "Header 4,";
$csv_output .= "\r\n";

// db query...

foreach($rows as $row):

    if($row["value_3"] == 10)
    {
        $x = 'low';
    }
    else if($row["value_3"] == 100)
    {
        $x = 'high';
    }

    $csv_output .= "".$row["value_0"].",";
    $csv_output .= "".$row["value_1"].",";
    $csv_output .= "".$row["value_2"].",";
    $csv_output .= "".$x.",";
    $csv_output .= "Constant Always Value Goes here (not in db),";
    $csv_output .= "\r\n";
endforeach;

$filename = "csv_".date("d-m-Y_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;

No matter what my database query is or how many rows the csv contains there is always an empty row at the end. Is there anything causing this?

First rule of creating CSV files...... use PHP's built-in fputcsv() function..... then it won't break your file if you have a comma (or other delimiter character) in your data — Mark Baker
– Mark Baker, Commented Nov 30, 2015 at 16:52
And why are you using those empty strings ("") before each value..... it's pointless and meaningless overhead — Mark Baker
– Mark Baker, Commented Nov 30, 2015 at 16:54
And if there's an empty row at the end, it's likely to be added after your print $csv_output; line — Mark Baker
– Mark Baker, Commented Nov 30, 2015 at 16:55
Why are you using \r\n as an EOL character, that may work on your windows box, but all *NIX like systems use \n. Thankfully, PHP has a magic constant PHP_EOL that depends on the platform — Elias Van Ootegem
– Elias Van Ootegem, Commented Nov 30, 2015 at 17:04
@MarkBaker How does fputcsv() work in this case, where it seems to me like he's not actually writing the CSV file somewhere as opposed to prompting a download of it? I thought fputcsv only worked with resources, and I wouldn't know where to start addressing this as a resource. Thanks :) — Logan Bentley
– Logan Bentley, Commented Nov 30, 2015 at 17:08

Flosculus · Accepted Answer · 2015-11-30 17:12:11Z

1

You can write the CSV directly to the output like so, and you don't need to format it yourself.

// Send correctly formatted headers
header("Content-type: application/vnd.ms-excel");

// Disposition should be "attachment" followed by optional filename.
header(sprintf('Content-disposition: attachment; filename="csv_%s.csv"', date("d-m-Y_H-i")));

// Open a pointer to the output stream
$fp = fopen('php://output', 'w');

// Build header array
$head = array(
    "Header 0",
    "Header 1",
    "Header 2",
    "Header 3"
);

// Write the header array in default csv row format.
fputcsv($fp, $head);

// db query...

// Loop rows
foreach($rows as $row) {
    // Write the rows in default csv row format
    fputcsv($fp, $row);
}

// Close the output stream
fclose($fp);

An additional benefit is it will use less memory on large downloads, for instance with PDO:

$stmt = $pdo->prepare("SELECT ...");
$stmt->execute();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    fputcsv($fp, $row);
}

This will output each row to the browser, directly from the database (more or less), will a very low memory footprint.

answered Nov 30, 2015 at 17:12

Flosculus

6,9563 gold badges22 silver badges44 bronze badges

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

2 Comments

odd_duck Over a year ago

Only 1 thing and sorry didn't put this in my original question (now in edited). Inside my for each loop - I need to check what the value is and put text inside the csv rather than the value for one of them (see in edit if($row["value_3"] == 10) ... And also when i'm wanting to put a constant string in there (see header 4 column) which isn;'t accessed from my db query but is always a constant value for every row. How would I do this with fputcsv?

Flosculus Over a year ago

@odd_duck You can modify the elements of $row inside the loop. So to add a column at the end is: $row[] = "My Constant Value".

Alex Blex · Accepted Answer · 2015-11-30 17:08:35Z

0

Apart from other problems highlighted by Mark Baker, you are adding EOL in the loop. Remove the final one by trim

print trim($csv_output);

answered Nov 30, 2015 at 17:08

Alex Blex

37.3k7 gold badges53 silver badges86 bronze badges

Collectives™ on Stack Overflow

PHP - Creating CSV file inserts empty row at the end

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related