DOMdata Extender - Json parsing to Excel

[stanl]

Jim;


Still using the NC.Json file I uploaded earlier. Script below traverses the elements and picks out values into a 3 column output which is then transferred to Excel. Very fast, and although there may be an easier way to traverse with Maps instead of ItemExtract, again for the present a matter of style.  One thing I did notice. The parsing of the coordinates truncates to 2 decimals in Excel, but also noticed when just displaying the parsed Json w/out going to excel the coordinate that dmjGetValue() obtains tend to end in 0's and do not reflect the raw data. Perhaps something I missed, but you might wish to look at the difference and suggest a work around.

Code (WINBATCH) Select Expand



IntControl(73,1,0,0,0)
Gosub udfs
AddExtender("wbdomdata.dll")
BoxOpen("Parsing Json Data","Please Wait")
cJson = Dirscript():"nc.json"
dmParse(FileGet(cJson),@dmJSON)
decimals(18)


cJson = "Element":@TAB:"Fld":@TAB:"Data":@CRLF
path = dmjGetTreePath()
cnt = ItemCount(path,@LF)


For i = 1 To cnt
   element = ItemExtract(i,path,@LF)
   BoxText(element)
   tree  = MapCreate(dmjGetElementMap(element),@TAB,@CR)
   subcnt = ArrInfo(tree,1)
   If subcnt==0
      fld = ItemExtract(ItemCount(element,"/"),element,"/")
      data = dmjGetValue(element)
      If Strlen(StrTrim(data))>0
         data = StrReplace(data,@TAB," ")
         data = StrReplace(data,@CRLF," ")         
         data = StrReplace(data,@LF," ")
         cJson = cJson:element:@TAB:fld:@TAB:data:@CRLF
      Endif
   Endif
Next


;just check results
;Message("",cJson)


;comment above and uncomment below for Excel output
BoxText("Moving Data To Excel")
toXLSX()


Exit


:WBERRORHANDLER
geterror()
Terminate(@TRUE,"Error Encountered",errmsg)
Exit


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine


#DefineSubRoutine toXLSX()
IntControl(73,1,0,0,0)
ClipPut(cJson)
oXL = CreateObject("Excel.Application")
oXL.Visible          = @TRUE  ; change this to @FALSE to run hidden
oXL.ScreenUpdating   = @TRUE  ; if running hidden, change this to @FALSE
oXL.UserControl      = @TRUE
oXL.DisplayAlerts    = @FALSE
oXL.WorkBooks.Add()
BoxShut()
oWS = oXL.ActiveWorkBook.Worksheets(1)
oWS.Activate()
oWS.Name = "Weather Alter"
oWS.Cells(1,1).Select()
oWS.Paste()
oWS.UsedRange.Select()
oXL.Selection.Font.Name = 'Tahoma'
oXL.Selection.Font.Size = 9
oXL.Selection.Font.Bold = @True
oWS.UsedRange.Columns.Autofit()
oWS.ListObjects.Add(:1,oWS.UsedRange, , 1).Name ="Table1"
oWS.Range("Table1[#All]").Select()
oWS.ListObjects("Table1").TableStyle = "TableStyleLight15"
oXL.ActiveWindow.DisplayGridlines = @False


;to create hyperlinks
nRows = oWS.UsedRange.Rows.Count
oWS.Range("C1").Select
oXL.Selection.Interior.ColorIndex = 6
r=2
c=3
While r<nRows+1
   oWS.Cells(r,c).Select()
   v=oWS.Cells(r,c).value
   If StrIndexNC(v,"http",0,@FWDSCAN)
      oWS.Hyperlinks.Add(::Anchor=oXL.Selection,Address="%v%",SubAddress="", TextToDisplay="%v%")
   Endif
   r=r+1
Endwhile


oWS.Cells(1,1).Select()
oWS=0
oXL=0
Pause("Data Loaded Into Excel","Save or Close Workbook")
Return(1)
;======================================================================================================
:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
;======================================================================================================


:CANCEL
Display(2,"Thank you","Goodbye...")
Exit


#EndSubRoutine






Return


[stanl]

Below is a more full-blown parsing. Json is created from web extract. Don't know why but all of a sudden any of my scripts using System.Net.Http.HttpClient now fail at Response = oClient.GetAsync(request).Result with mscorlib multiple errors.... probably something updated from Microsoft incorrectly.... anyway using WinHttp.WinHttpRequest.5.1 works and is just as fast... 


The script does acknowledge a kluge of returning errors when using dmjGetValue(element) - basically just ignore them.  Didn't come across them until I tested other states from NC [like CA/NY].  Figured either no alerts or too many to count :o . and the coordinate truncation brought up in previous post still valid.


Anyway, would appreciate some feedback:

Code (WINBATCH) Select Expand



;Winbatch 2020A - Parse Json with new DOMData Extender from Jim Taylor
;uses  WinHttp.WinHttpRequest.5.1  and not System.Net.Http.HttpClient
;      (that now gives mscorlib multiple error message)
;very clumsy error handling as elements as often blank arrays in Json - []
;      ( dmjGetValue cannot process them )
;all credit to Jim's Extender
;Stan Littlefield, January 24, 2021
;======================================================================================================
IntControl(73,1,0,0,0)
Gosub udfs
AddExtender("wbdomdata.dll")
ar = "NC" ;choose a different state to test outut
          ;or make a list dropdown
