0

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 }] } }]] }
Improve this question
5
  • fix indentation of your example Commented Jul 16, 2014 at 17:34
  • you mentioned that your excel file has two columns, what I can see is one line, where are the columns? Commented Jul 16, 2014 at 17:39
  • Is there any way I can attach an excel file here, the numeric part is the second column but its coming out as one when I simply copy paste from excel Commented Jul 16, 2014 at 17:41
  • Oh ok fixed it. Indentation fixed it as you said.. Commented Jul 16, 2014 at 17:42
  • @ gawi, is there any way to achieve this? Commented Jul 16, 2014 at 17:46

1 Answer 1

Reset to default
1

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));
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

@ gawi..Thank you so very much bro...I think I can make this work with your code...Appreciate your prompt response and help :)
@ Gawi, I just checked on jsonlist.com that the output is not in a valid json format:/...it gives an error for the last value of size
@Khan JSON.stringify is the standard JSON converter for javascript, so you should get correct json string, what is the exact error?
@ Gawi...Apologies for the late reply..I think I edited out the string a little so it gave an error, However I am trying to achieve a desired output as mentioned above..It's a format like flare.json. Can you please let me know if i can somehow convert it into a flare.json format somehow, as I would be using this json file in D3 visualizations..
If you think the input file needs to be modified to achieve a flare.json format then I'll be ok with that too, but I am not sure at the moment how to...Thank you so much for all you assistance:)
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.