Export table to CSV - escaping commas

Hi there,

I wonder if anyone can help me.

I am working on creating an export to CSV function for any collection I pass to it.

I have created the following class.

<?php

namespace App\Exporter;

class Exporter
{
    
    public function exportToCsv($collection)
    {
        $first = $collection->first();

        $output = implode(", ", array_keys($first['attributes']))."\n";

        foreach ($collection as $row) 
        {
            $output .= implode(", ",$row['attributes'])."\n";
        }

        $headers = [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => 'attachment; filename="export.csv"',
        ];

        return \Response::make($output, 200, $headers);
    }
}

This works well if there are no commas, but one field on say the Users table contains a description where the user can easily enter commas. These commas are breaking the layout of my csv export. I know I need to escape the commas I think by wrapping each field in double quotes but can't figure out how to achieve this with the implode function.

Any help appreciated!

Cronix

8 years ago

Level 67

Not tested...

foreach ($collection as $row) 
{
    // Check each attribute for a comma. If it has any, wrap attribute in a doube quote.
    foreach ($row['attributes'] as $key => $attribute) {
        if (strpos($attribute, ',') !== false) {
            $row['attributes'][$key] = '"' . $attribute . '"';
        }
    }
    $output .= implode(", ",$row['attributes'])."\n";
}

robrogers3

8 years ago

Best Answer

Level 37

use fputcsv

tested

$f = fopen('php://memory', 'w+');
$threads = Thread::get(['title', 'body', 'path']);
$header = array_keys($threads->first()->toArray());
fputcsv($f, $header);
foreach ($threads as $thread)
    fputcsv($f, $thread->toArray());

rewind($f);

$headers = [
    'Content-Type' => 'text/csv',
    'Content-Disposition' => 'attachment; filename="t.csv"',
];
return \Response::make(stream_get_contents($f), 200, $headers);

1 like

ralphmorris

8 years ago

Level 7

Thanks both for your replies :)

I initially tried @Cronix's suggestion as it was closest to what I already had which worked well for the escaping of commas but when I tried it on some live data I discovered there were more characters that needed escaping. I then tried @robrogers3's suggestion and integrated it into my class. It worked great with all data I tested it with.

Here is the full code incase it helps anyone else in the future.

<?php

namespace App\Exporter;

class Exporter
{
    /**
     * Export any collection to CSV. All fields exported.
     * 
     * @param  Collection $collection
     * @return Response downloads CSV
     */
    public function exportToCsv($collection)
    {
        $f = fopen('php://memory', 'w+');
        
        $header = array_keys($collection->first()->toArray());

        fputcsv($f, $header);
        foreach ($collection as $thread)
            fputcsv($f, $thread->toArray());

        rewind($f);

        $headers = [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => 'attachment; filename="export.csv"',
        ];

        return \Response::make(stream_get_contents($f), 200, $headers);
    }
}

ralphmorris

8 years ago

Level 7

Just trying to add the ability to remove certain keys from the exported csv, like 'id' etc.

I've tried adding the following line at the top of the above method.

$collection = $collection->forget($collection->first()->notExportable);

But has it has no effect?

On my model I have for example:

    public $notExportable = [
        'id',
        'some_other_protected_field'
    ];

My controller:

    public function exportToCsv(UserFilters $filters)
    {
        $users = User::filter($filters)->new()->get();

        $exporter = new Exporter;

        return $exporter->exportToCsv($users);
    }

Please or to participate in this conversation.