cUrl = "https://api.weather.gov/alerts/active?area=%ar%"
BoxOpen("Parsing:":cUrl,"Please Wait...")
cJson = Dirscript():ar:".json"
If FileExist(cJson) Then FileDelete(cJson)       
request = Createobject("WinHttp.WinHttpRequest.5.1")
request.Open("GET", cUrl, @False )
request.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded")
request.SetRequestHeader("Accept", "application/json")
request.Send()
jdata = request.ResponseText
FilePut(cJson,jdata)
request = 0


If FileExist(cJson)
   Boxtext("Iterating Json Data")
   dmParse(FileGet(cJson),@dmJSON)
   decimals(18)
   cJson = "Element":@TAB:"Fld":@TAB:"Data":@CRLF
   path = dmjGetTreePath()
   cnt = ItemCount(path,@LF)


   For i = 1 To cnt
      element = ItemExtract(i,path,@LF)
      BoxText(element)
      tree  = MapCreate(dmjGetElementMap(element),@TAB,@CR)
      subcnt = ArrInfo(tree,1)
      If subcnt==0
         fld = ItemExtract(ItemCount(element,"/"),element,"/")
         dodata()       
      Endif
   Next
   BoxText("Moving Data To Excel")
   toXLSX()
Endif


Exit


:WBERRORHANDLER
geterror()
Terminate(@TRUE,"Error Encountered",errmsg)
Exit


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine


#DefineSubRoutine dodata()
IntControl(73,1,0,0,0)
data = dmjGetValue(element)
If Strlen(StrTrim(data))>0
  data = StrReplace(data,@TAB," ")
  data = StrReplace(data,@CRLF," ")         
  data = StrReplace(data,@LF," ")
  cJson = cJson:element:@TAB:fld:@TAB:data:@CRLF
Endif
Return(1)
:WBERRORHANDLER
IntControl(73,1,0,0,0)
Return(1)
#EndSubRoutine




#DefineSubRoutine toXLSX()
IntControl(73,1,0,0,0)
ClipPut(cJson)
oXL = CreateObject("Excel.Application")
oXL.Visible          = @TRUE  ; change this to @FALSE to run hidden
oXL.ScreenUpdating   = @TRUE  ; if running hidden, change this to @FALSE
oXL.UserControl      = @TRUE
oXL.DisplayAlerts    = @FALSE
oXL.WorkBooks.Add()
BoxShut()
oWS = oXL.ActiveWorkBook.Worksheets(1)
oWS.Activate()
oWS.Name = "Weather Alter"
oWS.Cells(1,1).Select()
oWS.Paste()
oWS.UsedRange.Select()
oXL.Selection.Font.Name = 'Tahoma'
oXL.Selection.Font.Size = 9
oXL.Selection.Font.Bold = @True
oWS.UsedRange.Columns.Autofit()
oWS.ListObjects.Add(:1,oWS.UsedRange, , 1).Name ="Table1"
oWS.Range("Table1[#All]").Select()
oWS.ListObjects("Table1").TableStyle = "TableStyleLight15"
oXL.ActiveWindow.DisplayGridlines = @False


;to create hyperlinks
nRows = oWS.UsedRange.Rows.Count
oWS.Range("C1").Select
oXL.Selection.Interior.ColorIndex = 6
r=2
c=3
While r<nRows+1
   oWS.Cells(r,c).Select()
   v=oWS.Cells(r,c).value
   If StrIndexNC(v,"http",0,@FWDSCAN)
      oWS.Hyperlinks.Add(::Anchor=oXL.Selection,Address="%v%",SubAddress="", TextToDisplay="%v%")
   Endif
   r+=1
Endwhile


oWS.Cells(1,1).Select()
oWS=0
oXL=0
Pause("Data Loaded Into Excel","Save or Close Workbook")
Return(1)
;======================================================================================================
:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
;======================================================================================================


:CANCEL
Display(2,"Thank you","Goodbye...")
Exit


#EndSubRoutine


Return


[JTaylor]

I found one function where I was converting to Int rather than Double.   Not sure that would affect what you are asking about but let me know if anything has changed.  Question below is probably related as well.

Also, I added dmjIsData() and dmjGetType() functions.   The former may be useful in the script you posted as you won't have to retrieve and check the size of a Map, if I read the script correctly.

Also, I am not a "Number" guy so if you have -77.629999999999995  in the JSON would you expect it to return -77.63?    Floats and Double seem to look the same to the library but Float returns -77.6299975 which is a precision loss.  Some reading I did made it sound like the rounding is expected between Doubles and Floats but I have no idea myself.   Any info would be helpful.


Thanks.


jim

[stanl]

Also, I added dmjIsData() and dmjGetType() functions.   The former may be useful in the script you posted as you won't have to retrieve and check the size of a Map, if I read the script correctly.

Also, I am not a "Number" guy so if you have -77.629999999999995  in the JSON would you expect it to return -77.63?   

Thanks.


jim

