search

mhaemmerle / excel-to-json

Opinionated Excel to JSON converter where nested structures can be defined on different worksheets
http://mhaemmerle.github.io/excel-to-json/
Apache License 2.0
102 stars 25 forks source link

create nested json tree structure #20

Open doidata opened 10 years ago

doidata commented 10 years ago

Hello and I assume this is not an issue but rather a request, thanks and sorry for this. I try to create nested json array structures as an input file for d3js visualisation from excel files. I can create ONE array nest with the current excel-to-json succesfully, but cannot figure out how to create deeper array "children". The blueprint for the json would be the (infamous) "flare.json" structure which is the showcase for most d3js treemaps etc.: https://gist.github.com/mbostock/1093025 I have all the freedom to create the excel structur,e as I am just building the data from ground up. Is there a way to create this already now, or are the maybe some easy changes to your code to make this possible? dankeschoen aus wien, karl.

eproxus commented 10 years ago

Currently, excel-to-json only supports one level of nesting. The only way to nest deeper is to use nested keys as column names, like children.children.children, but that creates only maps, not arrays so you still would not get the JSON from your example, unfortunately.

doidata commented 10 years ago

thanks for the reply, I am rather glad to hear that it really is not possible (rather than me being too stupid to figure it out...). greetings karl.

eproxus commented 10 years ago

That being said, if you have any nice ideas about how to achieve this, it would be interesting to hear. :smile:

doidata commented 10 years ago

thanks for asking;-) In this first answer, I will present the collection of tools which I tried out so far to convert into the nested json format suitable for d3js. All tools take CSV as input, which I am exporting from excel, and are written in either PYTHON, JAVA or JAVASCRIPT. None of those works 100% for me unfortunately, for various reasons, but there might be some code in there which could be a base for the implementation:

https://groups.google.com/forum/?fromgroups=#!topic/d3-js/L3UeeUnNHO8/discussion https://github.com/OpenJC/CSV-to-flare.json https://github.com/albertchang/CSV-to-flare.json-D3-parser https://github.com/agalazis/ftf.js http://www.delimited.io/blog/2013/11/2/creating-nested-json-for-d3 http://stackoverflow.com/questions/17847131/generate-multilevel-flare-json-data-format-from-flat-json

Of course your application would be much more elegant to use, as I would not have to export from excel to csv, upload to the server, and download the result before feeding it to the server again ready for d3js. I will think about a "nice idea" of my own based on your example sheets for your excel-to-json code. I will need a day or two for this follow-up, thank you for your interest in my request, karl.

eproxus commented 10 years ago

Yeah, if you can build something on top of excel-to-json, that would be pretty nice. As you can see, we already have some notations in the project, like tab names (file.json#tab2, file.json#tab3) and array column names (like my_column@). If you come up with some alternative structure that would allow you to generate the data you want and can find a notation to support this, that could be an option.

doidata commented 10 years ago

Hello I am back with some findings and suggestions. First I tried various methods to create the nested structure with a combination of all the methods which are available already. The core problem, nesting the ARRAYS of children however, could not be solved by my attempts. As such, I present a notation which sticks as closely as possible to the existing methods, and adds just one new notation to adress the nested structure:

1) excel sheet relationship One could stick to the 2-sheet structure as described in the example "List sub properties". That is, the first sheet with an id column for the "Nodes", and a second sheet with id column, an empty column for labeling the text before the Array (before the square bracket open), and the following columns with the "Leaves" of the nodes. As such, there would be a cleare separation of tree structure (nodes on sheet one) and tree content (children or leaves on the second).

2) Node tree notation On the fist sheet, I suggest a new second column next to "id", which could be labled "parent". If you need an escape character for the code I would use the number sign, so you can parse that someone wants to create a nested tree, which gives "parent#" similar to the @-notation. The rule would be that you have to have one root, where the content of the "Parent#" cell would either be empty or contain the word "root", and all the other id-cells have to have the exact name of either the root id, or any other id-cell content in the "parent#" column cell content.

