Help modifying JsonToAHK to include headers Topic is solved

[AHKStudent]

If you run the script (below) you will get the following data

Code: Select all

2021-06-01,books,2
2021-02-01,book,28
2021-01-01,book three,00
2021-08-01,vols,30

I use JsonToAHK by @teadrinker (included below) to take data and build a report. Currently after I generate the data I manually go to the spreadsheet and enter the titles and it looks like:

Code: Select all

date,title,chap ; currently add this manually
2021-06-01,books,2
2021-02-01,book,28
2021-01-01,book three,00
2021-08-01,vols,30

Since the keys are the headers, I figured that the JsonToAHK function can be modified to get the headers as well from the keys. Help or pointers to what to modify to achieve this would be highly appreciated.

Sample script

Code: Select all

jsonData = 
(
{
   "err":"0",
   "data":[
      {
         "date":"2021-06-01",
         "title":"books",
         "chap":"2"
      },
      {
         "date":"2021-02-01",
         "title":"book",
         "chap":"28"
      },
      {
         "date":"2021-01-01",
         "title":"book three",
         "chap":"00"
      },
      {
         "date":"2021-08-01",
         "title":"vols",
         "chap":"30"
      }
   ]
}
)
obj := JsonToAHK(jsonData)
loop, % obj.data.length()
{
	generateData .= obj.data[a_index].date "," obj.data[a_index].title "," obj.data[a_index].chap "`n"
 }
MsgBox, % RTrim(generateData,"`n")
; in real life here it would fileappend and create a csv for this demo I am using a msgbox
ExitApp
JsonToAHK(json, rec := false) {
   static doc := ComObjCreate("htmlfile")
         , __ := doc.write("<meta http-equiv=""X-UA-Compatible"" content=""IE=9"">")
         , JS := doc.parentWindow
   if !rec
      obj := JsonToAHK(JS.eval("(" . json . ")"), true)
   else if !IsObject(json)
      obj := json
   else if json.toString() != "[object Object]" {
      obj := []
      Loop % json.length
         obj.Push( JsonToAHK(json[A_Index - 1], true) )
   }
   else {
      obj := {}
      keys := JS.Object.keys(json)
      Loop % keys.length {
         k := keys[A_Index - 1]
         obj[k] := JsonToAHK(json[k], true)
      }
   }
   Return obj 
}

[william_ahk]

Use for-loop to get keys

Code: Select all

key_count := obj.data[1].count()
for key, value in obj.data[1]
	generateData .= key (A_Index != key_count ? "," : "`n")

[AHKStudent]

Use for-loop to get keys

Code: Select all

key_count := obj.data[1].count()
for key, value in obj.data[1]
	generateData .= key (A_Index != key_count ? "," : "`n")

This solution gets the keys but not in the right order because AHK returns it in alphabetical order.

If it's done within the function, it will be in the right order

[Chunjee]

May be helpful to know that ahk object keys are stored alphabetically. Therefor enumerating an object with "chap", "data, "title" keys will be returned in that order.

[AHKStudent]

May be helpful to know that ahk object keys are stored alphabetically. Therefor enumerating an object with "chap", "data, "title" keys will be returned in that order.

Yup, this is why if there is a way to get it when the function is creating the object it would return it in the right order even if it gives me all the keys and I must clean it up for example, remove the err and data key, it would be better than what I have now

[teadrinker]

Maybe, like this:

Code: Select all

jsonData = 
(
{
   "err":"0",
   "data":[
      {
         "date":"2021-06-01",
         "title":"books",
         "chap":"2"
      },
      {
         "date":"2021-02-01",
         "title":"book",
         "chap":"28"
      },
      {
         "date":"2021-01-01",
         "title":"book three",
         "chap":"00"
      },
      {
         "date":"2021-08-01",
         "title":"vols",
         "chap":"30"
      }
   ]
}
)
script =
(
   var obj = %jsonData%;
   var headers = '', lines = '';
   
   for (var key in obj.data[0])
      headers += (headers == '' ? '' : ',') + key;
   
   obj.data.forEach(function(item) {
      var values = '';
      for (var key in item)
         values += (values == '' ? '' : ',') + item[key]
      lines += '\r\n' + values;
   });
   headers + lines;
)
MsgBox, % GetJS().eval(script)

