Create table from multiple JSON files

Question

I have over 3000 JSON files. What would be the easiest way to merge certain values from these files in one CSV table?

By example, each json has date_joined, country.name and expertise[] as keys. How would I combine these in a CSV like so:

date_joined,country.name,expertises
2021-09-13T21:40:08.567023Z,USA,"X, Y, Z"
2021-09-15T21:21:08.567033Z,UK,"X, A"

Please share any approach & more informations about your tools used, like which programming language...? — Maik Hasler, Feb 17 '22 at 10:24
Hi Maik, frankly, I care more about the output at this point. I was hoping it could be done with plain old JavaScript but it seems like a fairly painful resolution. — Daniel, Feb 17 '22 at 10:43
Is there any key in the JSON in order to check if a pair already exists? Like two JSON files can have users with the same id, but I guess you want to remove duplicates while merching, right? — Maik Hasler, Feb 17 '22 at 10:48
There's an "id" and a "slug" both of which are unique. All files contain a single entry, though, which is unique in itself. — Daniel, Feb 17 '22 at 11:07

Maik Hasler · Answer 1 · 2022-02-17T11:36:28.607

Disclaimer: I'm not very much experienced with JavaScript, so please excuse me if I srewed up anywhere. Anyways I want to share my suggestion.

In the first step, I would build a function, which can read a JSON file from your harddisk. For that I am using the function by the user @Stano, which he suggested in this answer.

function readFile(file, callback) {    
   var rawFile = new XMLHttpRequest();
   rawFile.overrideMimeType("application/json");
   rawFile.open("GET", file, true);    
   rawFile.onreadystatechange = function() {
      if (rawFile.readyState === 4 && rawFile.status == "200") 
      {
         callback(rawFile.responseText);
      }    
   }    
   rawFile.send(null); 
}

You can now read a JSON file from your harddisk, like down below.

readFile("test.json", function(text) {
   var data = JSON.parse(text);
   console.log(data); 
});

Let's take a look at an example by using the following JSON structure.

{
   "status": "Success",
   "data": {
      "key": [
         {
            "more_keys": "Some value"
         }
      ]
   }
}

You can extract the values from the above JSON structure like this:

readFile("my_test_json.json", function(text){
    var data = JSON.parse(text);
    console.log(data.status); // => Success
    console.log(data.key[0].more_keys); // => Some value
});

With that being said you can now iterate through your files and merch the JSON files into a two-dimensional array. Why?. Because it's very easy to serialize a .csv file from it. For that I am using the following approach, which was suggested by the user @Default in this answer.

const rows = [    
   ["name1", "city1", "some other info"],   
   ["name2", "city2", "more info"] 
];

let csvContent = "data:text/csv;charset=utf-8,";

rows.forEach(function(rowArray) {
    let row = rowArray.join(",");
    csvContent += row + "\r\n"; 
});

Then you can use JavaScript's window.open and encodeURI functions to download the CSV file like so:

var encodedUri = encodeURI(csvContent); 
window.open(encodedUri);

I hope that I could give you an idea how you can approach it, cheers!

Create table from multiple JSON files

1 Answers1