Converting this json data into a csv file in PHP

Question

my JSON data pattern is as follows:

{
    "Contestants": [
        {
            "Province": "Ontario",
            "Classification": "LSPI,,",
            "ClassificationDate": "2021",
            "RegistrationStatus": "Registered",
            "FirstName": "Kyle",
            "LastName": "Straunf",
            "Gender": "M",
            "AGE": null,
            "DOB": "02/08/2003",
            "Clubs": [
                {
                    "Clubname": "Penguins",
                    "Code": "MPNO",
                    "Clubid": "200"
                }
            ],
            "Email": null,
            "Language": "E",
            "ChallengeData": null
        },
        {
            "Province": "Alberta",
            "Classification": "LSPI,,",
            "ClassificationDate": "2021",
            "RegistrationStatus": "Registered",
            "FirstName": "Alexander",
            "LastName": "Kentwood",
            "Gender": "M",
            "AGE": null,
            "DOB": "08/16/2005",
            "Clubs": [
                {
                    "Clubname": "Elegant Dolphins",
                    "Code": "ZGIA",
                    "Clubid": "300"
                }
            ],
            "Email": null,
            "Language": "E",
            "ChallengeData": null
        }  
    ]
    
}

I managed to get the data like below, it prints but I'm having trouble printing each csv lines headers and data without it being overwritten. What's a good way to output it into a csv file with the keys as columns headers.

$all_data = json_decode($json_stream, true);

foreach ($all_data as $record) {
    foreach ($record as $rec) {
        foreach ($rec as $key => $value) {
            if (is_array($value)) {
                foreach ($value as $value2) {
                    foreach ($value2 as $key2 => $value2) {
                        echo "<p> key: $key2 . value: $value2</p>";
                    }
                }
            } else {
                echo "<p> key: $key . value: $value</p>";
            }
        }
    }
}

As for the expected output, I'd like all the keys to be column headers like the picture below but continued with the other headers and so on.

Classification section:

At the top is how it looks like presently however below is how it would be better

Your json is nested. How should that be dealt in the CSV? Share an expected output. — mighty_dev
– mighty_dev, Commented Jul 14, 2022 at 18:53
What if they are in more than one club? The fact that clubs is an array of objects implies this case. — Sammitch
– Sammitch, Commented Jul 14, 2022 at 19:09
Ok so basically, you can add them as another column header so the next one is like clubs2.clubname, clubs2.code, clubs2.clubid. THen it increments for the number of clubs for one contestant — answerSeeker
– answerSeeker, Commented Jul 14, 2022 at 19:15
@answerSeeker This is what I have so far. onlinephp.io/c/7dc08 Run and test this on your machine. I will write an answer with an explanation tomorrow morning. — mighty_dev
– mighty_dev, Commented Jul 14, 2022 at 20:15
Ok Sorry, I updated the question with visuals. "Classifications will become 3 columns which are S, SB, SM. The first value belongs to S, 2nd belongs to SB, 3rd belongs to SM — answerSeeker
– answerSeeker, Commented Jul 14, 2022 at 21:04

mighty_dev · Accepted Answer · 2022-07-15 05:59:58Z

First step is to get the maximum no. of clubs. This is important to maintain the order of the column headers.

Snippet:

<?php

function getMaxClubs($data){
    $max_clubs = 0;
    foreach($data as $value){
        $max_clubs = max($max_clubs, count($value['Clubs']));
    }
    return $max_clubs;
}

Second is to get the column headers. In here, we only proceed to the keys after the Clubs key once we have processed all the club keys which has maximum no. of clubs. We do this to maintain the order of the keys, a.k.a column headers.

Snippet:

<?php