As for the first part. The script is basic:

   
• retrieve the tree
• process each element and if no sub-elements - retrieve data

Last release I downloaded didn't have isdata() function.

[EDIT]: Just got latest release, adding isdata() function returns same as I already had

As for 2nd part - Unless you have Json typing in the Extender I would expect everything to be returned as string and let the receiving app handle it (as like my Excel does for web hyperlinks). Personally, i could care less about the coordinates being truncated, but if I were using an app to focus on specific lat/long coordinates, I would expect then to be more than 2 decimal places.

[JTaylor]

Maybe I misunderstood the script.   I thought knowing whether something was an Object/Array vs a data element would be helpful and shorten some of the checks you were doing.

jim

As for the first part. The script is basic:

[EDIT]: Just got latest release, adding isdata() function returns same as I already had

[stanl]

Maybe I misunderstood the script. 

But did you run the script? It worked for me across a multiple of states. I have always attempted to contribute to new Extenders or updates with script code [which raised questions] that can be executed and evaluated.


My bad.... I'll just stay away from this.

[JTaylor]

It wasn't an issue of whether it worked or not.   Your script worked fine.   I was simply trying to make what you did easier for you.

jim

[JTaylor]

Numbers should return, as is, now.   Sorry about the communication failure earlier.

jim

[stanl]

Numbers should return, as is, now.   Sorry about the communication failure earlier.

jim

All fine. I was having a bad afternoon.  Below script uses your added dmjIsData() function. Excel still rounds the coordinates but the real data is preserved. I see you noted potential issues with dmjGetValue() by returning data as string. Outside of an optional parameter to indicate a conversion type I hope this doesn't mess up for others using the Extender. I may be working with Json returns of sequences from a remote db. Sequence is a BigInt and I would need them returned as string so they don't end up as exponential. I will eventually mock up data with sequence types and test. Finally, I realized you changed dmjGetTreePath() to just @LF delimiter which makes iteration a snap. There is nothing even close to that I was able to find in Powershell unless you convert Json to XML then use xPath. But I ramble... :-X

Code (WINBATCH) Select Expand



;Winbatch 2020A - Parse Json with new DOMData Extender from Jim Taylor
;uses  WinHttp.WinHttpRequest.5.1  and not System.Net.Http.HttpClient
;      (that now gives mscorlib multiple error message)
;very clumsy error handling as elements as often blank arrays in Json - []
;      ( dmjGetValue cannot process them )
;all credit to Jim's Extender
;Stan Littlefield, January 24, 2021
;======================================================================================================
IntControl(73,1,0,0,0)
Gosub udfs
AddExtender("wbdomdata.dll")
ar = "NC" ;choose a different state to test outut
          ;or make a list dropdown
cUrl = "https://api.weather.gov/alerts/active?area=%ar%"
BoxOpen("Parsing:":cUrl,"Please Wait...")
cJson = Dirscript():ar:".json"
If FileExist(cJson) Then FileDelete(cJson)       
request = Createobject("WinHttp.WinHttpRequest.5.1")
request.Open("GET", cUrl, @False )
request.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded")
request.SetRequestHeader("Accept", "application/json")
request.Send()
cJson = request.ResponseText
request = 0
Boxtext("Iterating Json Data")
dmParse(cJson,@dmJSON)
decimals(18)
cJson = "Element":@TAB:"Fld":@TAB:"Data":@CRLF
path = dmjGetTreePath()
cnt = ItemCount(path,@LF)


For i = 1 To cnt
   element = ItemExtract(i,path,@LF)
   BoxText(element)
   tree  = MapCreate(dmjGetElementMap(element),@TAB,@CR)
   subcnt = ArrInfo(tree,1)
   If subcnt==0
      fld = ItemExtract(ItemCount(element,"/"),element,"/")
      dodata()       
   Endif
Next
BoxText("Moving Data To Excel")
toXLSX()


Exit


:WBERRORHANDLER
geterror()
Terminate(@TRUE,"Error Encountered",errmsg)
Exit


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine


#DefineSubRoutine dodata()
IntControl(73,1,0,0,0)
If dmjIsData(element)
   data = dmjGetValue(element)
   If Strlen(StrTrim(data))>0
     data = StrReplace(data,@TAB," ")
     data = StrReplace(data,@CRLF," ")         
     data = StrReplace(data,@LF," ")
     cJson = cJson:element:@TAB:fld:@TAB:data:@CRLF
   Endif
Endif
Return(1)
:WBERRORHANDLER
IntControl(73,1,0,0,0)
Return(1)
#EndSubRoutine




#DefineSubRoutine toXLSX()
IntControl(73,1,0,0,0)
ClipPut(cJson)
oXL = CreateObject("Excel.Application")
oXL.Visible          = @TRUE  ; change this to @FALSE to run hidden
oXL.ScreenUpdating   = @TRUE  ; if running hidden, change this to @FALSE
oXL.UserControl      = @TRUE
oXL.DisplayAlerts    = @FALSE
oXL.WorkBooks.Add()
BoxShut()
oWS = oXL.ActiveWorkBook.Worksheets(1)
oWS.Activate()
oWS.Name = "Weather Alert_":ar
oWS.Cells(1,1).Select()
oWS.Paste()
oWS.UsedRange.Select()
oXL.Selection.Font.Name = 'Tahoma'
oXL.Selection.Font.Size = 9
oXL.Selection.Font.Bold = @True
oWS.UsedRange.Columns.Autofit()
oWS.ListObjects.Add(:1,oWS.UsedRange, , 1).Name ="Table1"
oWS.Range("Table1[#All]").Select()
oWS.ListObjects("Table1").TableStyle = "TableStyleLight15"
oXL.ActiveWindow.DisplayGridlines = @False