GetJS() {
   static Doc, JS
   if !Doc {
      Doc := ComObjCreate("htmlfile")
      Doc.write("<meta http-equiv=""X-UA-Compatible"" content=""IE=9"">")
      JS := Doc.parentWindow
      (Doc.documentMode < 9 && JS.execScript())
   }
   Return JS
}

[AHKStudent]

Maybe, like this:

Code: Select all

jsonData = 
(
{
   "err":"0",
   "data":[
      {
         "date":"2021-06-01",
         "title":"books",
         "chap":"2"
      },
      {
         "date":"2021-02-01",
         "title":"book",
         "chap":"28"
      },
      {
         "date":"2021-01-01",
         "title":"book three",
         "chap":"00"
      },
      {
         "date":"2021-08-01",
         "title":"vols",
         "chap":"30"
      }
   ]
}
)
script =
(
   var obj = %jsonData%;
   var headers = '', lines = '';
   
   for (var key in obj.data[0])
      headers += (headers == '' ? '' : ',') + key;
   
   obj.data.forEach(function(item) {
      var values = '';
      for (var key in item)
         values += (values == '' ? '' : ',') + item[key]
      lines += '\r\n' + values;
   });
   headers + lines;
)
MsgBox, % GetJS().eval(script)

GetJS() {
   static Doc, JS
   if !Doc {
      Doc := ComObjCreate("htmlfile")
      Doc.write("<meta http-equiv=""X-UA-Compatible"" content=""IE=9"">")
      JS := Doc.parentWindow
      (Doc.documentMode < 9 && JS.execScript())
   }
   Return JS
}

works fast and great tried with big file with many columns thank you

[william_ahk]

@teadrinker Great idea!

[teadrinker]

Yeah, for ... in returns object keys in the order they were added.

[aifritz]

Hi all,
I've tried this wonderful script from teadrinker with another JSON file, but get an error from website:
Line: 24 Error: The property "0" of an undefined or null reference cannot be retrieved.

Does anyone have an idea what could be wrong here? Many thanks!

• Error: 0x80020101 -
  
  Specifically: eval
  ...
  headers + lines;
  ---> 041: MsgBox,GetJS().eval(script)
  042: ExitApp
  ...
  Continue running the script?

Code: Select all

jsonData =
(
{
  "totalSize" : 2,
  "done" : true,
  "records" : [ {
    "attributes" : {
      "type" : "Account",
      "url" : "/services/data/v52.0/sobjects/Account/00108000009fsaEAAQ"
    },
    "Id" : "00108000009fsaEAAQ",
    "Name" : "Name1",
    "CG_Status__c" : "Active"
  }, {
    "attributes" : {
      "type" : "Account",
      "url" : "/services/data/v52.0/sobjects/Account/00108000009fsaFAAQ"
    },
    "Id" : "00108000009fsaFAAQ",
    "Name" : "Name2",
    "CG_Status__c" : "Active"
  } ]
}
)
script =
(
   var obj = %jsonData%;
   var headers = '', lines = '';

   for (var key in obj.data[0])
      headers += (headers == '' ? '' : ',') + key;

   obj.data.forEach(function(item) {
      var values = '';
      for (var key in item)
         values += (values == '' ? '' : ',') + item[key]
      lines += '\r\n' + values;
   });
   headers + lines;
)
MsgBox, % GetJS().eval(script)

GetJS() {
   static Doc, JS
   if !Doc {
      Doc := ComObjCreate("htmlfile")
      Doc.write("<meta http-equiv=""X-UA-Compatible"" content=""IE=9"">")
      JS := Doc.parentWindow
      (Doc.documentMode < 9 && JS.execScript())
   }
   Return JS
}
exitapp

[aifritz]

I've found it with the help of ChatGPT

Another possibility is that the jsonData variable is being defined correctly, but the object it contains does not have a property called data. If this is the case, then you will need to modify the script to access the correct property of the jsonData object.