JSON To CSV conversion (JavaScript) : How to properly format CSV conversion

Question

I want to convert JSON responses to CSV format. I was able to figure out how to properly generate the headers in a way where parents of a key are appended together to generate a proper header. The problem I have is structuring the data that corresponds to each row. For example lets say I have these columns called id, tags, and friends_id. Taking the case for id = 1, tags = [car, plane] friends_id = [0,1,2]. It should kind of look like this in a csv table format.

+-------+-------+------------+
| id    | tags  | friends_id |
+-------+-------+------------+
| 1     | car   | 0          |
+-------+-------+------------+
| empty | plane | 1          |
+-------+-------+------------+
| empty | empty | 2          |
+-------+-------+------------+
| 2     | ...   | ...        |
+-------+-------+------------+

Here's some test data that I'm working on.

{
    "_id": "5cfe7d3c6deeeef08ce0444b",
    "name": "Debra Milligain",
    "phone": "+1 (906) 432-2182",
    "address": "676 Merit Court, Steinhatchee, Oregon, 5491",
    "tags": [
        "consequat",
        "reprehenderit",
        "amet"
    ],
    "Work": {
        "skills": [{
                "id": 0,
                "name": "Programming"
            },
            {
                "id": 1,
                "name": "Business"
            }
        ]
    },
    "friends": [{
            "id": 0,
            "name": "Stafford Hernandez"
        },
        {
            "id": 1,
            "name": "Colleen Christensen"
        },
        {
            "id": 2,
            "name": "Barker Keith"
        }
    ],
    "greeting": [],
    "favoriteFruit": "banana"
}

