DOMdata Extender - Json parsing to Excel

[JTaylor]

Didn't see it in the Time script.

Jim

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]

...or maybe I do...give me a minute.

Jim

[stanl]

...or maybe I do...give me a minute.

Jim

just add uncommented lines:

Code (WINBATCH) Select Expand



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


;add these
Message("Raw Json",cJson)
Message(cnt,path)
Exit ; so just show the issue


[JTaylor]

This should fix it.  It was a Global variable oversight.   Surprised that one of us hadn't noticed that sooner.  It was because you made two calls to dmjGetTreePath() in the same script.  Obviously that shouldn't matter but  that is what, fortunately, brought it to light.

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

Jim

[stanl]

It was because you made two calls to dmjGetTreePath() in the same script.

My bad. I was testing what I was testing... working between your Extender and PS :-[ .  The whole idea of a parent in a Json array came up with Json I will be working on which outputs a json array of columns from a basic SELECT via an API.


But for fun: [run in Powershell ISE]

Code Select Expand



$url = "https://worldtimeapi.org/api/ip"
#$url = "https://ipinfo.io/json"
$cJson = Invoke-RestMethod -Uri $url  | ConvertTo-Json | ConvertFrom-Json
$cCSV =$cJson | ConvertTo-Csv -NoTypeInformation
$outputCsv = $cCsv | % {$_ -replace '"',''} #may not always be safe, but generally removes quotes
$outputCsv




[td]

Thanks for posting the link to the site.  Have a need for the information provided by the API.

[JTaylor]

Not at all.  As I said, it shouldn't have mattered but I am very glad you did what you did so we caught that and I could fix it.

Jim

My bad. I was testing what I was testing... working between your Extender and PS :-[ .  The whole idea of a parent in a Json array came up with Json I will be working on which outputs a json array of columns from a basic SELECT via an API.

[stanl]

Thanks for posting the link to the site.  Have a need for the information provided by the API.

If you are talking about TimeZones the site has caveats that it is basically GIGO. The timestamps are fun and I guess we have to wait for the dst columns to be filled in.

[JTaylor]

Another thing you might appreciate Stan is I just added a count response to [hopefully] all the countable functions such as maps, CSV, delimited lists, etc.   So, for dmjXXX() functions the

    @dmjLastCount

variable will contain the count of whatever list was last returned.   Should save you a step.   I should have thought of that sooner but forgot I can initialize variables directly into the WinBatch environment without having to pass them back in a return.

Jim         

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

[stanl]

But for fun: [run in Powershell ISE]

Code Select Expand



$url = "https://worldtimeapi.org/api/ip"
#$url = "https://ipinfo.io/json"
$cJson = Invoke-RestMethod -Uri $url  | ConvertTo-Json | ConvertFrom-Json
$cCSV =$cJson | ConvertTo-Csv -NoTypeInformation
$outputCsv = $cCsv | % {$_ -replace '"',''} #may not always be safe, but generally removes quotes
$outputCsv




[EDIT]: easy to set optional delimiter and still remove " from output

Code Select Expand



#uncomment url to test
$dl = "|" # "`t" for Tab
$url = "https://worldtimeapi.org/api/ip"
#$url = "https://ipinfo.io/json"
$Outfile = "c:\temp\jtest.txt"
$cJson = Invoke-RestMethod -Uri $url  | ConvertTo-Json | ConvertFrom-Json
$cCSV =$cJson | ConvertTo-Csv -NoTypeInformation -Delimiter $dl | % {$_ -replace '"',''}
$cCSV #just display, comment if you want
#$cCSV | Out-File $OutFile -Encoding utf8 -Force  #creates file




[td]

Thanks for posting the link to the site.  Have a need for the information provided by the API.

If you are talking about TimeZones the site has caveats that it is basically GIGO. The timestamps are fun and I guess we have to wait for the dst columns to be filled in.

Yes, I was referring to the TimeZones site and I will make sure I don't provide garbage.