;to create hyperlinks
nRows = oWS.UsedRange.Rows.Count
oWS.Range("C1").Select
oXL.Selection.Interior.ColorIndex = 6
r=2
c=3
While r<nRows+1
   oWS.Cells(r,c).Select()
   v=oWS.Cells(r,c).value
   If StrIndexNC(v,"http",0,@FWDSCAN)
      oWS.Hyperlinks.Add(::Anchor=oXL.Selection,Address="%v%",SubAddress="", TextToDisplay="%v%")
   Endif
   r+=1
Endwhile


oWS.Cells(1,1).Select()
oWS=0
oXL=0
Pause("Data Loaded Into Excel","Save or Close Workbook")
Return(1)
;======================================================================================================
:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
;======================================================================================================
:CANCEL
Display(2,"Thank you","Goodbye...")
Exit
#EndSubRoutine


Return


[JTaylor]

Looks good.   You did seem a bit grumpy :)    Glad to hear it is working well for you.    I think overall making the number change is for the best since I want people to be certain of what they are receiving and I also don't have to worry about HUGE numbers being a problem either.   I am still going to try to fix it so the GetType() works correctly but since people can test on the WinBatch side it isn't a critical problem.


Jim

[stanl]

Script below is simple Json - gets info on ip address - and I repeated to toxlsx() udf. This is the kind of Json where you could use the 'fld' column I create as a table column name, run multiple requests and place data into a db table (or transpose in Excel for a table). I really like how quickly and smoothly your Extender can investigate any given Json - and it is important that how and what to do with it is a matter of user preference.

Code (WINBATCH) Select Expand



;Winbatch 2020A - Parse Json with new DOMData Extender from Jim Taylor
;Sample - query url that returns Json for ip address
;Stan Littlefield, January 25, 2021
;======================================================================================================
IntControl(73,1,0,0,0)
Gosub udfs
AddExtender("wbdomdata.dll")
ip = "51.107.59.180" ;ip to search to get owner and location
cUrl = "https://ipinfo.io/%ip%/json"
BoxOpen("Parsing:":cUrl,"Please Wait...")
TabName = "ipinfo"   
request = Createobject("WinHttp.WinHttpRequest.5.1")
request.Open("GET", cUrl, @False )
request.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded")
request.SetRequestHeader("Accept", "application/json")
request.Send()
cJson = request.ResponseText
request = 0
Boxtext("Iterating Json Data")
dmParse(cJson,@dmJSON)
decimals(18)
cJson = "Element":@TAB:"Fld":@TAB:"Data":@CRLF
path = dmjGetTreePath()
cnt = ItemCount(path,@LF)


For i = 1 To cnt
   element = ItemExtract(i,path,@LF)
   BoxText(element)
   tree  = MapCreate(dmjGetElementMap(element),@TAB,@CR)
   subcnt = ArrInfo(tree,1)
   If subcnt==0
      fld = ItemExtract(ItemCount(element,"/"),element,"/")
      dodata()       
   Endif
Next
BoxText("Moving Data To Excel")
toXLSX()


Exit


:WBERRORHANDLER
geterror()
Terminate(@TRUE,"Error Encountered",errmsg)
Exit


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine


#DefineSubRoutine dodata()
IntControl(73,1,0,0,0)
If dmjIsData(element)
   data = dmjGetValue(element)
   If Strlen(StrTrim(data))>0
     data = StrReplace(data,@TAB," ")
     data = StrReplace(data,@CRLF," ")         
     data = StrReplace(data,@LF," ")
     cJson = cJson:element:@TAB:fld:@TAB:data:@CRLF
   Endif
Endif
Return(1)
:WBERRORHANDLER
IntControl(73,1,0,0,0)
Return(1)
#EndSubRoutine




#DefineSubRoutine toXLSX()
IntControl(73,1,0,0,0)
ClipPut(cJson)
oXL = CreateObject("Excel.Application")
oXL.Visible          = @TRUE  ; change this to @FALSE to run hidden
oXL.ScreenUpdating   = @TRUE  ; if running hidden, change this to @FALSE
oXL.UserControl      = @TRUE
oXL.DisplayAlerts    = @FALSE
oXL.WorkBooks.Add()
BoxShut()
oWS = oXL.ActiveWorkBook.Worksheets(1)
oWS.Activate()
oWS.Name = TabName
oWS.Cells(1,1).Select()
oWS.Paste()
oWS.UsedRange.Select()
oXL.Selection.Font.Name = 'Tahoma'
oXL.Selection.Font.Size = 9
oXL.Selection.Font.Bold = @True
oWS.UsedRange.Columns.Autofit()
oWS.ListObjects.Add(:1,oWS.UsedRange, , 1).Name ="Table1"
oWS.Range("Table1[#All]").Select()
oWS.ListObjects("Table1").TableStyle = "TableStyleLight15"
oXL.ActiveWindow.DisplayGridlines = @False