3) Leave/Children notation The nice thing about this suggestion I think is, that the "leaves" part already works perfectly with no coding needed. If you use the id-cell names under the id column on the second sheet, place the name "children" in the adjacent row header, then you can assign any number of leaves to the nodes from the first sheet.

I hope this makes sense and is written clearly enough, I am attaching 2 pictures to this posting to visualize the suggested excel, and here is a link to the expected JSON output code after using excel-to-json (json code created manually by me): https://gist.github.com/doidata/c0b654a2608c6140b8d4

nested_tree_sheet_1

nested_tree_sheet_2

thank you very much for looking into this proposal.

PS: I activated the d3js-installation on my server and fed my manually created json "expected result" gist-file into the d3js "collapsable tree" visualization. This is a screenshot of the output (which looks like I hoped it would from the given excel cell data... huraay!):

nested_tree_d3js

adunning commented 9 years ago

Did you eventually find a way of accomplishing this? I'm in a similar situation.

(Thanks, by the way, to everyone who has worked on this tool; it's fantastic.)

doidata commented 9 years ago

Yes I did find a way, but not with this tool. After coming up with a structure and notation in this thread there was no further response. As I am not a real coder I had to hire someone via elancer and had him program an excel vba script for me, which works perfectly for the output to d3js json. If you can work with excel vba in your environment I can send you the vba script for free, as I explicitely asked the programmer to give me the rights for public domain usage. Greetings Karl.

eproxus commented 9 years ago

@doidata If you don't mind, maybe link to the code from this issue? It would help other people having the same problem.

We started looking at supporting this feature in excel-to-json as well, but we haven't had time to properly implement something yet. This issue should stay open until we do.

MasterSquishy commented 9 years ago

@doidata Could you please link to the vba script code for this solution?

doidata commented 9 years ago

Hello yes i will Just out if touch with this topic for some months now i have to find time to search my harddrive.... Over weekend maybe Cheers karl

Chanelle notifications@github.com schrieb am 29.09.2015:

@doidata Could you please link to the vba script code for this solution?

Reply to this email directly or view it on GitHub: https://github.com/mhaemmerle/excel-to-json/issues/20#issuecomment-144095745

-- Diese Nachricht wurde mit K-@ Mail gesendet.

MasterSquishy commented 9 years ago

Thank you, much appreciated!

doidata commented 9 years ago

Hello so here is the excel sheet with embedde vba code (ExcelJSON.xlsm), there are some rules for correct usage which you find in the comments below written by the programmer. There is also a sample data xml sheet for testing. I wish you success with your project, greetings, Karl.

Few changes:

Rules:

On Tue, Sep 29, 2015 at 5:33 PM, Chanelle notifications@github.com wrote:

@doidata https://github.com/doidata Could you please link to the vba script code for this solution?

— Reply to this email directly or view it on GitHub https://github.com/mhaemmerle/excel-to-json/issues/20#issuecomment-144095745 .

chanellegreen commented 9 years ago

@doidata Thank you!...but there's no attachment :-)

doidata commented 9 years ago

Ok it seems that github mail drops the attachements? Please mail me directly at doidata@gmail.com Greetings karl.

chanellegreen notifications@github.com schrieb am 10.10.2015:

@doidata Thank you!...but there's no attachment :-)

Reply to this email directly or view it on GitHub: https://github.com/mhaemmerle/excel-to-json/issues/20#issuecomment-147015962

-- Diese Nachricht wurde mit K-@ Mail gesendet.

csalvado commented 8 years ago

Hello Doidata

Is it possible to share the VBA script? I might be able to crack the children nesting to Json from the excel. im going to send an email to you directily. Many thanks

KunalShahare commented 6 years ago

Hello Doidata,

Can you please share the VBA script for this issue. I am stuck with similar issue from last 2 weeks , it would be really helpful if you share the vba script for this solution.

Many thanks.

anicu24 commented 4 months ago

Hello @doidata,

Can you please send me also the VBA script? I have an excel table with some filters and would need to convert it to a JSON file as tree data structure.

I am strugling to create the VBA script.

Thank you very much