PHP need fputcsv to print NULL instead of an empty cell

Question

I am using fputcsv to create a csv file from a mysql table, everything works fine except if there's an empty column or NULL in the database table, as then the output csv cell is empty, rather than printing NULL.

Is this normal behaviour?

Heres the script im using

        private function me_calendar_report() {

        ini_set("memory_limit","256M");

        $calendar = array();

        $this->load->model('me_reports_model');

        $calendar = $this->me_reports_model->get_all_resource_calendar();

        $headers = array(
            array('id', 'Resource ID', 'Job ID', 'From date', 'To date', 'Colour', 'Resource Type', 'Is provisional', 'Fixed price'
            ));

        $calendar_data = array();

        foreach($calendar as $i) {
            $calendar_data[] = array(
                $i->id,
                $i->resource_id,
                $i->job_id,
                $i->from_date,
                $i->to_date,
                $i->colour,
                $i->resource_type,
                $i->is_provisional,
                $i->fixed_price
            );
        }

        $csv = array_merge($headers, $calendar_data);

        $fp = fopen('report_repository/resource_calendar-' . date('d-m-Y') . '.csv', 'w');

        foreach ($csv as $fields) {
            fputcsv($fp, $fields);
        }

        fclose($fp);
        return 'report_repository/resource_calendar-' . date('d-m-Y') . '.csv';

    }

“Is this normal behaviour?” – I’d think so, since echo NULL; also just yields an empty output. But you could easily solve this by replacing NULL values with the string value 'NULL' while populating the array in the loop. — C3roe
– C3roe, Commented Jun 11, 2014 at 17:02
You can either change your sql query, which I assume is in get_all_resource_calendar(), to change NULL to NULL values, ie. SELECT CASE WHEN `colour` IS NULL THEN 'NULL' ELSE `colour` END as `colour` OR in your foreach($calendar as $i)` loop -> $i->colour = (trim($i->colour)=='') ? 'NULL':$i->colour; — Sean
– Sean, Commented Jun 11, 2014 at 17:06
This worked for me $i->colour = (trim($i->colour)=='') ? 'NULL':$i->colour; — frobak
– frobak, Commented Jun 11, 2014 at 18:32
It would be great if fputcsv resulted in null values showing up as a blank like "value1",,"value3" and empty strings showed up like "value1","","value3" but I'm probably missing something. To work around nulls I am using JSON format instead of CSV for export/import. — Programster
– Programster, Commented Apr 25, 2018 at 11:04

cmc · Accepted Answer · 2014-06-11 17:06:06Z

5

Yes. The CSV has no way to display null values, NULL would actually be the string "NULL".

You can pre-process before writing if that's what you want:

foreach ($calendar_data as $key => $value) {
  if ($value === null) {
    $calendar_data[$key] = "NULL";
  }
}

Note that you are essentially creating your own specialized CSV format, which you should keep in mind if anyone else needs to use it. Similarly, when loading it, you would also pre-process the line after retrieving it with fgetcsv:

foreach ($calendar_data as $key => $value) {
  if ($value === "NULL") {
    $calendar_data[$key] = null;
  }
}

There will still be ambiguity though, you have just moved it from the empty string vs null to the string "NULL" vs null. If that's a problem you could use YAML, JSON or an SQL dump instead.

answered Jun 11, 2014 at 17:06

cmc

4,4232 gold badges39 silver badges37 bronze badges

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

Comments

Massimiliano Arione · Accepted Answer · 2019-06-21 08:59:09Z

1

In modern versions of PHP, null value is rendered as an empty string in csv. See this code:

<?php    
$filePointer = \fopen('php://memory', 'wb');
$row= ['ciao', 'bo;no', null, '', 12];
\fputcsv($filePointer, $row, ';');
\rewind($filePointer);
$a = \stream_get_contents($filePointer);
\fclose($filePointer);
echo $a;

Collectives™ on Stack Overflow

PHP need fputcsv to print NULL instead of an empty cell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related