;to create hyperlinks
nRows = oWS.UsedRange.Rows.Count
oWS.Range("C1").Select
oXL.Selection.Interior.ColorIndex = 6
r=2
c=3
While r<nRows+1
   oWS.Cells(r,c).Select()
   v=oWS.Cells(r,c).value
   If StrIndexNC(v,"http",0,@FWDSCAN)
      oWS.Hyperlinks.Add(::Anchor=oXL.Selection,Address="%v%",SubAddress="", TextToDisplay="%v%")
   Endif
   r+=1
Endwhile


oWS.Cells(1,1).Select()
oWS=0
oXL=0
Pause("Data Loaded Into Excel","Save or Close Workbook")
Return(1)
;======================================================================================================
:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
;======================================================================================================
:CANCEL
Display(2,"Thank you","Goodbye...")
Exit
#EndSubRoutine


Return


[stanl]

Just had a thought. My script tends to use your Extender to reduce Json return into discreet fld:value data, ideal for Excel or a db table - have to look into a transform() function to configure from row->col into col->row....

[JTaylor]

So the name-value map but in a row-minor format?

Jim

[stanl]

So the name-value map but in a row-minor format?

Jim

well, let's just say csv format, the secret getting only xpath with value as opposed to xpath with sub-path.

   
• without even going the Excel route you can capture main 'fields' / 'columns' for db processing
• of course, often the element may not exist or be relevant per a certain 'run' so process only what is necessary.

[JTaylor]

Thought I had something like that in place already but that is in the XML and HTML camp.   Would a function like the Name-Value Map but in CSV and Array form help you along your path?  I thought I had already done that anyway and it will probably get done no matter what you say but want to make it as easy on you as I can so want to take any other factors into account.

Jim

[stanl]

Thought I had something like that in place already but that is in the XML and HTML camp.   Would a function like the Name-Value Map but in CSV and Array form help you along your path?  I thought I had already done that anyway and it will probably get done no matter what you say but want to make it as easy on you as I can so want to take any other factors into account.

Jim

Hard to say. If you have a function to parse just paired data, i.e. element>value, then no problem. My experience with Json data is that it

   
• needs to be reduced
• pairs can be evaluated
• certain pairs may not exist per next run of app to obtain Json.     

The weather alert Json I first suggested indicates returned data does not always fall into preset field/data...

[JTaylor]

Understood.   I limit people's expectations on the other CSV/Array functions.  All dependent on the data.

Upside is, if they are all similar blocks they can be concatenated easily enough.   Again, just depends on the dataset.

Jim

[JTaylor]

Will post Extender update momentarily.  Needed the colorizer here.

If you don't think this is cool I can't help you :)

Jim

Code (winbatch) Select Expand




AddExtender(DirScript():"wbdomdata.dll")

rnode = dmParseFile("CA.json",@dmJSON)

dmjBuildInit(1)  ;Initializes Object
header = @TRUE
For x = 0 to 100000
  cresp = dmjBuildCSV(1,"/features/%x%/properties",header,@FALSE)
  header = @FALSE
  If cresp == "***EOF***" Then x = 500000
Next


;TO GET THE CSV

    csv = dmjBuildGet(1)

;AND IF YOU WANT AN ARRAY

    arr = snArrayFromStrCSV(csv, 0, @TAB, 0, 0)
    ArrayFilePutCsv ("build.txt",arr,@TAB)

Message(ArrInfo(arr,1):" X ":ArrInfo(arr,2),arr[0,0])

dmjBuildInit(1)  ;Clears Value & Memory





[JTaylor]

Actually may be a few minutes.  Forgot I had a couple more things to wrap up.  Will post when ready.

Jim

[JTaylor]

Posted...

    http://www.jtdata.com/anonymous/DomData.zip


jim

[kdmoyers]

I suddenly have big json parsing project dumped in my lap.  Not urgent, but not optional.
Your efforts here are much appreciated. -Kirby

[stanl]

If you don't think this is cool I can't help you :)

It is. But did get failure. Script below is similar to others, this time using the ip adress url. The code 'reduces' the Json to a tab-delimted list of fld=>value [I took out element for the test] - which goes into Excel fine. In Excel one can copy and transform the 2 columns but I would like to perform the transformation prior to Paste().  I think if I create an array rather than a list I can use the WorkSheetFunction() for Transform prior to inserting into Excel - so has nothing to do with your Extender. Again, I'm pursuing more generic functionality.

Code (WINBATCH) Select Expand



;Winbatch 2020A - Parse Json with new DOMData Extender from Jim Taylor
;Testing CSV functions in Json portion of Extender
;Stan Littlefield, January 24, 2021
;======================================================================================================
IntControl(73,1,0,0,0)
Gosub udfs
AddExtender("wbdomdata.dll")
ar = "NC" ;choose a different state to test outut
          ;or make a list dropdown
