From json to array to datagrid

[teacherguy]

I'm trying to grab some json output from a google spreadsheet and eventually get it into a datagrid. Thanks to Marks json functions I am able to get the json into an array, but now I'm stuck as far as getting it into a datagrid. Here is what the array looks like:

Code: Select all

table :

   cols :
      1 :
         label = Title
         type = string
         id = B
         pattern = 

      2 :
         label = Section
         type = string
         id = C
         pattern = 

      3 :
         label = Today
         type = boolean
         id = D
         pattern = ✔:✗

      4 :
         label = Link
         type = string
         id = E
         pattern = 

      5 :
         label = Notes
         type = string
         id = F
         pattern = 

   rows :
      1 :
         c :
            1 :
               v = Stars and Stripes Forever

            2 :
               v = Beginning to trio

            3 :
               f = true
               v = true

            4 :
               v = http://en.wikipedia.org/wiki/John_Philip_Sousa

            5 :
               v = Notes for the song go here

sig = 1493742981
status = ok
version = 0.6

[marksmithhfx]

I'm trying to grab some json output from a google spreadsheet and eventually get it into a datagrid. Thanks to Marks json functions I am able to get the json into an array, but now I'm stuck as far as getting it into a datagrid. Here is what the array looks like:

You are going to have to write a parser to read through the text and extract the information you need and put it into a format that a datagrid is looking for. Here's a strategy. Manually figure out what the 1st row of information should contain and then format it (tab delimited) for a datagrid. Then try importing that 1 row into your data grid. Now you have 1/2 the problem solved: you know (a) how to find the data you are looking for from the json output and (b) you know how to format it for a datagrid.

Step 2: write a simple program to read the input file and create the first row of data formatted for the grid. This is the same task you performed above manually, now you are just writing some code to do it. Now you are 3/4 of the way to your solution.

Step 3: get your code to loop through the input file as many times as you need to create all the rows. This generally means reading and repeating step 2 until you reach the end of the file. Est Voila, you are done.

PS if that seems overwhelming, try posting just step 1. Extract the first row of information from json and post it here along with the original input and someone will post a solution (step 2) of how to get from point a (the input) to point b (the line of data you need to import). At the moment your json probably makes sense to you, but looks like greek to me or I would provide an example of lc to do this straight away... but I don't know what you want to extract/import from the json and there looks to be a lot of extraneous structural information in the json.

Hope that helps

-- Mark

[teacherguy]

Thank you for your time Mark, I appreciate it. I'll post an update.

[teacherguy]

PS if that seems overwhelming, try posting just step 1. Extract the first row of information from json and post it here along with the original input and someone will post a solution (step 2) of how to get from point a (the input) to point b (the line of data you need to import). At the moment your json probably makes sense to you, but looks like greek to me or I would provide an example of lc to do this straight away... but I don't know what you want to extract/import from the json and there looks to be a lot of extraneous structural information in the json.

Hope that helps

-- Mark

The first row of the current table I'm working on is:

google.visualization.Query.setResponse({"version":"0.6","status":"ok","sig":"1493742981","table":{"cols":[{"id":"B","label":"Title","type":"string","pattern":""},{"id":"C","label":"Section","type":"string","pattern":""},{"id":"D","label":"Today","type":"boolean","pattern":"✔:✗"},{"id":"E","label":"Link","type":"string","pattern":""},{"id":"F","label":"Notes","type":"string","pattern":""}],"rows":[{"c":[{"v":"Stars and Stripes Forever"},{"v":"Beginning to trio"},{"v":true,"f":"TRUE"},{"v":"http://en.wikipedia.org/wiki/John_Phili ... "v":"Notes for the song go here"}]}]}});

It looks like I need all of the "v" information, not sure how to extract. The row would be:

Stars and Stripes Forever, Beginning to trio, true, http://en.wikipedia.org/wiki/John_Philip_Sousa, Notes for the song go here

Brian

[teacherguy]

Any thoughts? Thanks in advance for your time.

[teacherguy]

In my parsing I have managed to get this far with the array. I am working with just one column to try to understand how this works. Now I just need to figure out how to address column "v" and put it into a DataGrid.

Code: Select all

rows :
   1 :
      c :
         1 :
            v = Title

   2 :
      c :
         1 :
            v = Stars and Stripes Forever

   3 :
      c :
         1 :
            v = The Liberty Bell March

   4 :
      c :
         1 :
            v = National Emblem

[marksmithhfx]

In my parsing I have managed to get this far with the array. I am working with just one column to try to understand how this works. Now I just need to figure out how to address column "v" and put it into a DataGrid.

Hi, sorry I have been offline for a couple of days. I am going to post an example which may help get you started. There are two fields (input and output) and 2 buttons (parse and cleanup). Put the text you posted earlier into the input field. Click on parse. This will split your input into separate lines at the "v": delimiter. Next click on the cleanup button. This will attempt to cleanup the input further. Should be almost where you want to be.

Pls feel free to post questions if you have any.

Best,

-- Mark

[marksmithhfx]

Hi teacherguy, to make the output more readable for a dg, change "return" to "tab" in the following line in cleanup1

Code: Select all

put item 2 of twork & return after fld"output" -- keep whats between " "

-- Mark

[teacherguy]

Amazing, thank you so much. I was trying to use a bunch of replacetext commands and I knew there had to be something much more efficient.

Thanks again,

Brian

[teacherguy]

Mark,

Is there a way to deal with special characters during the parse, like u0027t?

[marksmithhfx]

Mark,

Is there a way to deal with special characters during the parse, like u0027t?

Hi Brian,

Depends what you want to do. If you just want to get rid of them try using something like the replacetext command I used in the parse routine. If u0027t actually looks like a character string in the input stream then the following change to the parse routine replaces u0027t with an empty character, effectively eliminating it. The idea is to put whatever you want replaced in the brackets (but you do have to be careful not to use any special characters... in the example after where I search for "v": the quote marks are special characters used in regular expressions so to get them to be interpreted literally as quotes I had to "escape" them using a leading backslash.

on mouseUp
put empty into fld output -- initialize
put fld"input" into tinput -- initialize
put "(u0027t)" into tregularexpression
put replacetext (tinput,tregularexpression,empty) into tinput -- replace each occurence of u0027t with nothing
put "(\" & quote & "v" & "\" & quote & ":)" into tregularexpression -- equivalent to "v":
put replacetext (tinput,tregularexpression,return) into fld"output" -- replace each occurence of "v": with return
end mouseUp

[teacherguy]

Great. Last question (I think): How could I adapt the script to also handle multiple records being returned by the json? I assume I would have to somehow delimit by "c:" first?

[marksmithhfx]

Great. Last question (I think): How could I adapt the script to also handle multiple records being returned by the json? I assume I would have to somehow delimit by "c:" first?

Hi Brian,

Yes, I think so (although I think it is "c":, identical to the syntax for "v":). I would just adapt the "v": replacetext to replace all of the record delimiters with returns, so that each record is a distinct row in your input... I think it would make it easier to process... just loop through the lines of input similar to code in cleanup1. I think I would delimit records with return and elements within each record with tab. Then, when you loop through each tabbed item, you set the delimiter to quote so you can extract the 2nd item between the first and second quote. If you want, post me an example with 2or 3 records and I'll post a working example.

-- Mark

[teacherguy]

I think I'm pretty close here, just missing a simple step I'm sure.... attached.

[marksmithhfx]

I think I'm pretty close here, just missing a simple step I'm sure.... attached.

Very close indeed. Good job!

-- Mark