Methods to convert 2,000-10,000 name:value pairs of excel shreadsheet data into JSON format. Maximum recursive depth is approx 10.
I need to convert 2,000-10,000 name:value pairs of data from excel sheets into JSON format.
The data is not in a regulate tabular structure, and will be written to the spreadsheet in a layout that I can define.
The maximum 'depth' of recursion within the string is about 10.
My data DOES form a functional JSON string (when created via a method that cant be transferred to Excel).
Processing speed is not an issue.
I write vba; some practical experience of classes.
I seek pointers to established methods that can be run in excel, or in association with excel, preferably on the WEB.
Thanks
Kelvin
The data is not in a regulate tabular structure, and will be written to the spreadsheet in a layout that I can define.
The maximum 'depth' of recursion within the string is about 10.
My data DOES form a functional JSON string (when created via a method that cant be transferred to Excel).
Processing speed is not an issue.
I write vba; some practical experience of classes.
I seek pointers to established methods that can be run in excel, or in association with excel, preferably on the WEB.
Thanks
Kelvin
Nitin.
THANKS for responding!
The Excel file exists but the sheet of name:value pairs does not.
The Excel file currently controls the creation quite complex diagrams in MS Visio, being bound to the object model of Visio.
- The components of the diagram are SHAPES of different types, that fit into my DIAGRAM CONTENT OBJECT MODEL.
- Each shape has a Diagram_Object.Name
- Different diagrams have different sets of these objs; some Objs appear in some diagrams, not others, many appear in all diagrams.
- To requirement is to extract Name: Value pairs from diagram SHAPE objs: for Shape position, text they display, and values they contain.
The job of JSON is to deliver data to the Cloud that will render it into an interactive diagram.
This works. When (as test of concept) Diagram object Values were written BY HAND into an MsSql database, the JSON string output (exported to a DB on the Cloud) successfully created interactive diagrams in the browser.
I have a CHOICE: Either to use Excel to create JSON string directly, OR to export the data from Visio/Excel to the mySQL database, and use that to make the JSON string.
THUS I ask the QUESTION: what does it take to write JSON from Excel? However, you may have counter-views about using excel for this/as opposed to MySql. This MySql DB has no additional value, since the JSON strings used to render web-based images are stored permanently on a different DB on the cloud.
This bit of the project is new to me. I seek hi level guidance about BIG choices and and indication of method.
Thanks for your interest
Kelvin
THANKS for responding!
The Excel file exists but the sheet of name:value pairs does not.
The Excel file currently controls the creation quite complex diagrams in MS Visio, being bound to the object model of Visio.
- The components of the diagram are SHAPES of different types, that fit into my DIAGRAM CONTENT OBJECT MODEL.
- Each shape has a Diagram_Object.Name
- Different diagrams have different sets of these objs; some Objs appear in some diagrams, not others, many appear in all diagrams.
- To requirement is to extract Name: Value pairs from diagram SHAPE objs: for Shape position, text they display, and values they contain.
The job of JSON is to deliver data to the Cloud that will render it into an interactive diagram.
This works. When (as test of concept) Diagram object Values were written BY HAND into an MsSql database, the JSON string output (exported to a DB on the Cloud) successfully created interactive diagrams in the browser.
I have a CHOICE: Either to use Excel to create JSON string directly, OR to export the data from Visio/Excel to the mySQL database, and use that to make the JSON string.
THUS I ask the QUESTION: what does it take to write JSON from Excel? However, you may have counter-views about using excel for this/as opposed to MySql. This MySql DB has no additional value, since the JSON strings used to render web-based images are stored permanently on a different DB on the cloud.
This bit of the project is new to me. I seek hi level guidance about BIG choices and and indication of method.
Thanks for your interest
Kelvin
Got it. Thanks for the detailed explanation. Whether or not I will be able to provide solution, it is really helpful for other experts. Still trying to digest the text.
Is there human intervention involved? Or this is a automated process? If automated, what are the trigger events? Just trying to see where the proposed solution fits in. For example, some C# program which would directly read Visio Object model and create a json out of it.
Is there human intervention involved? Or this is a automated process? If automated, what are the trigger events? Just trying to see where the proposed solution fits in. For example, some C# program which would directly read Visio Object model and create a json out of it.
Thank you.
Once the diagram has been laid out and text has been added to some shapes, no human intervention is required.
If, when a Visio diagram is complete, a button labelled "Render to Browser" triggered collection of data from Visio, compiled JSON, validated it, and dispatched it to DB, that would be perfect.
Ideally, the whole application would be web-based but, initially, the steps up to and including JSON creation could be based on the HD.
[At present JSON creation is on the HD, and Visio is not connected to it.]
Once the diagram has been laid out and text has been added to some shapes, no human intervention is required.
If, when a Visio diagram is complete, a button labelled "Render to Browser" triggered collection of data from Visio, compiled JSON, validated it, and dispatched it to DB, that would be perfect.
Ideally, the whole application would be web-based but, initially, the steps up to and including JSON creation could be based on the HD.
[At present JSON creation is on the HD, and Visio is not connected to it.]
This may sound rude, but considering everything involved, I am not sure if I would be of much help to you. From whatever tool set I excel in VBA sounds like your best bet.
I believe (being part of Office family) it should be possible to write macro in Visio in VBA. Please correct me, if I am wrong.
You mention you know VBA. In that case what precise area where you are stuck, should you decide to take VBA path to solution?
I wrote VBA quite a few years back and no longer quite proficient in it now. I know JSON fairly well.
I believe (being part of Office family) it should be possible to write macro in Visio in VBA. Please correct me, if I am wrong.
You mention you know VBA. In that case what precise area where you are stuck, should you decide to take VBA path to solution?
I wrote VBA quite a few years back and no longer quite proficient in it now. I know JSON fairly well.
Your comments are much appreciated.
My final question:
If I take the vba route, what resources would:
A-- provide me with a Framework within which to assemble the JSON string, or would I need to assemble it by de novo (VBA) concatenation (starting dim myJSON as String, and ending up with myJSON being the final data string)?
B-- to validate the output?
With the aid of my 'dream' Framework, I imagine writing a set of vba classes to extract raw data from Visio (plus a REF to each data's location within JSON), maybe wrap it in braces etc, and automatically deliver it to the growing JSON string.
Are such applications available? A pointer to key concepts would be appreciated.
Thanks in advance
Kelvin
My final question:
If I take the vba route, what resources would:
A-- provide me with a Framework within which to assemble the JSON string, or would I need to assemble it by de novo (VBA) concatenation (starting dim myJSON as String, and ending up with myJSON being the final data string)?
B-- to validate the output?
With the aid of my 'dream' Framework, I imagine writing a set of vba classes to extract raw data from Visio (plus a REF to each data's location within JSON), maybe wrap it in braces etc, and automatically deliver it to the growing JSON string.
Are such applications available? A pointer to key concepts would be appreciated.
Thanks in advance
Kelvin
Thanks, Nitin,
That broke the 'ice' on my thinking, and I appreciated your early response.
Cheers,
Kelvin
That broke the 'ice' on my thinking, and I appreciated your early response.
Cheers,
Kelvin
This is probably best example of "How to earn points without providing solution".
You accepting this as a solution also made my 10K necessary for free expert membership next month!
You accepting this as a solution also made my 10K necessary for free expert membership next month!
Nitin: Agreed!!
One of my problems of being coder AND project-manager is to keep looking ahead and getting something out of it without wasting time.
For me the outcome was: think harder before discarding the option of sending the JSON data direct from Visio (using its direct DB connection facilities) to mySQl DB (which has proven ability to make functional JSON for ME!)
There should be an EE award for your sort of open interaction, for engaging as Cardboard Programmer !! (http://wiki.c2.com/?CardboardProgrammer).
Have a good one
Bye!
Kelvin
One of my problems of being coder AND project-manager is to keep looking ahead and getting something out of it without wasting time.
For me the outcome was: think harder before discarding the option of sending the JSON data direct from Visio (using its direct DB connection facilities) to mySQl DB (which has proven ability to make functional JSON for ME!)
There should be an EE award for your sort of open interaction, for engaging as Cardboard Programmer !! (http://wiki.c2.com/?CardboardProgrammer).
Have a good one
Bye!
Kelvin
Thanks for using Experts Exchange.
Please provide your email to receive a sample view!
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.