;cUrl = "https://api.weather.gov/alerts/active?area=%ar%"
cUrl = "https://ipinfo.io/51.107.59.180/json"
BoxOpen("Parsing:":cUrl,"Please Wait...")
;cJson = Dirscript():ar:".json"
;If FileExist(cJson) Then FileDelete(cJson)       
request = Createobject("WinHttp.WinHttpRequest.5.1")
request.Open("GET", cUrl, @False )
request.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded")
request.SetRequestHeader("Accept", "application/json")
request.Send()
cJson = request.ResponseText
request = 0
Boxtext("Iterating Json Data")
dmParse(cJson,@dmJSON)
decimals(18)
cJson = "Fld":@TAB:"Data":@CRLF
path = dmjGetTreePath()
cnt = ItemCount(path,@LF)


For i = 1 To cnt
   element = ItemExtract(i,path,@LF)
   BoxText(element)
   tree  = MapCreate(dmjGetElementMap(element),@TAB,@CR)
   subcnt = ArrInfo(tree,1)
   If subcnt==0
      fld = ItemExtract(ItemCount(element,"/"),element,"/")
      dodata()       
   Endif
Next
BoxText("Moving Data To Excel")


;uncomment below and will get another Assertation error


;transform data
;dmjBuildInit(1) ;Initializes Object
;header = @TRUE
;cJson = dmjBuildCSV(1,cJson,header,@FALSE)
;cJson= dmjBuildGet(1)


toXLSX()


Exit


:WBERRORHANDLER
geterror()
Terminate(@TRUE,"Error Encountered",errmsg)
Exit


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine


#DefineSubRoutine dodata()
IntControl(73,1,0,0,0)
If dmjIsData(element)
   data = dmjGetValue(element)
   If Strlen(StrTrim(data))>0
     data = StrReplace(data,@TAB," ")
     data = StrReplace(data,@CRLF," ")         
     data = StrReplace(data,@LF," ")
     cJson = cJson:fld:@TAB:data:@CRLF
   Endif
Endif
Return(1)
:WBERRORHANDLER
IntControl(73,1,0,0,0)
Return(1)
#EndSubRoutine




#DefineSubRoutine toXLSX()
IntControl(73,1,0,0,0)
ClipPut(cJson)
oXL = CreateObject("Excel.Application")
oXL.Visible          = @TRUE  ; change this to @FALSE to run hidden
oXL.ScreenUpdating   = @TRUE  ; if running hidden, change this to @FALSE
oXL.UserControl      = @TRUE
oXL.DisplayAlerts    = @FALSE
oXL.WorkBooks.Add()
BoxShut()
oWS = oXL.ActiveWorkBook.Worksheets(1)
oWS.Activate()
oWS.Name = "Weather Alert_":ar
oWS.Cells(1,1).Select()
oWS.Paste()
oWS.UsedRange.Select()
oXL.Selection.Font.Name = 'Tahoma'
oXL.Selection.Font.Size = 9
oXL.Selection.Font.Bold = @True
oWS.UsedRange.Columns.Autofit()
oWS.ListObjects.Add(:1,oWS.UsedRange, , 1).Name ="Table1"
oWS.Range("Table1[#All]").Select()
oWS.ListObjects("Table1").TableStyle = "TableStyleLight15"
oXL.ActiveWindow.DisplayGridlines = @False


;to create hyperlinks
nRows = oWS.UsedRange.Rows.Count
oWS.Range("B1").Select
oXL.Selection.Interior.ColorIndex = 6
r=2
c=2
While r<nRows+1
   oWS.Cells(r,c).Select()
   v=oWS.Cells(r,c).value
   If StrIndexNC(v,"http",0,@FWDSCAN)
      oWS.Hyperlinks.Add(::Anchor=oXL.Selection,Address="%v%",SubAddress="", TextToDisplay="%v%")
   Endif
   r+=1
Endwhile


oWS.Cells(1,1).Select()
oWS=0
oXL=0
Pause("Data Loaded Into Excel","Save or Close Workbook")
Return(1)
;======================================================================================================
:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
;======================================================================================================
:CANCEL
Display(2,"Thank you","Goodbye...")
Exit
#EndSubRoutine


Return


[JTaylor]

Sorry about that...after looking at the Help I realized I failed to include some critical information.  Even failed to include info on one of the parameters.  Below is an update.

The BuildCSV() is intended as a processing and storage function rather than just a storage function. 

That that leads into a question I thought of last night...Would having a way to store data like that be useful?   It would be globally available while the script was running.   You could add to it or just replace it.   Not sure if that would serve any practical purpose or not but thought I would ask.


Jim


dmjBuildCSV(Build_Id, Path, IncludeHeader, ObjectPlaceHolder)

Retrieves a CSV build object.

Build_Id:  A number 1 - 10.   This means you can have up to 10 active builds at any one time.

Path:  Path to Object/Array to process into CSV data.

IncludeHeader (optional):    @TRUE/FALSE  Defaults to @FALSE
                                                   You would normally only set to @TRUE for the first call.

ObjectPlaceHolder (optional):  @TRUE/FALSE   Defaults to @FALSE
                                                       Will insert the word "object" or "array" in their respective columns.

