PHP converting JSON file into excel table is formatted incorrectly

Question

I have made a PHP file that takes this JSON-based file:

http://www.yellowpages.com.iq/ajax/get/register/Categories.php
and converts it into an excel-formatted-table with the formatting of (.cvs) using this code :

$contents = file_get_contents('http://www.yellowpages.com.iq/ajax/get/register/Categories.php');

$data = JSON_decode($contents);
$excel_file = fopen("file.csv", "a+");
$table = "<table border='1'>";
foreach($data as $elem) {
    $table .= "<tr>";
    foreach($elem as $key => $prop) {
        $table .= "<th>$key</th>";
        $table .= "<td>$prop</td>";
        fwrite($excel_file, "$key,$prop\n");
    }
    $table .= "</tr>";
}
$table .= "</table>";
echo $table;

But the problem being, is it takes the data and displays it correctly, although it tends to format it like so:

id 1 category Advertising
id 2 category Agriculture & FoodÂ
id 3 category Air condition
id 4 category Airlines
id 5 Aluminium & Glass

Instead of what I'm trying to make it look like which I made manually:

Any help would be appreciated!

CSV is a simple format, but it still has its corner cases (embedded commas, quotation marks, etc) that mean you can't just blindly output a string into a CSV file without examining it. You should really use fputcsv instead of doing it manually (e.g. fputcsv($excel_file, array($key, $prop));). — Mark Reed
– Mark Reed, Commented Aug 26, 2016 at 12:52

trincot · Accepted Answer · 2016-08-26 13:02:45Z

1

You could change the code using fputcsv, which takes care of double quotes and escaping them.

For that you need to get the JSON as an associative array (provide the second argument true):

$data = JSON_decode($contents, true);

And then the loop you have would be replaced with this:

// "loop" for the header (only 1 iteration)
foreach($data as $elem) {
    $table .= "<tr><th>" . implode("</th><th>", array_keys($elem)) . "</th></tr>";
    fputcsv($excel_file, array_keys($elem));
    break; // only need one row for header
}
// Restart loop for the data
foreach($data as $elem) {
    $table .= "<tr><td>" . implode("</td><td>", $elem) . "</td></tr>";
    fputcsv($excel_file, $elem);
}

edited Aug 26, 2016 at 13:02

answered Aug 26, 2016 at 12:47

trincot

357k38 gold badges282 silver badges339 bronze badges

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

Collectives™ on Stack Overflow

PHP converting JSON file into excel table is formatted incorrectly

I have made a PHP file that takes this JSON-based file:

Instead of what I'm trying to make it look like which I made manually:

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

I have made a PHP file that takes this JSON-based file:

Instead of what I'm trying to make it look like which I made manually:

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related