CSV to json - tree structure
New here? Learn about Bountify and follow @bountify to get notified of new bounties! x

CSV to json

I have a CVS file of a bunch of nodes/paths that we need to construct a hierarchy from to be able to build a proper tree.

for example, we have things like

1 | 2 | 3

which in path form look like

1 > 2 > 3

1 is the grandparent 2 is the parent and 3 is the child. we need to be able to create entities for the grandparent and parent etc..

here is a good view of some examples from our csv

https://www.dropbox.com/s/rxnhsm7nton5xhx/2019-08-01_09-10-34.png?dl=0

In terms of constructing the json here is a simple example of the sort of transform we need

https://www.dropbox.com/s/tlcxk5h87xlbo0e/2019-08-01_09-11-24.png?dl=0

here is a more complex one

https://www.dropbox.com/s/2dgalswt2kzboqz/2019-08-01_09-12-02.png?dl=0

as for coming up the IDs that are needed, I based it a bit on a representation of the hierarchy (order from the CSV obviously matters a lot not only for position but also the ID)

https://www.dropbox.com/s/8xlzr7jbisztsyy/2019-08-01_09-12-34.png?dl=0

I’m looking for a JavaScript page that i can give a csv to and it will give me back a json file which will have the tree fully represented. the UI of the page is unimportant as its just internal and just working in chrome is good enough for us

the full CSV can be found here

https://www.dropbox.com/s/68ae46ahbt60rjm/tree-categories.csv?dl=0

Any update on this?
kostasx 3 days ago
11 days ago
Tags
javascript

Crowdsource coding tasks.

5 Solutions


Here is my solution: https://codepen.io/denisstad/pen/pMrJee

Based on your example, it wasn't quite clear to me what exactly label and title should be, they are the same in my solution. Let me know if you need something different there. Also, please let me know if the IDs work for you!

Usage:
You simply select the file and a moment later it should spit out the JSON. I included two outputs, the first is the flat list, as per your example. The second output is the nested tree structure. You can ignore that if you don't need it.

Best

I'm having a tough time running this can you check it again? tried chrome and ff with the csv and after browse and select then nothing happens after a few minutes.
Qdev 17 days ago
I tested on Chrome and Firefox. Might wanna try again now, made a change.
DenisStad 17 days ago

<!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-8">
    <title>CSV to json - tree structure</title>
  </head>
  <body>
    <h1>CSV to json - tree structure</h1>
    <form>
      <input type="file" onchange="openFile(event)"/>
    </form>
    <script>
      "use strict";

      function download(filename, text) {
        var element = document.createElement('a');
        element.setAttribute('href', 'data:text/plain;charset=utf-8,' +
                                     encodeURIComponent(text));
        element.setAttribute('download', filename);

        element.style.display = 'none';
        document.body.appendChild(element);

        element.click();

        document.body.removeChild(element);
      }

      function textToCSV(text) {
        function parse(row) {
          var insideQuote = false, entries = [], entry = [];
          row.split('').forEach(function (character) {
            if (character === '"') {
              insideQuote = !insideQuote;
            } else {
              if (character === "," && !insideQuote) {
                entries.push(entry.join(''));
                entry = [];
              } else if (character != "\r") {
                entry.push(character);
              }
            }
          });
          entries.push(entry.join(''));
          return entries;
        }

        var lines = text.split("\n");
        return lines.map(parse);
      }

      function openFile(ev) {
        var input = ev.target;

        var reader = new FileReader();
        reader.onload = function() {
          var text = reader.result;
          var json = csvToJSON(textToCSV(text));
          download("output.json", JSON.stringify(json, null, 2)
            .replace(/("id": )"([0-9]+)"|("parentID": )"([0-9]+)"/g,
                     "$1$2$3$4"));
        }
        reader.readAsText(input.files[0]);
      }

      function csvToJSON(csv) {
        var idTree = {id: 0, children: {}, length: 0};

        var outputJSON = [];

        function flattenID(ids) {
          function zeroPad(num, places) {
            if (typeof num === "number") {
              var zero = places - num.toString().length + 1;
              return Array(+(zero > 0 && zero)).join("0") + num;
            } else {
              return "";
            }
          }

          if (ids.length > 0) {
            return [
              zeroPad(ids[0], 2),
              zeroPad(ids[1], 4),
              zeroPad(ids[2], 4),
              zeroPad(ids[3], 4)
            ].join("");
          } else {
            return null;
          }
        }

        function createNodes(values) {
          var nodes = [];

          var parent = [];
          var parentIDs = idTree;
          for (var level = 0; level < values.length; level++) {
            if (values[level].replace(/\s/g, '') === "") {
              break;
            }
            var nodeID = parentIDs.children[values[level]];
            if (typeof nodeID === "undefined") {
              parentIDs.children[values[level]] = {
                id: parentIDs.length + 1,
                children: {}, length: 0};
              nodeID = (parentIDs.length += 1);
              nodes.push({
                "id": flattenID(parent.concat([nodeID])),
                "position": nodeID,
                "label": values[level],
                "value": values.slice(0, level + 1).join(" > "),
                "title": values[level],
                "parentID": flattenID(parent)
              });
            } else {
              nodeID = nodeID.id;
            }
            parent = parent.concat([nodeID]);
            parentIDs = parentIDs.children[values[level]];
          }

          return nodes;
        }

        for (var i = 1; i < csv.length; i++) {
          Array.prototype.push.apply(outputJSON, createNodes(csv[i]));
        }

        return outputJSON;
      }
    </script>
  </body>
</html>
Codepen: https://codepen.io/anon/pen/VozvYY
CyteBode 17 days ago
output works! we are validating the data now thanks!
Qdev 17 days ago

Here's my solution:

Source

Demo

Edit: Wrong source link.

View Timeline