Returns: 1 if data is found.  Returns "***EOF***" if node doesn't exist.   See Example to see how this is helpful.



Comments:

Be sure to run BuildInit() before starting this process.

This functions takes the submitted Element (Path) and transforms the data into a CSV String and combines it with previous calls.  Once complete you can retrieve the entire CSV string.  As long as the script doesn't close and you do not call BuildInit(), the data will remain, globally, available to your script.   You can, at any point, continue to add more data to it as well.

Use the snArrayFromStrCSV() function to obtain an array.

[JTaylor]

They were just waiting on me to get this ready for you :)

Let me know if you have any "It would be great if..." thoughts while working on your project.

Also, don't forget, you can do JSON stuff through the SQLite Extender.   Depending on what you are doing that might have some benefits as well.

Jim

I suddenly have big json parsing project dumped in my lap.  Not urgent, but not optional.
Your efforts here are much appreciated. -Kirby

[stanl]

Sorry about that...
Path:  Path to Object/Array to process into CSV data.

That is a central point. The function is built around a directive to a path in the Json, so you cannot run the functions unless you already know the path.  In my simple scripts, I am reducing the Json to probable path and/or field:data pairs.  So, if the parameter would accept my reduced data as either a delimited list or 2-dimensional array it would achieve the same as Excel's Transform().


One of the greatest values of WB is to create scripts that can write script output based on generic parsing rules. As early as 2005 I had scripts that stored udfs as access or fabricated recordset memo fields, then a main script which would access and load them depending upon their need with a simple Call {wbc file} as exported from the table field. I am facing up to 160 different Json outputs from a Hana DB API.  My plan is to use the Extender and my reduction code to create and store structural udfs based on each Json file. Then create a Main script that uses a itembox or fancy report/gridview to determine based on the API call, the specific udf functions to call which handle the pairs. 


As the main script will be an executable, only the stored udfs would need to be overwritten for any additions or changes.

[JTaylor]

Okay.  That took about 3 times as long as it should but I think I accomplished what you wanted and what I wanted.

Take a look at that dmStore...() functions.

    http://www.jtdata.com/anonymous/DomData.zip


Jim

[stanl]

The problem is Excel:
I tested your Extender with some actual Json that returns Sequences. First, I have to say that working with my script that aims to reduce elements to pairs is hands above anything tried with Powershell [unless you use Python]. The data returns array data up to 100 arrays * 60 fields at a time - your Extender iterates to pairs in seconds :) :) :)


The sample scripts I attached placed the data into Excel. Originally I mentioned an issue with rounding for lat/long values for the weather url - you fixed and data is returned as string. However: my test Json returned a sequence 32651097298436192 and the best Excel could interpret was 32651097298436100. Of course, if I knew in advance  :o  but want to keep the iteration generic. Plan-B already in motion and thanks again for your hard work!

[JTaylor]

Glad to hear it is working well.   I am still trying to figure out a way to report a valid Type for the numbers.

As I told Kirby, if you have any more "It would be great if it could...." thoughts let me know.


Jim

[JTaylor]

Not sure if it will be helpful but I have tweaked dmjGetType() so it now will report better.   See Help for more info and one potential issue.

I also added a dmjGetLength() function that will return the length of the string representation of any field/object/array. 


     http://www.jtdata.com/anonymous/DomData.zip

Jim

[stanl]

I also added a dmjGetLength() function that will return the length of the string representation of any field/object/array. 

Script below uses MI.json [attached - it has .txt in order to attach here, either rename to MI.json or modify script]. Your jmjGetLength() function used and data sent to Reportview rather than Excel

Code (WINBATCH) Select Expand



IntControl(73,1,0,0,0)
Gosub udfs
AddExtender("wbdomdata.dll")
BoxOpen("Parsing Json Data","Please Wait")
cJson = Dirscript():"Mi.json"
dmParse(FileGet(cJson),@dmJSON)
decimals(18)
jOut = Dirscript():"jOut.csv"
If FileExist(jOut) Then FileDelete(jOut)
cData = "Element":@TAB:"Fld":@TAB:"Data":@TAB:"Size":@CRLF




path = dmjGetTreePath()
cnt = ItemCount(path,@LF)


For i = 1 To cnt
   element = ItemExtract(i,path,@LF)
   BoxText(element)
   tree  = MapCreate(dmjGetElementMap(element),@TAB,@CR)
   subcnt = ArrInfo(tree,1)
   If subcnt==0
      fld = ItemExtract(ItemCount(element,"/"),element,"/")
      data = dmjGetValue(element)
      If Strlen(StrTrim(data))>0
         data = StrReplace(data,@TAB," ")
         data = StrReplace(data,@CRLF," ")         
         data = StrReplace(data,@LF," ")
         nSize = dmjGetLength(element)
         cNext = element:@TAB:fld:@TAB:data:@TAB:nSize:@CRLF
         cData = cData:cNext
      Endif
   Endif
Next