function getHeadersRow($data, $max_clubs){
    $columns = [];

    foreach($data as $value){
        foreach($value as $k => $v){
            if($k === 'Classification'){
                $columns['SM'] = $columns['SB'] = $columns['S'] =  true;
            }elseif($k === 'Clubs'){
                if(count($v) !== $max_clubs) break;
                $max_clubs = -1; // indicating club keys are processed to avoid repetition
                $cnt = 0;
                foreach($v as $club_data){
                    $cnt++;
                    foreach($club_data as $club_key => $club_value){
                        $columns[ $club_key. $cnt] = true;
                    }
                }
            }else{
                $columns[ $k ] = true;
            }
        }
    }

    return array_keys($columns);
}

Now, we loop row by row. We append additional empty row entries for Clubs values if they are not having a count of $max_clubs.

Full Code:

<?php

$json = <<<EOD
{
    "Contestants": [
        {
            "Province": "Ontario",
            "Classification": "LSPI,,",
            "ClassificationDate": "2021",
            "RegistrationStatus": "Registered",
            "FirstName": "Kyle",
            "LastName": "Straunf",
            "Gender": "M",
            "AGE": null,
            "DOB": "02/08/2003",
            "Clubs": [
                {
                    "Clubname": "Penguins",
                    "Code": "MPNO",
                    "Clubid": "200"
                },
                {
                    "Clubname": "What Dolphins",
                    "Code": "AIZG",
                    "Clubid": "498"
                }
            ],
            "Email": null,
            "Language": "E",
            "ChallengeData": null
        },
        {
            "Province": "Alberta",
            "Classification": "LSPI,TEST2,TEST3",
            "ClassificationDate": "2021",
            "RegistrationStatus": "Registered",
            "FirstName": "Alexander",
            "LastName": "Kentwood",
            "Gender": "M",
            "AGE": null,
            "DOB": "08/16/2005",
            "Clubs": [
                {
                    "Clubname": "Elegant Dolphins",
                    "Code": "ZGIA",
                    "Clubid": "300"
                },
                {
                    "Clubname": "Weird Dolphins",
                    "Code": "ZGIA2",
                    "Clubid": "301"
                },
                {
                    "Clubname": "Favorite Dolphins",
                    "Code": "ZGIA3",
                    "Clubid": "302"
                }
            ],
            "Email": null,
            "Language": "E",
            "ChallengeData": null
        }  
    ]
    
}
EOD;

$data = json_decode($json, true);

$max_clubs = getMaxClubs($data['Contestants']);
$headers = getHeadersRow($data['Contestants'], $max_clubs);

$fp = fopen('test.csv', 'w+');

fputcsv($fp, $headers);
fputcsv($fp, array_fill(0, count($headers), ''));// empty next line for elegance

foreach($data['Contestants'] as $contestant_data){
    $row = [];

    foreach($contestant_data as $key => $value){
        if($key === 'Classification'){
            $row = array_merge($row, explode(",", $value));
        }elseif($key === 'Clubs'){
            foreach($value as $club){
                $row = array_merge($row, array_values($club));
            }
            $row = array_merge($row, array_fill(0, ($max_clubs - count($value)) * 3, ''));
        }else{
            $row[] = $value;
        }
    }

    fputcsv($fp, $row);
}

fclose($fp);


function getMaxClubs($data){
    $max_clubs = 0;
    foreach($data as $value){
        $max_clubs = max($max_clubs, count($value['Clubs']));
    }
    return $max_clubs;
}

function getHeadersRow($data, $max_clubs){
    $columns = [];

    foreach($data as $value){
        foreach($value as $k => $v){
            if($k === 'Classification'){
                $columns['SM'] = $columns['SB'] = $columns['S'] =  true;
            }elseif($k === 'Clubs'){
                if(count($v) !== $max_clubs) break;
                $max_clubs = -1; // indicating club keys are processed to avoid repetition
                $cnt = 0;
                foreach($v as $club_data){
                    $cnt++;
                    foreach($club_data as $club_key => $club_value){
                        $columns[ $club_key. $cnt] = true;
                    }
                }
            }else{
                $columns[ $k ] = true;
            }
        }
    }

    return array_keys($columns);
}

Collectives™ on Stack Overflow

Converting this json data into a csv file in PHP

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related