CODEWITHSUNDEEP
javascriptjson
feeks
feeks

Reputation: 9

excel file to json conversion

I am trying to convert an excel file with two columns into a Json file. However, I want to keep the hierarchy as well some how. For example from the excel file below, I want the json to form a hierarchy in such a way that "Charles Johnson" is the root value, and the size associated to him is 5395. Similarly, in the second row, "Donald Williams" works under "Charles Johnson" and the size for him is 3057. And so on, the last name in each row of the file connects it to its parent nodes. And i want to use the .split(-) function I have been stuck at this for quite a while till i finally gave up:/ Any help would be greatly appreciated :)

Charles Johnson 4184
Charles Johnson-Donald Williams 8385
Charles Johnson-Donald Williams-Daniel Fertig   428
Charles Johnson-Donald Williams-Daniel Fertig-Lino Walling  1091
Charles Johnson-Donald Williams-Daniel Fertig-Lino Walling-Jim Cooke    318

Desired Output Type:

{
    "name": "flare",
    "children": [{
        "name": "analytics",
        "children": [{
            "name": "cluster",
            "children": [{
                "name": "AgglomerativeCluster",
                "size": 3938
            }, {
                "name": "CommunityStructure",
                "size": 3812
            }, {
                "name": "HierarchicalCluster",
                "size": 6714
            }, {
                "name": "MergeEdge",
                "size": 743
            }]
        }
    }]]
}

Upvotes: 0

Views: 263

Answers (1)

gawi
gawi

Reputation: 2962

You have to use recursion when parsing every single row. Assuming that you always have two tab separated columns and there is only one root your code could look like that:

//example data
var str = "Charles Johnson\t4184\nCharles Johnson-Donald Williams\t8385\nCharles Johnson-Donald Williams-Daniel Fertig\t428\nCharles Johnson-Donald Williams-Daniel Fertig-Lino Walling\t1091\nCharles Johnson-Donald Williams-Daniel Fertig-Lino Walling-Jim Cooke\t318";
var lines = str.split("\n");

var name_ = lines[0].split("\t")[0];
var val_ = lines[0].split("\t")[1];
var obj = {name:     name_,
           children: [],
           value:    val_};
//process all lines
for (var i=1;i<lines.length;i++) {
  var addr = lines[i].split("\t")[0].split("-");
  var val = lines[i].split("\t")[1];
  var local_obj  = obj;
  var recursive_obj;
  for (var j=1;j<addr.length;j++) {
    recursive_obj = null;
    for (var k=0;k<local_obj.children.length;k++) {
      if (local_obj.children[k].name==addr[j]) {
        recursive_obj=local_obj.children[k];
      }
    }
    if (recursive_obj==null) {
      recursive_obj = {name:     addr[j],
                       children: [],
                       value:    null
                      };
      local_obj.children.push(recursive_obj);
    }   
    local_obj=recursive_obj;
  }
  recursive_obj.value=val;
}

//print a json result
alert(JSON.stringify(obj));

Upvotes: 1

Related Questions