Fileput(jOut,cData)
jArr = ArrayFileGetCSV(jOut, 0,@TAB, 0, 0)
MyDialogFormat=`WWWDLGED,6.2`
MyDialogCaption=`Json Tree`
MyDialogX=9999
MyDialogY=9999
MyDialogWidth=560
MyDialogHeight=102
MyDialogNumControls=002
MyDialogProcedure=``
MyDialogFont=`Microsoft Sans Serif|5632|70|34`
MyDialogTextColor=`DEFAULT`
MyDialogBackground=`DEFAULT,255|255|255`
MyDialogConfig=0


MyDialog001=`219,075,090,012,PUSHBUTTON,"PushButton_OK",DEFAULT,"OK",1,20,@csDefButton,DEFAULT,DEFAULT,"128|255|0"`
MyDialog002=`007,005,540,062,REPORTVIEW,"ReportView_1",jArr,DEFAULT,DEFAULT,10,@csAsort|@csFirstHeader|@csFullSel|@csGrid|@csSingleSel,"Microsoft Sans Serif|6144|70|34","255|255|0","0|0|0"`


ButtonPushed=Dialog("MyDialog")


Exit


:WBERRORHANDLER
geterror()
Terminate(@TRUE,"Error Encountered",errmsg)
Exit


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine
Return


[JTaylor]

Nice.   The Excel stuff doesn't work for me (apparently Office365 is the issue) so I could see the end result of this one.

After thinking about it I wondered if StrLen() would have been enough but maybe there are times when knowing ahead of time would be useful.   Sometimes I catch myself reinventing the wheel :)


Jim

[stanl]

Here is a cute Json return:  https://worldtimeapi.org/api/ip

Code (WINBATCH) Select Expand



IntControl(73,1,0,0,0)
Gosub udfs
AddExtender("wbdomdata.dll")
BoxOpen("Parsing Json Data","Please Wait")
cUrl = "https://worldtimeapi.org/api/ip"
BoxOpen("Parsing:":cUrl,"Please Wait...")
request = Createobject("WinHttp.WinHttpRequest.5.1")
request.Open("GET", cUrl, @False )
request.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded")
request.SetRequestHeader("Accept", "application/json")
request.Send()
cJson = request.ResponseText
request = 0
Boxtext("Iterating Json Data")
dmParse(cJson,@dmJSON)
decimals(18)
cHdr = ""
cData = ""
path = dmjGetTreePath()
jOut = Dirscript():"jOut.csv"
If FileExist(jOut) Then FileDelete(jOut)
cData = "Element":@TAB:"Fld":@TAB:"Data":@TAB:"Size":@CRLF




path = dmjGetTreePath()
cnt = ItemCount(path,@LF)


For i = 1 To cnt
   element = ItemExtract(i,path,@LF)
   BoxText(element)
   tree  = MapCreate(dmjGetElementMap(element),@TAB,@CR)
   subcnt = ArrInfo(tree,1)
   If subcnt==0
      fld = ItemExtract(ItemCount(element,"/"),element,"/")
      data = dmjGetValue(element)
      If Strlen(StrTrim(data))>0
         data = StrReplace(data,@TAB," ")
         data = StrReplace(data,@CRLF," ")         
         data = StrReplace(data,@LF," ")
         nSize = dmjGetLength(element)
         cNext = element:@TAB:fld:@TAB:data:@TAB:nSize:@CRLF
         cData = cData:cNext
      Endif
   Endif
Next


Fileput(jOut,cData)
jArr = ArrayFileGetCSV(jOut, 0,@TAB, 0, 0)
MyDialogFormat=`WWWDLGED,6.2`
MyDialogCaption=`Json Tree`
MyDialogX=9999
MyDialogY=9999
MyDialogWidth=560
MyDialogHeight=102
MyDialogNumControls=002
MyDialogProcedure=``
MyDialogFont=`Microsoft Sans Serif|5632|70|34`
MyDialogTextColor=`DEFAULT`
MyDialogBackground=`DEFAULT,255|255|255`
MyDialogConfig=0


MyDialog001=`219,075,090,012,PUSHBUTTON,"PushButton_OK",DEFAULT,"OK",1,20,@csDefButton,DEFAULT,DEFAULT,"128|255|0"`
MyDialog002=`007,005,540,062,REPORTVIEW,"ReportView_1",jArr,DEFAULT,DEFAULT,10,@csFirstHeader|@csFullSel|@csGrid|@csSingleSel,"Microsoft Sans Serif|6144|70|34","255|255|0","0|0|0"`


ButtonPushed=Dialog("MyDialog")


Exit


:WBERRORHANDLER
geterror()
Terminate(@TRUE,"Error Encountered",errmsg)
Exit


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine
Return


[stanl]

Possible issue:  in the last script I posted, I noticed the ReportView duplicated all elements.  I then just checked the path returned by dmjGetTreePath() - and it had duplicates, even though the raw Json didn't.  Might be something to look at.


[EDIT]: this seems to occur in Json, without a 'parent', i.e. only a set of pairs are exported.... dunno ??? ???

[JTaylor]

Interesting.  I could see that being a useful site. 

Jim

Here is a cute Json return:  https://worldtimeapi.org/api/ip

[stanl]

Interesting.  I could see that being a useful site. 

Jim

Here is a cute Json return:  https://worldtimeapi.org/api/ip

as long as dmjGetTreePath() doesn't duplicate  ;D