How do I convert json blocks to a CSV file(s)?

Question

Okay. I have a jsons file where everything is in blocks and not one single one. My question is now if I can convert the entire file (around 13k lines, every line is one block) to a csv file that I can later import to MySQL using PHP. Does anybody already have a script for that or a program? I tried doing it myself but gave up because I never did something like that in PHP...

{
    "reason_public": "",
    "reason_admin": "text",
    "enacted_by": 1,
    "created": 1,
    "time_lifted_orig": 1,
    "enacted_username": "text",
    "time_lifted": 1,
    "suspended_username": "text",
    "row_id": 1,
    "user_suspended": 1,
    "reason_private": "text"
} {
    "reason_public": "",
    "reason_admin": "",
    "enacted_by": 3,
    "created": 1,
    "time_lifted_orig": 1,
    "enacted_username": "text",
    "time_lifted": 1,
    "suspended_username": "text",
    "row_id": 1,
    "user_suspended": 1,
    "reason_private": "text"
}

first you need to decode it, second why you dont insert it direcly to db but export inport it? — Rafael Shkembi, Oct 17 '16 at 17:36
@RafaelShkembi - Devil's Advocate: He may need/want to manipulate the data in some Spreadsheet-friendly way before importing it into MySQL. — Christine, Oct 17 '16 at 17:50
starting with **[valid JSON](http://jsonlint.com)** would probably help. Did you also stop googling because you had not asked that question in the past **[a possible answer here](http://stackoverflow.com/questions/9573421/php-library-to-convert-json-to-csv)** ? — YvesLeBorg, Oct 17 '16 at 17:59

score 0 · Answer 1 · edited May 23 '17 at 12:06

The answer kind of depends on your application. If you just have a one-off need to convert a bunch of json, there are online tools that you can use to do it for you (like This One and This One). Though, I might not advise using an online tool if your json contains sensitive info.

If you need something automated, there are utilities out there you could download (like This One. Or, you could roll your own.

If you want/need to roll your own, I would suggest giving it a shot yourself and then coming back when you hit a stumbling block. This doesn't sound like an uncommon requirement, so there should be some good resources out there to get you started. If you can't find something to do the whole thing for you, I would recommend searching for how to read in json through php (maybe this?), then searching for how to spit out csv through php (perhaps this)... if you're married to using php. Personally, I might be inclined to write a stand-alone API.

score 0 · Answer 2 · answered Oct 17 '16 at 19:43

Assuming you're not talking about processing a file uploaded via a form, this should work:

<?php
    $read_file_handle = fopen("inputfile.txt", "r");
    $write_file_handle = fopen("outputfile.txt", "w"); //Will truncate file if it exists 
    if ($read_file_handle && $write_file_handle) {
        while (($line = fgets($read_file_handle)) !== false) {
            $output = json_decode($line, true); //Decode into array
            fputcsv($write_file_handle, $output);
        }

        fclose($read_file_handle);
        fclose($write_file_handle);
    } else {
        // error opening the file.
    }

?>

Note that, you'll need to use this function to test for errors during the json_decode process. This method requires that each line contains complete, properly formatted JSON. I did not run this myself, so might require minor tweaking.

How do I convert json blocks to a CSV file(s)?

2 Answers2