TableData - more efficient handling with table structured data

[AspirinJunkie]

Introduction

In AutoIt, we often have to deal with data structured like tables.
Depending on the source, we sometimes have to write a lot of code to convert the data into a form that we can continue working with.
Subsequently, working with this data is not necessarily any easier, as instead of accessing the descriptive names of the data attributes, we have to deal with numerical indices where it is very easy to lose track.

Both problems are addressed by the UDF.
In the basic approach, the UDF works with a table object (just an AutoIt map) in which the data is separated from the header.
This enables cleaner processing of the data.
It offers functions to read in data from various sources (CSV, array, string with fixed-width-columns, strings with their own column separators).
Within this call, the data is separated from the header or header data is added to it in the first place.
The data is then processed according to its format (with csv quotes and escapes removed, with fixed-width spaces removed...).
In addition, the user can define how exactly the data should be processed for each column individually. He is completely free to do this.
In this way, the data is already given the final format in which it is to be further processed when it is read in.

The data can then be handled on a column-based or attribute-based basis.
In other words, instead of using indices, the attribute names of the data can simply be used - this makes the code clearer.

Example (much more examples provided >>here<<)

We want to evaluate the open ports on a computer with AutoIt.
The command line command for this is netstat -t and gives us the following output (Depending on the system, the output may look slightly different for you):

Active Internet connections (w/o servers)

Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0    116 192.168.64.110:ssh      192.168.29.200:65069    ESTABLISHED
tcp        0      0 192.168.64.110:ssh      192.168.29.200:65068    ESTABLISHED

To continue processing the data in a meaningful way, we may need to carry out the following steps in AutoIt:

• Delete the first useless lines
• Extract the header row (it is treated differently from the data)
• Create an array with the correct dimension to hold the data
• Separate the data either by spaces or by fixed column widths
• Removal of unnecessary spaces from the data
• Converting the Recv-Q and Send-Q column to a numeric data type (for sorting etc.)
• Separation of address data into IP address and port

This can mean a lot of (error-prone) code effort.
With this UDF, however, you can solve the whole thing in a single call as follows:

$sString = 'Active Internet connections (w/o servers)' & @CRLF & _
    '' & @CRLF & _
    'Proto Recv-Q Send-Q Local Address           Foreign Address         State' & @CRLF & _
    'tcp        0    116 192.168.64.110:ssh      192.168.29.200:65069    ESTABLISHED' & @CRLF & _
    'tcp        0      0 192.168.64.110:ssh      192.168.29.200:65068    ESTABLISHED'

; Transfer data to table type by using the size of every column:
Global $mData = _td_fromFixWidth($sString, _
                                 "left 6; Number 7; Number 7; StringSplit($x, ':', 2) 24;StringSplit($x, ':', 2) 24;", _ ; column definitions
                                 "1-2", _ ; skip row 1 to 2
                                 True)

; display data
_td_display($mData)

and we get the result:

If you now use functions such as _td_toObjects(), you can process the individual data with expressions such as $aData.Proto or $aData.State.
This should be much clearer than having to deal with array indices.

Functions

Spoiler

---- input ------
_td_fromString      - convert table-structured strings where the column separator can be described by a regular expression into a table object
_td_fromCsv         - read strings with 2D-array-like data from a string where the rows and columns separated by separator-chars (e.g.: csv or tsv)
_td_fromFixWidth    - read 2D-array-like data from a string where the columns have a fixed width (e.g. console outputs or printf-strings)
_td_fromArray       - creates a table object from an existing 2D array

---- output  ------
_td_toCsv           - convert a table object into a csv formatted string
_td_toFixWidth      - convert a table object into a string where the columns has fixed width
_td_display         - present a table object like _ArrayDisplay
_td_toArray         - creates an array from a table object

------ process table objects ------------
_td_join            - sql-like joins for table objects
_td_filter          - sql-like "where"-filtering for table objects
 
----- Preparation of 2D arrays for easy further processing ---
_td_toObjects       - converts a table object into a set of key-value maps (every record = key-value map)
_td_toDics          - converts a table object into a set of objects (every record = Dictionary with named attributes)
_td_toPrimaryKeys   - converts a table object into a map where the data can be accessed by their unique primary key
_td_toColumns       - converts a table object into a map with column names as keys and their data as 1D-arrays
_td_getColumn       - extract one or multiple colums from a 2D-Array or a table-data map
_td_MapsToTable     - converts a map-array (a 1D-array with maps as values) into 2 2D-array where the colums = keys

 

>>sourcecode and download on github<<

Edited February 20 by AspirinJunkie
added: _td_filter

[Sascha]

Nice job. Works fine.

It would be usefull to me if you add an Ok and Cancel Button and return the selected line (incl. double mouse click) . So we would have an easy to use selection dialog.

Only my 2 cents.

Thank you.

Regards

Sascha

[AspirinJunkie]

A new helpful function has been added in this context: The _td_join() function.
This combines the table objects here similar to an SQL join.

It is best explained using an example: We have the open ports via netstat and want to have the information on the corresponding process there.
The process information could be obtained via the "tasklist" command.

However, querying, preparing and merging the whole thing is a lot of paperwork.
With the UDF here, however, it is reduced to the following (the output of netstat might differ depending on the language) :

#include "TableData.au3"
#include <WinAPIConv.au3>

; get data from netstat
Global $mNetStat = _td_fromFixWidth(_getCmdOutput('netstat -ano'), "7;23;23;16;Number 100", "1-2", true)
; get data from tasklist
Global $mTaskList =  _td_fromCsv(_getCmdOutput('tasklist /FO CSV',  True), ',', 0, True)

; show data
_td_display($mNetStat, "netstat data")
_td_display($mTaskList, "tasklist data")

; link the NetStat data with the data for the associated process
$mJoined = _td_join($mNetStat, $mTaskList, "PID", "PID", "left")

; display the combined table object
_td_display($mJoined, "Joined tables: open ports and their corresponding process info")




; run cmdline-commands and return their output
Func _getCmdOutput($sCmd, $bComspec = False, $oFlags = $STDOUT_CHILD)
    Local $iPID = Run(($bComspec ? '"' & @ComSpec & '" /c ' : "") & $sCmd, "", @SW_Hide, $oFlags)
    ProcessWaitClose($iPID)
    Return _WinAPI_OemToChar(StdoutRead($iPID))
EndFunc