Jump to content
Skysnake

JSON as AutoIt data type

Recommended Posts

Skysnake
Posted (edited)

I know how JSON works. However JSON data is typically received from web based servers. 

 

My question is:

Has anyone considered to use, or currently actively using JSON as an internal data container? 

Internal here as in an ordinary AutoIt data type, generated, populated and manipulated within an AutoIt script, without any external data sources. 

The JSON and BinaryCall UDFs work well and appear the de facto defaults. Are there any thoughts on using JSON for internal variable values instead of the typical array? 

Also, there can be situations where such a JSON collection contains an array and it will have to be parsed using typical array functions. 

Currently I am working on a small project where I have to keep and use various identifiers from different sources. There are several ideas in this regard, some involving SQL child tables and arrays stored with keys (maps), which lead me to think that JSON may be a more suitable data container. It readily shrinks and stretches, allowing for dynamic data growth.  JSONs ability to add new repeating data groups on the fly makes it particularly powerful and appealing. 

Ideas and suggestions welcome

Skysnake

Edited by Skysnake

Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
jchd

FYI SQLite JSON1 functions work like a charm. I know you're using SQLite a lot so this may be a decent solution. Just store your JSON source in a temp table and peek-poke in from there. You can as well issue a direct query without having to store it first in case the desired operation (creation, extraction, modification) isn't repetitive.

  • Like 1

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Skysnake

:)

It's funny how we miss the obvious. Yes, you are right about SQLite, and I often refer to the SQlite JSON documentation... For some reason the AutoIt-SQLite-JSON link has passed me by :>

That's also an easy error checking device :)

Will definitely have a look. Thanks for the reminder 


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
argumentum
5 hours ago, jchd said:

Just store your JSON source in a temp table and peek-poke in from there

...just saw this and google it ...and ... would you paste some simple code to give me a clue of how to go about this.  =) 

Share this post


Link to post
Share on other sites
TheXman
19 minutes ago, argumentum said:

give me a clue of how to go about this

If you are looking for SQLite syntax examples, then you can start here:

https://www.sqlite.org/json1.html

Share this post


Link to post
Share on other sites
argumentum
1 minute ago, TheXman said:

If you are looking for SQLite syntax examples

au3 example. I know those

Share this post


Link to post
Share on other sites
TheXman

Here's a very simple example of validating that json is well-formed and extracting a value from well-formed JSON:

#include <SQLite.au3>

example()

Func example()
        Const $TEST_JSON = _
                           '{' & _
                           '    "manifest_version": 2,' & _
                           '    "name": "Google Apps Certification app",' & _
                           '    "description": "Link to Google Apps Certification website",' & _
                           '    "version": "1.1",' & _
                           '    "icons": {' & _
                           '        "128": "128.png"' & _
                           '    },' & _
                           '    "app": {' & _
                           '        "urls": [' & _
                           '            "http://certification.googleapps.com/app-info/"' & _
                           '        ],' & _
                           '        "launch": {' & _
                           '            "web_url": "http://certification.googleapps.com/"' & _
                           '        }' & _
                           '    },' & _
                           '    "permissions": [' & _
                           '        "unlimitedStorage",' & _
                           '        "notifications"' & _
                           '    ]' & _
                           '}'

        Const $SQLITE_DLL = "c:\program files\sqlite\sqlite3.dll" ;<-- Change to the location of your sqlite dll

        Local $aRow
        Local $hQuery

        ;Init sqlite and create a memory db
        _SQLite_Startup($SQLITE_DLL, False, 1)
        If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
        _SQLite_Open()

        ;Example of validating json (Good return means JSON is well-formed)
        If _SQLite_Query(-1, "select json('" & $TEST_JSON & "');", $hQuery) = $SQLITE_OK Then
            While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
                ConsoleWrite("Minimized JSON = " & $aRow[0] & @CRLF)
            WEnd
        EndIf

        ;Example of extracting data
        If _SQLite_Query(-1, "select json_extract('" & $TEST_JSON & "', '$.version');", $hQuery) = $SQLITE_OK Then
            While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
                ConsoleWrite("version = " & $aRow[0] & @CRLF)
            WEnd
        EndIf

        ;Close db and shutdown sqlite
        _SQLite_Close()
        _SQLite_Shutdown()

EndFunc

 

  • Like 2
  • Thanks 1

Share this post


