Creating json tree from excel file

[Sharad Adhikari]

Hi, 

I have following kind of excel data. 

Firm Department Sub-department Accounts Amount

Asia South-asia   Nepal               355599     120000

I want to build treemap as in the following link. http://philogb.github.com/jit/static/v20/Jit/Examples/Treemap/example1.html

My questions,

1. How do I create Json tree from the above excel data? There are nearly 200 rows of data. I need to automate it somehow.

2. Do I need to change anything in the example1.js file? I want to drill-down till accounts. 

I am new to javascript and replies with examples will be appreciated.

Thanks in advance

Regards

Sharad

[Michael Steidl (NewsIT)]

Hi Sharad,

 

I solved this issue this way:

-          I’m using Excel 2007 and 2010: open the Excel file, apply “Save As” and select as data format “XML spreadsheet 2003”

-          This creates an XML file with an <Data> element as child of <Cell> - this is the value you can use for JSON.

-          Then I’ve created an XSLT which reads the Excel XML and creates the JSON as output. This is a bit tricky as the Excel XML creates only <Cell> elements for the cells which have a value. All others are skipped and a column-qualifier attribute is used for the next cell with a value.

-          Then I copy and paste the JSON output into the JavaScript file.

 

Regards,

Michael

 

 

--
You received this message because you are subscribed to the Google Groups "JavaScript InfoVis Toolkit" group.
To view this discussion on the web visit https://groups.google.com/d/msg/javascript-information-visualization-toolkit/-/juti7i7komwJ.
To post to this group, send email to javascript-information...@googlegroups.com.
To unsubscribe from this group, send email to javascript-information-visua...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/javascript-information-visualization-toolkit?hl=en.

[Jay @ Jazfry]

These posts need G+ buttons.

Even though I don't know exactly what you are saying, I think I could do it after reading your post.

I have been writing my JSON tables in Notepad++ because the strength of JSON is also a weakness  It can store data in so many ways that the open source tools aren't quite good enough. I think current programmers are distracted by the ability to name the child elements rather than the "outlining" strengths of JSON.

The following is the potential open source tool I imagine could be a good start: Any thoughts from real programmers?
Step one - Create a list of all items manually or from CSV file in an open source interface.
Step two - Choose a "level" for each item to create a visual outline - order of elements would determine whether a child was placed into I.A.1. or I.B.1.

Step three - Assign categories to a sub level. for example, all "I.LETTER.1."s could be called "firstname" and all "I.LETTER.2."s could be called "lastname" - all capital letters could be a "Customer ID Number". "I" would be the name of the info, say "Customers". "II" could be Products. All "II.LETTER.2"s could be the product "Weight".

Step Three - Generate JSON based on the outline

Imagining what this would look like to the user:

I. Customer

  A. Customer ID: 1000

    1. First Name: John

    2. Last Name: Smith

    3. Children:

      a. Richard (NOTE: "I.A.3.a" has a "generic" field type. this should just work with the ability to name or move the field later only if necessary)

      b. Susan

      c. James

  B. Customer ID: 1001

    1. First Name: Sally

    2. Last Name: Jones

    3. Children:

      a. Accountant (NOTE: "I.A.3" does not represent the same field type as "I.B.3" this should be ok)

      b. Volunteer Efforts:

         sub1. Food Bank

         sub2. Literacy

II. Product

  A. Product ID: 2000

    1. Name: Shirt

    2. Weight: 0.5 lbs

III. Category Object

  A. ID Number: 3000

    1. Field: example1

       a. Field: example detail

    2. Field: example 2

  B. ID Number: 3001

    1. Field: example

{{{Click Here to Generate JSON}}}

Ok, so way over simplified and not about the execution at all... but I think a tool like this could be a good starting place that could eventually get better at interpreting CSV and other initial data types.

Whoever overseas making this thing will also become famous of course - lol.

This is the most useful tool I have found http://www.jsoneditoronline.org/ but you cannot create the starting outline with the "Auto Field Type" easily (Where is the "Add object" button and why are the "children" visually at the same level as the "parent" object) so I used the tool to learn how JSON worked and then hand coded my last project.

Anyone up to the challenge?

Cheers,

Jay

Advantages of this approach. By ignoring JSONs ability to categorize