PHP array with array (2d array?) to csv

Question

I'm trying to create a CSV from an array with some data and another array in it.

Code:

public function exportCSV()
{
    $user = Auth::user();
    $company = $user->company;
    $dealers = $company->dealers;

    $formatted_dealers = [];

    foreach ($dealers as $dealer) {
        if($dealer->is_deleted == 0){
          array_push($formatted_dealers, $dealer);
        }
    }

    if(count($formatted_dealers) > 0){
        $csvData = array('name,phone,email,is_active,streetname,number,zip,city,special_id,products');
        foreach($formatted_dealers as $dealer){
            $products = [];
            foreach ($dealer->products as $product) {
              array_push($products, $product->slug);
            }
            $products = json_encode($products);
            $csvData[] = $dealer->name . ',' . $dealer->phone . ',' . $dealer->email . ',' . $dealer->is_active . ',' . $dealer->address->streetname . ',' . $dealer->address->number . ',' . $dealer->address->zip . ',' . $dealer->address->city . ',' . $dealer->special_id . ',' . $products;
        }

        $new_time = date('d-m-Y h:i:s', strtotime('+2 hours'));
        $filename = $new_time . '.csv';
        $file_path = base_path() . '/' . $filename;
        $file = fopen($file_path,'w+');
        foreach ($csvData as $exp_data){
          fputcsv($file, explode(',', $exp_data));
        }
        fclose($file);

        $headers = ['Content-Type' => 'application/csv'];

        return response()->download($file_path, $filename, $headers)->deleteFileAfterSend(true);
    } else {
        return redirect()->route('dealers.index', ['export' => 'error']);
    }
}

So, I have some data, like the dealers name, phone etc. and an array of products that the dealer has. My line of thought was to json_encode the products array and then append to the string that is being pushed to the csvData array. But the result I'm getting when doing this is:

name,phone,email,is_active,streetname,number,zip,city,special_id,products
"Some name",11111111,example@example.com,1,xxxx,x,xxxx,xxxxxxxx,96548,"[""nBuGbW""","""qP3DAF""]"
"Another name",22222222,anoter@example.com,0,xxxxxxxx,x,xxxx,xxxxxx,,"[""nBuGbW""","""IRTQBN""]"

the products json array has way too many " and I can't seem to figure out why. Any ideas?

In my head it should be something like:

name,phone,email,is_active,streetname,number,zip,city,special_id,products
"Some name",11111111,example@example.com,1,xxxx,x,xxxx,xxxxxxxx,96548,["nBuGbW","qP3DAF"]"
"Another name",22222222,anoter@example.com,0,xxxxxxxx,x,xxxx,xxxxxx,,["nBuGbW","IRTQBN"]"

Possible duplicate of [How to parse a CSV file using PHP](https://stackoverflow.com/questions/9139202/how-to-parse-a-csv-file-using-php) — PHP Ninja, Nov 01 '18 at 10:46
This will help you: https://stackoverflow.com/questions/32441327/csv-export-in-laravel-5-controller — Madhusudan, Nov 01 '18 at 10:46
Hmm I don't see how any of those are a help, I don't know if you've misunderstood me. I have an array of dealers and for each dealer an array of products. So I want to create a CSV with the dealers and their products. But when I do that the CSV look weird (the example above) — Nissen, Nov 01 '18 at 10:59

jeroen · Answer 1 · 2018-11-01T12:17:16.017

1

The problem is that you are manually constructing - invalid - csv:

$csvData[] = $dealer->name . ',' . $dealer->phone . ',' . $dealer->email . ',' 
    . $dealer->is_active . ',' . $dealer->address->streetname . ',' . $dealer->address->number . ',' 
    . $dealer->address->zip . ',' . $dealer->address->city . ','
    . $dealer->special_id . ',' . $products;

Your $products json will contain commas as well, so when you explode() on the commas, your data is all messed up and you have invalidated your json:

foreach ($csvData as $exp_data) {
    // Here you break your json data
    fputcsv($file, explode(',', $exp_data));
}

You should use a multi-dimensional array instead, making exploding unnecessary:

$csvData[] = [
    $dealer->name,
    ...
    $products,
];

and:

foreach ($csvData as $exp_data) {
    fputcsv($file, $exp_data);
}

Note that making it valid csv, will cause escaping of for example the double-quotes. So it will still look weird in a way :-)

edited Nov 01 '18 at 12:17

answered Nov 01 '18 at 11:09

jeroen

91,079
21
114
132

Hmm that gives me: Some name,88888888,example@example.com,1,xxxxxxx,x,xxxx,xxxxxxxx,96548,"[""nBuGbW"",""qP3DAF""]" the products are still incased in "" "" – Nissen Nov 01 '18 at 12:28
@Nissen Is this the exact result of a `var_dump()`? I would expect the whole section to be quoted, but the quotes inside that section to be escaped with backslashes. Either way, using CSV you will never get the results you are looking for as CSV does not support nested data structures. – jeroen Nov 01 '18 at 12:31
When I export the CSV and open it, it looks like this: "name,phone,email,is_active,streetname,number,zip,city,special_id,products" Some name,88888888,example@example.com,1,xxxxxx,x,xxxx,xxxxxxx,96548,"[""nBuGbW"",""qP3DAF""]" "Example name",xxxxxxxx,example@someotherexample.com,1,xxxxxxx,x,xxxx,xxxxxxx,,"[""nBuGbW"",""IRTQBN""]" – Nissen Nov 01 '18 at 12:37
@Nissen Like I said, CSV does not support nested data structures so you will never get a nicely readable format like in your desired output. JSON comes close, but that would require you to read and parse all data every time you need only one line so that is far from perfect too. But I guess it depends on your use-case. – jeroen Nov 01 '18 at 12:39

score 0 · Answer 2 · answered Nov 01 '18 at 12:30

This should be a comment but its a bit long (the problem you asked about, as jeroen says, you are double encoding the CSV data).

My line of thought was to json_encode the products array and then append to the string

So you have the lassitude to invent your own encoding system - which implies that you control the code which both encodes and decodes data. But why are you writing data to a flatfile?

You are heading for a whole world of pain if you are using flat files as a database. There is a reason that programmers have been using things like Oracle, MySQL, MongoDB, Redis.... for at least the last 40 years.

If, on the other hand, you are trying to implement a way of publishing data to different instances of an application (which does use a proper DB) then there are many, much better ways of doing this.

If you are publishing the data for someone else to consume then you shouldn't be inventing your encoding.

This looks like the XY problem.

PHP array with array (2d array?) to csv

2 Answers2