The following code (Which is a solution to an earlier problem I had, found here is what I use to recursively generate headers into a map or hash table and append their corresponding values.

var arrayOfHeaders = {};
var headerDirectory = "";
var rootLevel = true;
var temp = ""
var firstWalkthrough = true;
function traverseJSON(obj){
    for (var o in obj) {
        if (typeof obj[o] == "object") {
            //console.log("Before traversal ", o)
            //console.log("Traversing the object: ", obj[o])
            if(!isNaN(o)){
                //console.log("Current position is a number ", o)
            }else{
                console.log("Adding to directory... " , o)
                headerDirectory += (headerDirectory == "") ? o : "_" + o;
            }
            rootLevel = false;
            if (firstWalkthrough){
                firstWalkthrough = false;
                //if (o == 0) 
                rootLevel = true;
            }

            traverseJSON(obj[o]);
            rootLevel = true;
            temp = headerDirectory;
            headerDirectory = "";
        } else {
            if (rootLevel) {
                if(isNaN(o)){ 
                    headerDirectory = "";
                    //console.log("Has value and is root ", o, "and temp ", temp)
                    arrayOfHeaders[o] +=  ",\"" + obj[o] + "\"";
                }else{
                    arrayOfHeaders[headerDirectory+"_"+o] +=  ",\"" + obj[o] + "\"";
                }

            }
            else {
                //console.log("Has value and is not root ", o)
                //console.log("Current Header Directory " + headerDirectory)
                //console.log("Saved temp : ", temp)
                if(isNaN(o)){ 
                    if(headerDirectory == "") headerDirectory = temp; 
                    //arrayOfHeaders.push(headerDirectory + "_" + o)
                    arrayOfHeaders[headerDirectory + "_" + o] += ",\"" + obj[o] + "\"";
                }              
            }
        }
    }
    // console.log("Array of Headers : ", arrayOfHeaders)
}

This is the actual response from arrayofHeaders:

{ _id: 'undefined,"5cfe7d3c6deeeef08ce0444b"',
  name: 'undefined,"Debra Milligain"',
  phone: 'undefined,"+1 (906) 432-2182"',
  address: 'undefined,"676 Merit Court, Steinhatchee, Oregon, 5491"',
  tags_0: 'undefined,"consequat"',
  tags_1: 'undefined,"reprehenderit"',
  tags_2: 'undefined,"amet"',
  'Work_skills_id-skill': 'undefined,"0","Business"',
  'Work_skills_name-skill': 'undefined,"Programming"',
  'friends_id-friends': 'undefined,"0","1","2"',
  'friends_name-friends':
   'undefined,"Stafford Hernandez","Colleen Christensen","Barker Keith"',
  favoriteFruit: 'undefined,"banana"' }

I want to structure the data in such a way that it would be easy to loop through and produce a csv like this. The issue is that I'm not sure how to make the leap from whats above to whats below.

+--------------------------+-----------------+-------------------+---------------------------------------------+---------------+------------------------+--------------------------+---------------------+-----------------------+---------------+
| _id                      | name            | phone             | address                                     | tags          | Work__skills__id-skill | Work__skills__name-skill | friends__id-friends | friends__name-friends | favoriteFruit |
+--------------------------+-----------------+-------------------+---------------------------------------------+---------------+------------------------+--------------------------+---------------------+-----------------------+---------------+
| 5cfe7d3c6deeeef08ce0444b | Debra Milligain | +1 (906) 432-2182 | 676 Merit Court, Steinhatchee, Oregon, 5491 | consequat     | 0                      | Programming              | 0                   | Stafford Hernandez    | banana        |
+--------------------------+-----------------+-------------------+---------------------------------------------+---------------+------------------------+--------------------------+---------------------+-----------------------+---------------+
|                          |                 |                   |                                             | reprehenderit | Business               |                          | 1                   | Colleen Christensen   |               |
+--------------------------+-----------------+-------------------+---------------------------------------------+---------------+------------------------+--------------------------+---------------------+-----------------------+---------------+
|                          |                 |                   |                                             | amet          |                        |                          | 2                   | Barker Keith          |               |
+--------------------------+-----------------+-------------------+---------------------------------------------+---------------+------------------------+--------------------------+---------------------+-----------------------+---------------+

EDIT: El Tom - possible fix of your code

var arrayOfHeaders = {};
var headerDirectory = "";
var rootLevel = true;
var temp = ""
var firstWalkthrough = true;
traverseJSON(
    JSON.parse('{"_id": "5cfe7d3c6deeeef08ce0444b","name": "Debra Milligain","phone": "+1 (906) 432-2182","address": "676 Merit Court, Steinhatchee, Oregon, 5491","tags": ["consequat","reprehenderit","amet"],"Work": {"skills": [{"id": 0,"name": "Programming"},{"id": 1,"name": "Business"}]},"friends": [{"id": 0,"name": "Stafford Hernandez"},{"id": 1,"name": "Colleen Christensen"},{"id": 2,"name": "Barker Keith"}],"greeting": [],"favoriteFruit": "banana"}')
)
function traverseJSON(obj) {
    for (var o in obj) {
        if (typeof obj[o] == "object") {
            //console.log("Before traversal ", o)
            //console.log("Traversing the object: ", obj[o])
            if (!isNaN(o)) {
                //console.log("Current position is a number ", o)
            } else {
                console.log("Adding to directory... ", o)
                headerDirectory += (headerDirectory == "") ? o : "_" + o;
            }
            rootLevel = false;
            if (firstWalkthrough) {
                firstWalkthrough = false;
                //if (o == 0) 
                rootLevel = true;
            }

            traverseJSON(obj[o]);
            rootLevel = true;
            temp = headerDirectory;
            headerDirectory = "";
        } else {
            if (rootLevel) {
                if (isNaN(o)) {
                    headerDirectory = "";
                    //console.log("Has value and is root ", o, "and temp ", temp)
                    if (arrayOfHeaders[o] !== undefined) {
                        arrayOfHeaders[o].push(obj[o]);
                    } else {
                        arrayOfHeaders[o] = [obj[o]];
                    }
                } else {
                    if (arrayOfHeaders[headerDirectory + "_" + o] !== undefined) {
                        arrayOfHeaders[headerDirectory + "_" + o].push(obj[o]);
                    } else {
                        arrayOfHeaders[headerDirectory + "_" + o] = [obj[o]];
                    }
                }

            }
            else {
                //console.log("Has value and is not root ", o)
                //console.log("Current Header Directory " + headerDirectory)
                //console.log("Saved temp : ", temp)
                if (isNaN(o)) {
                    if (headerDirectory == "") headerDirectory = temp;
                    //arrayOfHeaders.push(headerDirectory + "_" + o)
                    if (arrayOfHeaders[headerDirectory + "_" + o] !== undefined) {
                        arrayOfHeaders[headerDirectory + "_" + o].push(obj[o]);
                    } else {
                        arrayOfHeaders[headerDirectory + "_" + o] = [obj[o]];
                    }
                }
            }
        }
    }
}
var res = JSON.stringify(arrayOfHeaders).replace(/,"/g, ',\n"')
console.log("Easier Arrays in Array printed by JSON.stringify:\n", res)
res = {
    "_id": ["5cfe7d3c6deeeef08ce0444b"],
    "name": ["Debra Milligain"],
    "phone": ["+1 (906) 432-2182"],
    "address": ["676 Merit Court, Steinhatchee, Oregon, 5491"],
    "tags_0": ["consequat"],
    "tags_1": ["reprehenderit"],
    "tags_2": ["amet"],
    "Work_skills_id": [0, 1],
    "Work_skills_name": ["Programming",
        "Business"],
    "friends_id": [0, 1, 2],
    "friends_name": ["Stafford Hernandez",
        "Colleen Christensen",
        "Barker Keith"],
    "favoriteFruit": ["banana"]
};
var yourResult = "";
for(var i in arrayOfHeaders) {
    if(arrayOfHeaders[i].length > 1) {
       arrayOfHeaders[i] = '"' + arrayOfHeaders[i].join('","') + '"';
    } else {
        arrayOfHeaders[i] = '"' + arrayOfHeaders[i] + '"';
    }
    yourResult += i + ':' + arrayOfHeaders[i] + '\n';
}
console.log("\nProbably result you wanted to collect but invalid JSON format:\n", yourResult);

But if you compare them with my answer, printed structure is the same, but not as your manualy generated table (probably some errors).

converting arbitrary data structures to csv is impossible. csvs are for tabular data and json isn't. — I wrestled a bear once., Jun 12 '19 at 17:50
There's a website that seems to do it the way I'm thinking of called [json-csv](https://json-csv.com/). And the response i currently have feels like I'm so close to getting it. I think I have to some how track the number of rows in some way. But again I'm not sure — davechester, Jun 12 '19 at 17:54
that page is doing the best it can with what you give it but it's output is semantically wrong. in order to produce semantically correct results you'd have to have multiple associated tables that reference each other... that said. there are libraries that do this out there already. no reason to roll your own. — I wrestled a bear once., Jun 12 '19 at 18:07
Do you know which libraries these are lol cause I've spent like a day trying to figure this stuff out. — davechester, Jun 12 '19 at 18:18
there are tons of em man, just do a google search. there's on on npm called json2csv that looks mature. — I wrestled a bear once., Jun 12 '19 at 18:21
@Tom yeah man for sure, I think it's still important to have a working answer for this question because it's a pretty common business problem for things like getting datasets or saving data in a human readable (non tech savy) way. Plus for those like me who want a very specific format. Anyways, Ill take a look at your code but if you can make it accept REST that would be awesome! — davechester, Jun 13 '19 at 14:16
Do you need to use JavaScript or would an existing command line tool work as well? I can recommend [Catmandu](http://librecat.org/Catmandu/) and [jq](https://stedolan.github.io/jq/) to convert JSON to CSV but as already mentioned there are many data processing tools. — Jakob, Jun 14 '19 at 10:42

Jan · Answer 1 · 2019-06-17T12:28:59.973

Is that it (have to run the snippet to see results as HTML table demo - do not know how to put it here directly) ?

function readDown(headName, arr, outRows) {
    var under = [headName];
    while (arr.toString().indexOf("[object") == 0
        || Array.isArray(arr)) {
        var hasArray = false;
        for (var i in arr) {
            if (arr[i].toString().indexOf("[object") == 0
                || Array.isArray(arr[i])) hasArray = true;
        }
        if (hasArray) {
            for (var i in arr)
                if (arr[i].toString().indexOf("[object") == 0
                    || Array.isArray(arr[i])) {
                    if (isNaN(i)) under[0] += '_' + i;
                    readDown(under[0], arr[i], outRows);
                    if (arr.length === undefined)
                        return; // empty associative (skills)
                    delete arr[i++];
                    var next = false; // are there following arrays to be groupped ?
                    while (i && arr[i - 1] === undefined && i < arr.length && (arr[i].toString().indexOf("[object") == 0
                        || Array.isArray(arr[i]))) { // group whole lowest array
                        next = true;
                        var idx = outRows.length - 1
                        if (!Array.isArray(outRows[idx][1])) {
                            outRows[idx][1] = [outRows[idx][1], arr[i++]];
                        } else outRows[idx][1].push(arr[i++]);
                        delete arr[i - 1];
                    }
                    if (next && i == arr.length) {
                        arr.length = 0; // array full of undefined members, fix length in case
                        return null; // and return - we are done here
                    }
                } else { // funny never get there now
                    if (hasArray && isNaN(i)) under[0] += '_' + i;
                    under.push(arr[i]);
                    break;
                }
        } else {
            if (arr.length === undefined) { // Work_skills[0], friends[0]
                under.push(arr);
                outRows.push(under);
            } else { // tags, greetings
                if(!arr.length) return; // do not keep empty greetings
                for (var i in arr) under.push(arr[i]);
                outRows.push(under);
            }
            return null; // all arrays return here
        }
        // unreachable next
    }
}
function JSON2CSV(arr, inRows, outRows) {
    var row = [];
    var hasArray = false;
    for (var a in arr) {
        try {
            if (arr[a] == null) { // Change null's to string
                arr[a] = "null";
            }
            if (Array.isArray(arr[a]) || arr[a].toString().indexOf("[object") == 0) {
                readDown(a, arr[a], outRows);
                continue;
            } else {
                if (isNaN(a)) {
                    outRows.push([a, arr[a]]);
                } else {
                    outRows.push(['#', arr[a]]);
                }
            }
        } catch (e) {
            return e;
        }
    }
}
function convert(json) {
    var JSONtxt;
    try {
        JSONtxt = JSON.parse(json);
    } catch (e) {
        console.log(e);
        return;
    }
    var columnsArray = [];
    JSON2CSV(JSONtxt, [], columnsArray);
    var maxDepth = 1; // find longest lowest array size
    for (var i in columnsArray)
        if (Array.isArray(columnsArray[i][1])
            && maxDepth < columnsArray[i][1].length)
            maxDepth = columnsArray[i][1].length;
    maxDepth++;
    var csv = [];
    for (var c in columnsArray) {
        if (Array.isArray(columnsArray[c][1])) {
            var r = 0;
            for (var nd in columnsArray[c][1][r]) {
                csv.push([columnsArray[c][0] + '_' + nd]); // top header
                var col = csv.length - 1;
                for (; r < columnsArray[c][1].length; r++) {
                    csv[col].push(columnsArray[c][1][r][nd]); // key's value
                    delete columnsArray[c][1][r][nd];
                }
                r = 0;
            }

        } else {
            csv.push(columnsArray[c]);
        }
        while (csv[c].length < maxDepth) csv[c].push(null);
    }
    var csvTxt = ""; // tab separated values (copy/paste 2 XL ?)
    var htmlTab = "<TABLE border=\"1\" width=\"1500\">";
    for (var r = 0; r < maxDepth; r++) {
        htmlTab += "<TR>";
        for (var c in csv) {
            htmlTab += r==0?"<TH":"<TD"; // mind 1st header row too
            if (c != 0) csvTxt += '\t';
            if (csv[c][r] != null) {
                csvTxt += csv[c][r];
                htmlTab += ">" + csv[c][r];
            } else htmlTab += " class=empty>"
            htmlTab += "</TD>";
        }
        csvTxt += '\r\n';
        htmlTab += r==0?"</TH>":"</TR>";
    }
    htmlTab += "</TABLE>";
    return htmlTab;
}
var json = '{"_id": "5cfe7d3c6deeeef08ce0444b","name": "Debra Milligain","phone": "+1 (906) 432-2182","address": "676 Merit Court, Steinhatchee, Oregon, 5491","tags": ["consequat","reprehenderit","amet"],"Work": {"skills": [{"id": 0,"name": "Programming"},{"id": 1,"name": "Business"}]},"friends": [{"id": 0,"name": "Stafford Hernandez"},{"id": 1,"name": "Colleen Christensen"},{"id": 2,"name": "Barker Keith"}],"greeting": [],"favoriteFruit": "banana"}';

table {
  border-spacing: 0px; /* small tricks 2 make rounded table simply or */
}
th {
  text-align:left; /* centered looks ugly */
}
td.empty {
  background-color:lightgray; /* mark null cells */
}

<body onload="document.body.innerHTML=convert(json)"></body>

Original code without tidy modifications for the snippet:

function readDown(headName, arr, outRows) {
    var under = [headName];
    while (arr.toString().indexOf("[object") == 0
        || Array.isArray(arr)) {
        var hasArray = false;
        for (var i in arr) {
            if (arr[i].toString().indexOf("[object") == 0
                || Array.isArray(arr[i])) hasArray = true;
        }
        if (hasArray) {
            for (var i in arr)
                if (arr[i].toString().indexOf("[object") == 0
                    || Array.isArray(arr[i])) {
                    if (isNaN(i)) under[0] += '_' + i;
                    readDown(under[0], arr[i], outRows);
                    if (arr.length === undefined)
                        return; // empty associative (skills)
                    delete arr[i++];
                    var next = false; // are there following arrays to be groupped ?
                    while (i && arr[i - 1] === undefined && i < arr.length && (arr[i].toString().indexOf("[object") == 0
                        || Array.isArray(arr[i]))) { // group whole lowest array
                        next = true;
                        var idx = outRows.length - 1
                        if (!Array.isArray(outRows[idx][1])) {
                            outRows[idx][1] = [outRows[idx][1], arr[i++]];
                        } else outRows[idx][1].push(arr[i++]);
                        delete arr[i - 1];
                    }
                    if (next && i == arr.length) {
                        arr.length = 0; // array full of undefined members, fix length in case
                        return null; // and return - we are done here
                    }
                } else { // funny never get there now
                    if (hasArray && isNaN(i)) under[0] += '_' + i;
                    under.push(arr[i]);
                    break;
                }
        } else {
            if (arr.length === undefined) { // Work_skills[0], friends[0]
                under.push(arr);
                outRows.push(under);
            } else { // tags, greetings
                if(!arr.length) return; // do not keep empty greetings
                for (var i in arr) under.push(arr[i]);
                outRows.push(under);
            }
            return null; // all arrays return here
        }
        // unreachable next
    }
}
function JSON2CSV(arr, inRows, outRows) {
    var row = [];
    var hasArray = false;
    for (var a in arr) {
        try {
            if (arr[a] == null) { // Change null's to string
                arr[a] = "null";
            }
            if (Array.isArray(arr[a]) || arr[a].toString().indexOf("[object") == 0) {
                readDown(a, arr[a], outRows);
                continue;
            } else {
                if (isNaN(a)) {
                    outRows.push([a, arr[a]]);
                } else {
                    outRows.push(['#', arr[a]]);
                }
            }
        } catch (e) {
            return e;
        }
    }
}
function convert(json) {
    var JSONtxt;
    try {
        JSONtxt = JSON.parse(json);
    } catch (e) {
        console.log(e);
        return;
    }
    var columnsArray = [];
    JSON2CSV(JSONtxt, [], columnsArray);
    var maxDepth = 1; // find longest lowest array size
    for (var i in columnsArray)
        if (Array.isArray(columnsArray[i][1])
            && maxDepth < columnsArray[i][1].length)
            maxDepth = columnsArray[i][1].length;
    maxDepth++;
    var csv = [];
    for (var c in columnsArray) {
        if (Array.isArray(columnsArray[c][1])) {
            var r = 0;
            for (var nd in columnsArray[c][1][r]) {
                csv.push([columnsArray[c][0] + '_' + nd]); // top header
                var col = csv.length - 1;
                for (; r < columnsArray[c][1].length; r++) {
                    csv[col].push(columnsArray[c][1][r][nd]); // key's value
                    delete columnsArray[c][1][r][nd];
                }
                r = 0;
            }

        } else {
            csv.push(columnsArray[c]);
        }
        while (csv[c].length < maxDepth) csv[c].push(null);
    }
    var csvTxt = ""; // tab separated values (copy/paste 2 XL ?)
    var htmlTab = "<TABLE border=\"1\">";
    for (var r = 0; r < maxDepth; r++) {
        htmlTab += "<TR>";
        for (var c in csv) {
            htmlTab += "<TD>";
            if (c != 0) csvTxt += '\t';
            if (csv[c][r] != null) {
                csvTxt += csv[c][r];
                htmlTab += csv[c][r];
            }
            htmlTab += "</TD>";
        }
        csvTxt += '\r\n';
        htmlTab += "</TR>";
    }
    htmlTab += "</TABLE>";
    console.log(csvTxt);
    console.log(htmlTab);
}
var json = '{"_id": "5cfe7d3c6deeeef08ce0444b","name": "Debra Milligain","phone": "+1 (906) 432-2182","address": "676 Merit Court, Steinhatchee, Oregon, 5491","tags": ["consequat","reprehenderit","amet"],"Work": {"skills": [{"id": 0,"name": "Programming"},{"id": 1,"name": "Business"}]},"friends": [{"id": 0,"name": "Stafford Hernandez"},{"id": 1,"name": "Colleen Christensen"},{"id": 2,"name": "Barker Keith"}],"greeting": [],"favoriteFruit": "banana"}';
convert(json);

And even my original JSONoperations.hta CSV export does not look so bad, but a bit rotated and includes useless 1-level numbering, anyway if I would start here ;-)

table { border-spacing: 0px; }
th { text-align:left; }
td.empty { background-color:lightgray;}

<TABLE border="1" width="900"><TR><TD>tags</TD><TD>0</TD><TD>1</TD><TD>2</TD></TR><TR><TD class="empty"></TD><TD>consequat</TD><TD>reprehenderit</TD><TD>amet</TD></TR><TR><TD>Work</TD><TD>skills</TD><TD>#</TD><TD>id</TD><TD>name</TD></TR><TR><TD class="empty"></TD><TD class="empty"></TD><TD>1</TD><TD>0</TD><TD>Programming</TD></TR><TR><TD class="empty"></TD><TD class="empty"></TD><TD>2</TD><TD>1</TD><TD>Business</TD></TR><TR><TD>friends</TD><TD>#</TD><TD>id</TD><TD>name</TD></TR><TR><TD class="empty"></TD><TD>1</TD><TD>0</TD><TD>Stafford Hernandez</TD></TR><TR><TD class="empty"></TD><TD>2</TD><TD>1</TD><TD>Colleen Christensen</TD></TR><TR><TD class="empty"></TD><TD>3</TD><TD>2</TD><TD>Barker Keith</TD></TR><TR><TD>_id</TD><TD>name</TD><TD>phone</TD><TD>address</TD><TD>favoriteFruit</TD></TR><TR><TD>5cfe7d3c6deeeef08ce0444b</TD><TD>Debra Milligain</TD><TD>+1 (906) 432-2182</TD><TD>676 Merit Court, Steinhatchee, Oregon, 5491</TD><TD>banana</TD></TR></TABLE>

Ya recursion is always mind numbing haha and by path you mean header path right? Try looking at my previous code (just updated it cause i realized there was a mistake) but it gets the path pretty well. — davechester, Jun 13 '19 at 14:52
Thought about JSON iterator object, but as it was almost finished yesterday... But in case I have that iterator on my github in C# yet (Gason C# parser) and in using IIFEs it should be possible to do, but probably not so cute like in C# debug view anyway ;-) As long as this would be enough - or you can get basic orientation from basic comments. But can't guarantee it will work with different or more complex structure ;-) — Jan, Jun 14 '19 at 06:23
There are some iterators yet, but who want's to read so much so long documents ;-) https://stackoverflow.com/questions/722668/traverse-all-the-nodes-of-a-json-object-tree-with-javascript And rounded table also here https://stackoverflow.com/questions/4932181/rounded-table-corners-css-only — Jan, Jun 14 '19 at 07:10
Iterators IIFE published as answer to your older question here https://stackoverflow.com/questions/56531973/convert-json-to-csv-and-have-the-headers-be-concatenations-of-the-parents/56652311#56652311 — Jan, Jun 18 '19 at 15:22

JSON To CSV conversion (JavaScript) : How to properly format CSV conversion

1 Answers1

Linked