Link to post
Share on other sites
Skysnake
#cs ----------------------------------------------------------------------------

    AutoIt Version: 3.3.14.2
    Author:         TheXman
    JSON as AutoIt data type

    Script Function:
    https://www.autoitscript.com/forum/topic/197243-json-as-autoit-data-type/?do=findComment&comment=1414692

#ce ----------------------------------------------------------------------------

; Script Start - Add your code below here
#include <SQLite.au3>

example()

Func example()

    ; https://en.m.wikipedia.org/wiki/00_Agent
    Const $TEST_JSON = _
            '{' & _
            '    "James Bond": "007",' & _
            '    "Randy Enton": "006",' & _
            '    "Sam Johnston": "0012"' & _
            '}'
    ;;#CE

    Const $SQLITE_DLL = @ScriptDir & "\sqlite3.dll" ;<-- Change to the location of your sqlite dll

    Local $aRow
    Local $hQuery

    ;Init sqlite and create a memory db
    _SQLite_Startup($SQLITE_DLL, False, 1)
    If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
    _SQLite_Open()

    ;Example of validating json (Good return means JSON is well-formed)
    If _SQLite_Query(-1, "select json('" & $TEST_JSON & "');", $hQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            ConsoleWrite("Minimized JSON = " & $aRow[0] & @CRLF)
        WEnd
    EndIf

    ;Example of extracting data
    Local $sJSONValueToFind = "James Bond"
    ;;;$sJSONQuery = "select json_extract('" & $TEST_JSON & "', '$.James Bond') ;"
    $sJSONQuery = "select json_extract('" & $TEST_JSON & "', '$." & $sJSONValueToFind & "') ;"
    ConsoleWrite($sJSONQuery & @CRLF)
    If _SQLite_Query(-1, $sJSONQuery, $hQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            ConsoleWrite($sJSONValueToFind & ": " & $aRow[0] & @CRLF)
        WEnd
    EndIf


    ;Example of setting data
    Local $sJSONAttribToSet = "Stuart Thomas"
    Local $sJSONValueToSet = "005"
    Local $sJSONValueToFind = "Stuart Thomas"
    ;;;$sJSONQuery = "select json_extract('" & $TEST_JSON & "', '$.e4') ;"
    $sJSONQuery = "select json_set('" & $TEST_JSON & "', '$." & $sJSONAttribToSet & "'," & $sJSONValueToSet & ") ;"
    ConsoleWrite("88 " & $sJSONQuery & @CRLF)
    If _SQLite_Query(-1, $sJSONQuery, $sJSONQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($sJSONQuery, $aRow) = $SQLITE_OK
            ConsoleWrite("91 " & $sJSONValueToFind & ": " & $aRow[0] & @CRLF)
            Local $improvedJSON = $aRow[0]
        WEnd
    EndIf


    ;Local $improvedJSON = $aRow[0]
    ConsoleWrite("$improvedJSON " & $improvedJSON & @CRLF)

    $sJSONQuery = "select json_extract('" & $improvedJSON & "', '$." & $sJSONValueToFind & "') ;"
    ConsoleWrite("98 " & $sJSONQuery & @CRLF)
    If _SQLite_Query(-1, $sJSONQuery, $hQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            ConsoleWrite("101 " & $sJSONValueToFind & ": " & $aRow[0] & @CRLF)
        WEnd
    EndIf



    ;Close db and shutdown sqlite
    _SQLite_Close()
    _SQLite_Shutdown()

EndFunc   ;==>example

@TheXman I modded your code :)

Added a SQLite JSON_SET statement.  https://www.sqlite.org/json1.html#jins see 4.5 

I am struggling with

  • How to insert the JSON string into the SQLite table (probably just a syntax issue)
  • and how to save '007' as string and not a number ie '007' and not '7'

Thx

Skysnake

Edited by Skysnake
typo

Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
jchd
2 minutes ago, Skysnake said:

How to insert the JSON string into the SQLite table (probably just a syntax issue)

JSON is text so you can simply and safely store it in a TEXT column.

31 minutes ago, Skysnake said:

how to save '007' as string and not a number ie '007' and not '7'

You need to quote the value, like this:

; Script Start - Add your code below here
#include <SQLite.au3>

example()

Func example()
    Const $TEST_JSON = _
            '{' & _
            '    "James Bond": "007",' & _
            '    "Randy Enton": "006",' & _
            '    "Sam Johnston": "0012"' & _
            '}'

    Const $SQLITE_DLL = "C:\SQLite\bin\sqlite3.dll" ;<-- Change to the location of your sqlite dll
    Local $aRow
    Local $hQuery

    ;Init sqlite and create a memory db
    _SQLite_Startup($SQLITE_DLL, False, 1)
    If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
    _SQLite_Open()

    ;Example of validating json (Good return means JSON is well-formed)
    If _SQLite_Query(-1, "select json('" & $TEST_JSON & "');", $hQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            ConsoleWrite("Source JSON = " & $aRow[0] & @CRLF)
        WEnd
    EndIf

    ;Example of setting data
    Local $sJSONAttribToSet = "Stuart Thomas"
    Local $sJSONValueToSet = "005"
    Local $sJSONValueToFind = $sJSONAttribToSet
    ; #### note double quotes around $sJSONValueToSet ####
    $sJSONQuery = "select json_set('" & $TEST_JSON & "', '$." & $sJSONAttribToSet & "',""" & $sJSONValueToSet & """) ;"
    Local $sChangedJSON
    If _SQLite_Query(-1, $sJSONQuery, $sJSONQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($sJSONQuery, $aRow) = $SQLITE_OK
            $sChangedJSON = $aRow[0]
        WEnd
    EndIf

    ConsoleWrite("$sChangedJSON " & $sChangedJSON & @CRLF)

    $sJSONQuery = "select json_extract('" & $sChangedJSON & "', '$." & $sJSONValueToFind & "') ;"
    ConsoleWrite($sJSONQuery & @CRLF)
    If _SQLite_Query(-1, $sJSONQuery, $hQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            ConsoleWrite($sJSONValueToFind & ": " & $aRow[0] & @CRLF)
        WEnd
    EndIf



    ;Close db and shutdown sqlite
    _SQLite_Close()
    _SQLite_Shutdown()

EndFunc   ;==>example

 

  • Like 1

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
TheXman

Hi SkySnake -

Below you will see a slighly modified version of your script.  I identified my modifications by wrapping them in

"; =========== Modified  ====================="

Also notice that I changed the method of validating json by changing the function to json_valid().

#cs ----------------------------------------------------------------------------

    AutoIt Version: 3.3.14.2
    Author:         TheXman
    JSON as AutoIt data type

    Script Function:
    https://www.autoitscript.com/forum/topic/197243-json-as-autoit-data-type/?do=findComment&comment=1414692

#ce ----------------------------------------------------------------------------

; Script Start - Add your code below here
#include <SQLite.au3>

example()

Func example()

    ; https://en.m.wikipedia.org/wiki/00_Agent
    Const $TEST_JSON = _
            '{' & _
            '    "James Bond": "007",' & _
            '    "Randy Enton": "006",' & _
            '    "Sam Johnston": "0012"' & _
            '}'
    ;;#CE

    Const $SQLITE_DLL = "c:\program files\sqlite\sqlite3.dll" ;<-- Change to the location of your sqlite dll

    Local $aRow
    Local $hQuery

    ;Init sqlite and create a memory db
    _SQLite_Startup($SQLITE_DLL, False, 1)
    If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
    _SQLite_Open()

; =========== Modified  =====================
    Local $sQuery, $sKey
    Local $vValue

    ;Example of validating json (Good return means JSON is well-formed)
    $sQuery = StringFormat("SELECT json_valid('%s')", $TEST_JSON)
    ConsoleWrite(@CRLF & "Query: " & $sQuery & @CRLF)
    If _SQLite_Query(-1, $sQuery, $hQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            ConsoleWrite("Value: " & $aRow[0] & @CRLF)
        WEnd
    EndIf

    ;Example of validating json (Good return means JSON is well-formed)
    $sKey   = "Stuart Thomas"
    $vValue = "007"
    $sQuery = StringFormat("SELECT json_set('%s', '$.%s','%s')", $TEST_JSON, $sKey, $vValue)
    ConsoleWrite(@CRLF & "Query: " & $sQuery & @CRLF)
    If _SQLite_Query(-1, $sQuery, $hQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            ConsoleWrite("Value: " & $aRow[0] & @CRLF)
        WEnd
    EndIf

    ConsoleWrite(@CRLF)
; =========== Modified  =====================

    ;Example of extracting data
    Local $sJSONValueToFind = "e4"
    ;;;$sJSONQuery = "select json_extract('" & $TEST_JSON & "', '$.e4') ;"
    $sJSONQuery = "select json_extract('" & $TEST_JSON & "', '$." & $sJSONValueToFind & "') ;"
    ConsoleWrite($sJSONQuery & @CRLF)
    If _SQLite_Query(-1, $sJSONQuery, $hQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            ConsoleWrite($sJSONValueToFind & ": " & $aRow[0] & @CRLF)
        WEnd
    EndIf


    ;Example of setting data
    Local $sJSONAttribToSet = "Stuart Thomas"
    Local $sJSONValueToSet = "005"
    Local $sJSONValueToFind = "Stuart Thomas"
    ;;;$sJSONQuery = "select json_extract('" & $TEST_JSON & "', '$.e4') ;"
    $sJSONQuery = "select json_set('" & $TEST_JSON & "', '$." & $sJSONAttribToSet & "'," & $sJSONValueToSet & ") ;"
    ConsoleWrite("88 " & $sJSONQuery & @CRLF)
    If _SQLite_Query(-1, $sJSONQuery, $sJSONQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($sJSONQuery, $aRow) = $SQLITE_OK
            ConsoleWrite("91 " & $sJSONValueToFind & ": " & $aRow[0] & @CRLF)
            Local $improvedJSON = $aRow[0]
        WEnd
    EndIf


    ;Local $improvedJSON = $aRow[0]
    ConsoleWrite("$improvedJSON " & $improvedJSON & @CRLF)

    $sJSONQuery = "select json_extract('" & $improvedJSON & "', '$." & $sJSONValueToFind & "') ;"
    ConsoleWrite("98 " & $sJSONQuery & @CRLF)
    If _SQLite_Query(-1, $sJSONQuery, $hQuery) = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            ConsoleWrite("101 " & $sJSONValueToFind & ": " & $aRow[0] & @CRLF)
        WEnd
    EndIf



    ;Close db and shutdown sqlite
    _SQLite_Close()
    _SQLite_Shutdown()

EndFunc   ;==>example

 

 

By the way, although sqlite has some nice json functionality, you may want to check out my new UDF that lets you run jq. You can read more about it here:

 

Edited by TheXman
Fixed the comments to accurately represent the code blocks
  • Like 1

Share this post


Link to post
Share on other sites
argumentum
2 hours ago, TheXman said:

you may want to check out my new UDF that lets you run jq

I've tried it and find it slow. Good but 0.5 sec. to do an operation.    =/

Share this post


Link to post
Share on other sites
TheXman
1 hour ago, argumentum said:

I've tried it and find it slow.

Yes, since jq itself is a command line tool, it is a bit slower than say a dll, especially if one is just retrieving a lot of scalar values.  However, it is very powerful.  I can definitely imagine more advanced scenarios in which jq may not only be faster, but much easier too.   If json slicing and dicing is necessary, or if you need to do mathematical functions against json data like averages or summing, or even need to extract, sort,  and/or generate json from existing json files, jq may be a better choice and a lot easier to use.  In addition, there are many things that jq can do that other json parsers cannot.  That's primarily because jq is a json processing tool as opposed to a json parser.  In any case, It's just another tool for the tool box.  :)

...And thanks for trying it out.  :thumbsup:

Edited by TheXman
  • Like 1

Share this post


Link to post
Share on other sites
Skysnake
14 hours ago, jchd said:

You need to quote the value, like this:

:)  @jchd  you posted elsewhere a very handy little function for quoting standard SQL strings: _SQLStr() which quotes and doubles ' inline.

What would the JSON equivalent be? _SQLJSON() ...

Func _SQLJSON($s)
    Return('""' & StringReplace($s, "'", "''") & '""')
EndFunc

Replace ' with double for SQL , and use double "" around the JSON value...

Am I on the right track?

@TheXman & @argumentum thx. :)

 


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
jchd
5 hours ago, Skysnake said:

Am I on the right track?

There's an extra " around the result, but that isn't the whole story:

#include <SQLite.au3>

Const $SQLITE_DLL = "C:\SQLite\bin\sqlite3.dll" ;<-- Change to the location of your sqlite dll
Local $aRow

;Init sqlite and create a memory db
_SQLite_Startup($SQLITE_DLL, False, 1)
If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
_SQLite_Open()

Local $sTEST_JSON = "Mc'Donald's" & @TAB & 'food / if you call that "food" \' & @CRLF & "this is the last verse."
Local $sEscapedJSON = _JSON_SQL_Escape($sTEST_JSON)
ConsoleWrite("Escaped JSON = " & $sEscapedJSON & @LF)

If _SQLite_QuerySingleRow(-1, "select json_extract(" & $sEscapedJSON & ", '$');", $aRow) = $SQLITE_OK Then
    ConsoleWrite("Valid JSON = " & $aRow[0] & @LF)
EndIf

Exit

; Escape chars in JSON strings as per rfc-7159 (http://www.rfc-editor.org/rfc/rfc7159.txt)
; then returns a valid SQL string containing a valid JSON string type.
;
; Literal single & double quotes are escaped to hex to avoid issues
; this is less human-readable but rock-solid (see below for full Unicode)
;
; If you expect Unicode chars outside the BMP (Basic Multilingual Plane)
; you need to convert codepoints > 0xFFFF to their surrogate pair in Hex

Func _JSON_SQL_Escape($s)
    ; escape / \ with a backslash
    $s = StringRegExpReplace($s, '([\\/])', '\\$1')
    ; escape single quotes to hex
    $s = StringRegExpReplace($s, "'", '\\u0027')
    ; escape solidus, backslash, double quotes and control chars to Hex
    ; then enclose the whole string in double quotes so that it's then a valid JSON literal string
    ; then enclose it in single quotes so that the SQL parser sees a valid SQL string
    Return '''"' & Execute('"' & StringRegExpReplace($s, '(["\x00-\x1f])', '" & "\\u" & Hex(Asc(''$1''), 4) & "') & '"') & '"'''
EndFunc

 

Edited by jchd
  • Like 1

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Skysnake

@jchd thank you kindly


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • wolflake
      By wolflake
      I've used PowerPro for years and it had a feature that let you turn an array into a menu.  It also let you know if it got a right click. I liked the right click feature because it doubled the menu item usage ie left/right on the same menu item.  I tried recreate that usage with a context menu.   With _ArrayToMenu you can pass menu items for 1 and 2 dimensional arrays. Note you can indicate sub-menu but using a -> at the end of the item.  Use a - to make a separater. Optionally you can pass Tooltips by passing a second array. This array must have the same dimensions as the items array even if not all the elements contain tips. The UDF returns an array with this index of row (and if submenus column), the third element is 1 if right clicked otherwise 0 and the fourth element of the array is the label of the menu item. If the user escapes without choosing an item the the first element returns a -1.  Same thing if the user clicks on another window.   @error returns 1 if there was no array passed, 2 if the second array has different dimensions than the first.   If you use the same label in two parts of your menu you will have to distinguish them.  You can do that by additionally making sure that the selection matches the index number(s).  Note that you'll have to match the index number plus label before you match just the label because Switch will use the first match it can find.  In the example below I am using a check on which column the data is in.  If it's not in the first column then it must be the other "Beef".   Beef
      Fruit
      Col2 > Beef
                  Bread   $aR = _ArrayToMenu($aM) Switch $aR[3]
      Case "Beef" and $aR[2] = 0
             Beef1()
       Case "Beef"
             Beef2()
      EndSwitch   On a technical note I attached the context menu to the window itself not a dummy control and I didn't use _GUICtrlMenu_TrackPopupMenu.  Instead I launched the context menu with "send shift-F10" and waited for GuiGetMsg() to give me the selection. Right click is picked up by GUIRegisterMsg WM_RBUTTONUP and Tooltips are done with GUIRegisterMsg WM_MENUSELECT. The whole thing is done with 3 functions. 
      I won't tell you how long it took me to figure this out but I'll say that on one of my early attempts it had two windows running at once and one was just to recieve the right click an tell the other it got it.  Suffice it to say I'm no wiz at Autoit but I really appreciate the support the community offers and I hope someone finds this useful. BTW I wrote a script to produce 1d and 2d auotit array code from excel in case you want to model your menu in excel. Here is the link.
      https://www.autoitscript.com/forum/topic/139260-autoit-snippets/?do=findComment&comment=1412314
      _ArrayToMenu() UDF
      ;ArrayToMenu with submenus, tooltips, rightclick and esc to close. ; #INDEX# ======================================================================================================================= ; Title .........: _ArrayToMenu ; AutoIt Version : 3.3.14.2 ; Description ...: Show an array as a popup menu optionally with tooltips and right click. ; Author(s) .....: Rick Sharp ; =============================================================================================================================== ; #FUNCTION# ==================================================================================================================== ; Name..........: _ArrayToMenu($aArray_menu[,$aArray_tooltips]) ; Description...: Display an array as a menu and return the users choice, display tooltips(optional), return right click. ; Syntax........: _ArrayToMenu($aArray_menu[,$aArray_tooltips]) ; ; Parameters....: ; Required......: A 1d or 2d array of menu items ; Use a minus sign in the item to indicate a menu separator. ; Use -> at the end of an item to indicate a sub-menu. ; Optional......: A 1d or 2d array of tooltips. The array must use the same dimensions as the menu items array. ; ; Return values.: An Array ; $aArray[0] is index of the row (-1 if exited with no choice) ; $aArray[1] is index of the column ; $aArray[2] is 1 if right clicked ; $aArray[3] is the selected item (if any) ; Notes.........: If the user clicks on another window the ArrayToMenu returns as if esc were pressed. ; Sub-Menus are limited to 10 levels if you need more change $ahM[10] ; =============================================================================================================================== ; #VARIABLES# =================================================================================================================== ; Global $__g_iRT = 0, $__g_aTT1, $__g_ahi ; "$__g_iRT" for right click flag, "$__g_aTT1" for tips, "$__g_ahi" for index of id's in menu and tips ; =============================================================================================================================== ; #@error# ====================================================================================================================== ; 1 - First parameter is Not an array ; 2 - The Menu/Items array and the Tips array are not the same number of dimensions ; =============================================================================================================================== #include-once #include <WindowsConstants.au3> #include <GuiMenu.au3> #include <array.au3> #include <misc.au3> Func _ArrayToMenu($aMenu, $att = "") If Not IsArray($aMenu) Then Return SetError(1, 0, -1) Global $__g_iRT = 0 Local $ahM[10], $iCcnt = UBound($aMenu, 2), $iRcnt = UBound($aMenu), $iRow, $iCol, $b_Esc If UBound($aMenu, 2) = 0 Then _ArrayColInsert($aMenu, 1) ;if 1d array make it 2d EndIf ;Prep Loop to make Menus and Sub-Menus $iRcnt = UBound($aMenu) ;Count of Rows/Items $iCcnt = UBound($aMenu, 2) ;Count of Cols/Menus GUIRegisterMsg($WM_RBUTTONUP, "WM_RBUTTONUP") ;handles Right Click If IsArray($att) Then If UBound($att, 2) = 0 Then _ArrayColInsert($att, 1) If UBound($att, 2) <> $iCcnt Or UBound($att) <> $iRcnt Then Return SetError(2, 0, -1) ;$amenu and $att not same dimensions Global $__g_aTT1 = $att ;added $__g_aTT1 because $att was not seen by WM_MenuSelect for tooltips GUIRegisterMsg($WM_MENUSELECT, "WM_MENUSELECT") ;handles tooltips EndIf Local $mPos = MouseGetPos() #Region ### START Koda GUI section ### Form= $hMenu = GUICreate("C_menu", 10, 10, $mPos[0], $mPos[1], $WS_POPUP, $WS_EX_TOPMOST) GUISetState(@SW_SHOW) #EndRegion ### END Koda GUI section ### ;Build Menus Global $__g_ahi[$iRcnt + 1][$iCcnt + 1] ;array to hold Menu Item id's $iMcnt = 0 ;Menu count $ahM[$iMcnt] = GUICtrlCreateContextMenu() ;if the array element is null then it falls through and nothing happens For $j = 0 To $iCcnt - 1 ;for each Column/Menu For $i = 0 To $iRcnt - 1 ;for each Row/Item If StringRight($aMenu[$i][$j], 2) = "->" Then ;Sub-Menu $aMenu[$i][$j] = StringTrimRight($aMenu[$i][$j], 2) ;remove -> $iMcnt += 1 $ahM[$iMcnt] = GUICtrlCreateMenu($aMenu[$i][$j], $ahM[$j]) ; $__g_ahi[$i][$j] = $ahM[$iMcnt] ElseIf $aMenu[$i][$j] > "" Then ;Normal item If StringLeft($aMenu[$i][$j], 1) = "-" Then $aMenu[$i][$j] = "" $__g_ahi[$i][$j] = GUICtrlCreateMenuItem($aMenu[$i][$j], $ahM[$j]) EndIf Next Next Send("+{F10}") ;sends right click to open context menu While 1 $nMsg = GUIGetMsg() If $nMsg > 0 Then ;ConsoleWrite("nMsg= " & $nMsg & @CRLF) $iRow = _ArraySearch($__g_ahi, $nMsg) $iCol = _ArraySearch($__g_ahi, $nMsg, 0, 0, 0, 0, 0, $iRow, True) ExitLoop EndIf If _IsPressed("1B") = 1 Or WinActive("C_menu") = 0 Then $b_Esc = -1 ExitLoop EndIf WEnd ;*** Done *** GUIDelete($hMenu) Local $aAr1[4] $aAr1[0] = $iRow $aAr1[1] = $iCol $aAr1[2] = $__g_iRT If Not $b_Esc = -1 Then $aAr1[3] = $aMenu[$aAr1[0]][$aAr1[1]] Else $aAr1[0] = -1 EndIf Return $aAr1 EndFunc ;==>_ArrayToMenu ;Check for Right Click Func WM_RBUTTONUP($hMenu, $iMsg, $iwParam, $ilParam) $__g_iRT = 1 ;Mark as rclicked Send("{Enter}") ;choose the item EndFunc ;==>WM_RBUTTONUP ;Tooltips Func WM_MENUSELECT($hMenu, $iMsg, $iwParam, $ilParam) Local $idMenu = BitAND($iwParam, 0xFFFF) Local $iRow, $iCol If $idMenu > 0 Then $iRow = _ArraySearch($__g_ahi, $idMenu) If $iRow > -1 Then $iCol = _ArraySearch($__g_ahi, $idMenu, 0, 0, 0, 0, 0, $iRow, True) EndIf If $iCol > -1 And $iRow > -1 And $__g_aTT1[$iRow][$iCol] > " " Then ToolTip($__g_aTT1[$iRow][$iCol]) Else ToolTip("") EndIf EndIf EndFunc ;==>WM_MENUSELECT Example 1 Simple 1d array with tooltips, item separator and right click.
      #include "ArrayToMenu.au3" ;Simple 1d array with tooltips item separator and right click. $aM = StringSplit("Zero,One,-,Two/Two_R", ",", 3) ;make an array for the menu items $aT = StringSplit("Zero,One,-,", ",", 3) ;make an array for the menu Tooltips $aR = _ArrayToMenu($aM,$aT) if @error then ConsoleWrite(@error & @CRLF) EndIf ConsoleWrite("R: " & $aR[0] & " " & "C: " & $aR[1] & " " & "Rclick: " & $aR[2] & " " & "Item: " & $aR[3] & @CRLF) If $aR[0] = -1 Then ;either hit escape or clicked on another window ConsoleWrite("Esc" & @CRLF) Exit EndIf ;_ArrayDisplay($aR) Switch $aR[3] Case "Zero" Zero() Case "One" One() Case "Two/Two_R" And $aR[2] = 0 ;No Rclick Two() Case "Two/Two_R" And $aR[2] = 1 ;Rclick Two_R() EndSwitch Func Zero() ConsoleWrite("You chose: Zero" & @CRLF) EndFunc ;==>Zero Func One() ConsoleWrite("You chose: One" & @CRLF) EndFunc ;==>One Func Two() ConsoleWrite("You chose: Two" & @CRLF) EndFunc ;==>Two Func Two_R() ConsoleWrite("You chose: Two_R" & @CRLF) EndFunc ;==>Two_R Example 2 2d array with sub-menu
      #include "ArrayToMenu.au3" ;2d array with a sub-menu dim $aM[4][2] = [["Beef", "Orange"], ["Pork", "Apple"], ["Chicken", "Grape"], ["Fruit->", ""]] ;Note you don't need a tooltip for every item but you at least need a place holder in the array dim $aT[4][2] = [["Red Meat", "Fruit"], ["Other white meat", "Fruit"], ["White meat", "Fruit"], ["", ""]] $aR = _ArrayToMenu($aM,$aT) if @error Then ConsoleWrite(@error & @CRLF) Exit EndIf If $aR[0] = -1 Then ConsoleWrite("Esc" & @CRLF) Exit EndIf ConsoleWrite("R: " & $aR[0] & " " & "C: " & $aR[1] & " " & "Rclick: " & $aR[2] & " " & "Item: " & $aR[3] & @CRLF) Switch $aR[3] Case "Beef" Beef() Case "Pork" Pork() Case "Chicken" Chicken() Case "Orange" ConsoleWrite("Oranges are good for you!" & @CRLF) ConsoleWrite("Oranges" & " $aR[0] = " & $aR[0] & " $aR[1] = " & $aR[1] & @CRLF) Case "Apple" ConsoleWrite("Apples are good for you!" & @CRLF) Case "Grape" ConsoleWrite("Grapes are good for you!" & @CRLF) EndSwitch Func Beef() ConsoleWrite("Beef" & " $aR[0] = " & $aR[0] & " $aR[1] = " & $aR[1] & @CRLF) EndFunc ;==>Beef Func Pork() ConsoleWrite("Pork" & " $aR[0] = " & $aR[0] & " $aR[1] = " & $aR[1] & @CRLF) EndFunc ;==>Pork Func Chicken() ConsoleWrite("Chicken" & @CRLF) EndFunc ;==>Chicken  
    • jchd
      By jchd
      Here's a new version of my Dump.au3 file (dirty). I've recently added support to dissect (dump) as a treeview-like view any string in the variable conforming to a strict JSON grammar, whereever said string is: as a standalone string variable or in an array or a map. AFAICT the only relaxed constraint not implemented here is that numbers in parsed JSON may have leading zeroes, although leading zeroes are forbidden in pure JSON specifications. If required it wouldn't be hard to add this constraint.
      @c.haslam (among others) may find it useful. Use freely.
      Dump.au3
    • Ibet
      By Ibet
      Hey all, 
      Ending day 2 of learning AutoIt, and I'm stumped. I wrote an extremely rudimentary script simulating keystrokes for reading/copying values from one excel spreadsheet and pasting them into another spreadsheet, line by line. It works, but it doesn't use any of the Excel UDFs and was just sloppy. So, I'm trying to re-write it using some Excel UDFs to not only optimize the script, but to also learn how to use the Excel UDFs. If the answer is in a help file, please explain as I'm sometimes having problems understanding the examples in the help files.
      I'm getting the error: 
      "C:\Users\johndoe\Desktop\AutoIt Test\AutoIt_Read spreadsheet 1 - write spreadsheet 2-version2.au3" (25) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.: MsgBox(0,"Test","Test",$SourceEntry[1]) MsgBox(0,"Test","Test",^ ERROR >Exit code: 1 Time: 1.804 Here is the code:
      #include<Array.au3> #include<Excel.au3> ;-------------------Read from Source--------------------------- Local $oExcel_Source = _Excel_Open() Local $sWorkbook = "C:\Users\johndoe\Desktop\AutoIt Test\AutoIt_Testing_SOURCE.xlsx" Local $oWorkbook = _Excel_BookOpen($oExcel_Source,$sWorkbook) Local $SourceRow = 3 ;--eventually will be used to iterate through the rows, one at a time Local $SourceEntry[5] = _Excel_RangeRead($oWorkbook,Default,"A"&$SourceRow&":E"&$SourceRow) _ArrayDisplay($SourceEntry, "1D Display") ;--Displays array values correctly MsgBox(0,"Test","Test",$SourceEntry[1]) ;--Gives error, for any index in the array I want to make sure I can read the values of the array individually, before I try putting them into another document. This is because I've got to add some checks against the values already existing in the destination spreadsheet before any manipulation. I've spent the last hour or more googling that error and reading multiple posts where that error is meaning many different things, so unsure EXACTLY what the problem is. Would greatly appreciate a fix and/or explanation as well as patience with my noob-ness.
      Thanks in advance
    • SlackerAl
      By SlackerAl
      I appreciate this seems like a trivial question, but my searching has failed....
      I was using a 1D array as follows:
      Local $DataName[3] = [2, $sBaseFileName, $sUpdatedFileName] And all was OK. I then wanted to separate the declaration from the assignment and assumed I could do something like:
      Local $DataName[3] $DataName = [2, $sBaseFileName, $sUpdatedFileName] ;or $DataName[] = [2, $sBaseFileName, $sUpdatedFileName] But I see I cannot. I am aware that I can add the 3 elements 1 at a time with _ArrayAdd, but is there a simple inline method like I was trying for?
    • Lynn Shaw
      By Lynn Shaw
      Anyone know why _ArraySearch is not finding an entry with a single quote in it?  
      Here is an example:
      #include <array.au3>
      Dim $array[3][2]
      $array[0][0] = "Testing is****fun"
      $array[1][0] = "Don't mess with me kid"
      $array[2][0] = "blah'blah"
      _ArrayDisplay($array)
      $index = _ArraySearch($array, "blah'blah", 0, 0, 0, 0, 1, 1)
      MsgBox(262144, 'Debug line ~' & @ScriptLineNumber, 'Selection:' & @CRLF & '$index' & @CRLF & @CRLF & 'Return:' & @CRLF & $index) ;### Debug MSGBOX
×