Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

[Lib] String-based Table Manipulation v0.28


  • Please log in to reply
17 replies to this topic
VxE
  • Moderators
  • 3622 posts
  • Last active: Dec 24 2015 02:21 AM
  • Joined: 07 Oct 2006
This is a collection of functions for manipulating plain text tables in tab-separated value format.

There are a few basic formatting rules that must be followed by any text passed as a 'table' to these functions.
    1: The text may contain any number of newline (`n) characters, which separate rows.
    2: Each row must have the same number of tab characters (`t) as the first (top) row. Tabs separate cells and, conceptually, columns.

This is a work in progress, new versions are not guaranteed to be 100% compatible with old ones. Old versions will not remain available once a newer version has been released.

The 'Table_MsgBox' function is in its own file because its presence automatically makes the entire script PERSISTENT!

          Download Table.ahk                Download Table.zip

          Download Table_MsgBox.ahk


Save (or unpack) 'Table.ahk' to your "<Program Files>\AutoHotkey\Lib" folder to make the functions instantly available to your scripts.

All of the functions in this library are stand-alone functions*, so it is not necessary to include functions not explicitly used in a script.

(*) Stand-alone Function: A function that is independent of ALL other user-functions, even ones in the same collection or file.

12-20-2010: bugfix: typo in Table_Append().
12-22-2010: bugfix: disrupted literal HTML entities.
01-15-2011: NEW*: version 0.04 adds 4 functions: Table_Deintersect, Table_FromListview, Table_Intersect, and Table_ToListview.
01-16-2011: NEW*: Table_MsgBox added as a standalone function.
01-19-2011: bugfix: Table_Msgbox fixed header text
01-22-2011: NEW*: version 0.05 adds 2 functions: Table_Aggregate and Table_ColToList. Also, bugfix when header ends with CRLF
03-26-2011: NEW*: version 0.06 adds 5 functions: Table_FormatTime, Table_FromINI, Table_SpacePad, Table_Update, and Table_Width. Also, Table_GetCell is enhanced.
05-23-2011: MAJOR version released. New functions, revisions to old functions.
06-26-2012: A few tweaks and new functions.


VxE
  • Moderators
  • 3622 posts
  • Last active: Dec 24 2015 02:21 AM
  • Joined: 07 Oct 2006
      Function descriptions

Table_Aggregate: Yields metadata based on the input table. Supported operations are: Sum, Min, Max, Median, Average, Span, Count, and Standard Deviation.

Table_Append: Appends TableB to the bottom of TableA. If the table's don't have the same header, the columns of both tables may be reconciled, either by re-arranging TableB's columns or by adding columns to the right side of TableA.

Table_Between: Removes rows from a table based on the result of one or two inequality checks.

Table_ColToList: Returns a delimited list composed of the values in a single column of the table.

Table_Decode: Alters a string by decoding character entities for tabs and newlines.

Table_Deintersect: Removes rows from a table if they contain matching data from another table. Or removes rows that exceed a multiplicity check (e.g: remove duplicates).

Table_Encode: Alters a string by encoding tabs and newlines as character entities. This is required only when inserting cell contents that may contain tabs or newlines.

Table_FormatTime: Transforms AHK datetime strings inside a table using FormatTime

Table_FromCSV: Converts a CSV (comma-separated value) table into a 9/10 (tab-separated) table.

Table_FromHTML: Attempts to convert HTML into a 9/10 table based on '<table>' tags.

Table_FromINI: Coerces an INI-formatted text into a 9/10 table.

Table_Join: Performs a join (like a SQL table join) operation using two tables.

Table_FromListview: Returns a table representing data in the default gui's current listview. The table may be modified based on any checked, selected or focused row(s) in the listview.

Table_GetCell: Returns the text contents of a single cell in a table.

Table_GetRowIndex: Returns the ordinal position (index) of a row in a table that matches the given criteria.

Table_Header: Returns the table's header, which is the 0'th table row.

Table_Intersect: Removes rows from a table that don't contain matching data from another table. Or removes rows that don't meet a multiplicity check (e.g: remove non-duplicates).

Table_Invert: Transforms a table so that its first column becomes the header and its header becomes its first column.

Table_Len: Returns the number of rows in the table.

Table_MsgBox: Displays a gui containing a listview which contains the contents of the table. The gui number is chosen dynamically.

Table_RemCols: Removes columns from a table.

Table_RemRows: Removes rows from a table, either by row index, or by the first column's values.

Table_SetCell: Inserts the specified text into the indicated cell in the table.

Table_Sort: Rearranges the rows in the table, using most of the same options as the Sort command.

Table_SpacePad: Converts a table from 9/10 format to padded-cell format (where the columns are aligned by character position within the row).

Table_SubTable: Removes rows from the table, based on the position of a row and the number of rows desired.

Table_ToCSV: Converts a 9/10 table to CSV format.

Table_ToListview: Modifies a listview using a table. The modifications include altering columns, appending rows, and updating cells in existing rows.

Table_Update: Performs multiple cell modifications at once using data from another table.

Table_Validate: Coerces text into 9/10 table format by aligning the tabs in each row.

Table_Width: Returns the number of columns in the table.

Coming Eventually:
    * Table_SetRowOrder
    * Table_Query
    * Table_UpdateExpr

nader
  • Guests
  • Last active:
  • Joined: --
Hi [VxE]
Could you give an example of table file, please?
Thanks

VxE
  • Moderators
  • 3622 posts
  • Last active: Dec 24 2015 02:21 AM
  • Joined: 07 Oct 2006
Sure, here's a few examples of using the functions with a sample table text. Oh, and I had to fix a typo in the 'Table_Append()' function, so refresh your local copy if needed.

ImportFileText =
(
order-id`torder-item-id`tpurchase-date`tpayments-date`tbuyer-email`tbuyer-name`tbuyer-phone-number`tproduct-name `tquantity-purchased`tcurrency`titem-price`titem-tax`tshipping-price`tshipping-tax`trecipient-name
0xd4735e3a`t265e16ee`t2010-12-08T11:47:12-08:00`t2010-12-08T11:47:12-08:00`[email protected] `t8b9b5d03`t019c07d8`tHorsez`t2`tUSD`t69.96`t0.00`t13.98`t0.00`tb6c51f90
0x4b227777`td4dd1fc6`t2010-12-08T11:53:26-08:00`t2010-12-08T11:53:26-08:00`[email protected] `t48641d02`tb4d121d3`tStar Wars Battlefront`t1`tUSD`t31.95`t0.00`t7.99`t0.00`tfd328cb0
0xef2d127d`te37b942b`t2010-12-08T11:54:25-08:00`t2010-12-08T11:54:25-08:00`[email protected] `te54b0c61`t9a1f2232`tDisney Princess: Enchanted Journey`t1`tUSD`t39.98`t0.00`t7.99`t0.00`t7b2ebbcf
0x4fc82b26`taecb47d2`t2010-12-06T17:45:19-08:00`t2010-12-06T17:45:19-08:00`[email protected] `te3581732`ta3e7cbcc`tCisco-Linksys WMB54G Wireless-G Music Bridge`t1`tUSD`t69.49`t0.00`t7.99`t0.00`t6c2efb32
0x6b51d431`tdf5d7f14`t2010-12-08T11:37:15-08:00`t2010-12-08T11:37:15-08:00`[email protected] `tf79edf3d`td861c3b4`tDisney Princess: Enchanted Journey`t1`tUSD`t39.98`t0.00`t7.99`t0.00`t069f0b11
0x3fdba35f`t04dc8c46`t2010-12-08T11:52:55-08:00`t2010-12-08T11:52:55-08:00`[email protected] `tbcf87554`t62571130`tWii Sports Resort (Game ONLY) (No Instruction Booklet)`t1`tUSD`t26.95`t0.00`t7.99`t0.00`t72a909c1
0x8527a891`te2241369`t2010-12-08T11:30:40-08:00`t2010-12-08T11:30:40-08:00`[email protected] `t212b45bc`t93f69fbb`tLego Star Wars: The Complete Saga + Dual Glow Sabers for Nintendo Wii`t1`tUSD`t46.98`t0.00`t12.98`t0.00`t801c3b1e
0x9400f1b2`t1cb527d7`t2010-12-08T11:43:05-08:00`t2010-12-08T11:43:05-08:00`[email protected] `tba93557a`t18ebe7a2`tNerf-N-Strike Bundle`t1`tUSD`t39.98`t0.00`t7.99`t0.00`tca4e471c
0xf5ca38f7`t48a1d6ea`t2010-12-08T11:47:28-08:00`t2010-12-08T11:47:28-08:00`[email protected] `t2fb575c3`tc71f1864`tDragonball Z Budokai Tenkaichi`t1`tUSD`t7.25`t0.00`t10.99`t0.00`ta8143301
0x785f3ec7`teb32f30b`t2010-12-08T11:57:28-08:00`t2010-12-08T11:57:28-08:00`[email protected] `t3657d388`tb5ff4297`tWii Logitech Vantage USB Microphone (Black)`t1`tUSD`t39.98`t0.00`t7.99`t0.00`tf2f9716f
)

; Example 1: Extracting the desired columns from the table
MsgBox % PricesTable := Table_Append( "order-id`tcurrency`titem-price`tshipping-price", ImportFileText )

; Example 2: Adding a column to the right side of the table
MsgBox % PricesTable := Table_Append( PricesTable, "Profit", 1 )

; Example 3: Extracting the value from a single cell (referencing the row by the first cell's text)
MsgBox % SomePrice := Table_GetCell( PricesTable, "0xf5ca38f7", "item-price" )

; Example 4: Setting the value of a single cell (referencing the row by index)
MsgBox % PricesTable := Table_SetCell( PricesTable, "`t" 9, "Profit", Round( SomePrice * 0.11, 2 ) )

; Example 5: Sorting a table using a column containing floating-point values
MsgBox % PricesTable := Table_Sort( PricesTable, "item-price", "N R" )

; Example 6: Querying a row index using a cell's value
MsgBox % MyRow := Table_GetRowIndex( PricesTable, 31.95, "item-price" )

; Example 7: Trimming a table's rows
Msgbox % LowPriceTable := Table_SubTable( PricesTable, "`t" MyRow, 0 )

; Example 8: Converting a 9/10 table to CSV
MsgBox % Table_ToCSV( LowPriceTable )

exitapp
The sample text is derived from an amazon merchant orders report file, though the extraneous columns have been removed and most of the other data has been replaced with noise.

The table doesn't need to be in a file, I believe that copying a table from excel will fill the clipboard with a compatible table.

ruespe
  • Members
  • 567 posts
  • Last active: Dec 01 2014 07:59 PM
  • Joined: 17 Jun 2008
Nice and easy to use. Thanks for sharing.

I get one error:
Table_Decode( String ) { ; ------------------------------------------------------------------------- 
; Returns the string with the following entities decoded into their ascii characters:   
; NOTE: It is the user's responsibility to manage character escaping when using table-functions 
   oel := ErrorLevel 
   [color=red]StringReplace, String, String, , `n, a [/color]   StringReplace, String, String, 
, `n, a 
   StringReplace, String, String, 
, `n, a 
 [color=red]  StringReplace, String, String, , % "`t", a [/color]     
 [color=red]  StringReplace, String, String, , % "`t", a [/color]   StringReplace, String, String, 	, % "`t", a 
   StringReplace, String, String, 	, % "`t", a 
   Return String, ErrorLevel := oel 
} ; Table_Decode( String ) ------------------------------------------------------------------------- 

---------------------------
~tempAuto.ahkL
---------------------------
Error at line 166.

Line Text: StringReplace, String
Error: "StringReplace" requires that parameter #3 be non-blank.

The program will exit.
---------------------------
OK
---------------------------



VxE
  • Moderators
  • 3622 posts
  • Last active: Dec 24 2015 02:21 AM
  • Joined: 07 Oct 2006

Nice and easy to use. Thanks for sharing.

I get one error:...

Thanks for pointing that out, I thought I had interrupted the html entities used to encode tabs and newlines, but I guess not :oops: The top post should have the fixed code now.

VxE
  • Moderators
  • 3622 posts
  • Last active: Dec 24 2015 02:21 AM
  • Joined: 07 Oct 2006
Here is a more in-depth explanation of the listview interaction functions in my table library.

Table_FromListview( scf )
    Firstly, I hope it's clear to everyone that listviews are inherently tabular and that extracting a listview's contents into a container with a tabular format requires no stretch of the imagination. The only tricky thing about this function is the parameter 'scf'.

If 'scf' contains a lowercase 's', then the returned table will only contain rows that were selected in the listview. If 'scf' contains a lowercase 'c', then only the checked rows are returned. However, if 'scf' contains BOTH 's' and 'c', then only rows that are both selected AND checked will end up in the output table. Similarly, a lowercase 'f' indicates the focused row (if it exists).

The uppercase 'S', 'C', and 'F' do not restrict which rows are in the output table. Instead, they add columns to the right side of the output table named 'select', 'check', and 'focus', respectively. Each row will have the word 'select', 'check, or 'focus' as the value for that column if it has that attribute.


Table_ToListview( Table, Mode, MatchCol )
    This function is much more complicated because it has the power to add, remove, and modify both columns and rows. To handle these powers, 'Mode' is split into 3 parts.

!! IMPORTANT !! is is NOT necessary to use a table with the same column order as the listview, or even the same columns.

'Mode' part-1 is the lowest 3 bits, or in plain words, an integer between 0 and 7. This part of the mode determines how rows are treated when applying the table to the listview.
  * Mode: 0 - No listview rows are effected.
  * Mode: 1 - The listview is emptied, then the table's rows are added.
  * Mode: 2 - The listview is emptied, then the table's rows are added (selected rows are noted).
  * Mode: 3 - The listview is emptied, then the table's rows are added (selected rows are noted).
  * Mode: 4 - The listview is emptied, then the table's rows are added (selected and checked rows are noted).
  * Mode: 5 - Listview rows are updated using the table, but no rows are added.
  * Mode: 6 - Table rows are added to the listview.
  * Mode: 7 - Table rows are used to update listview rows, but are added to the listview if matching rows aren't found.

'Mode' part-2 is bits 5 to 8, and they modify the first part of the mode (bit #4 is currently unused).
  * Mode: +16   (+0x10) - Prevent duplicate rows from being added to the table. This only applies if part 1 does not equal 0 or 5. Duplicates are determined using the 'MatchCol' (see below).
  * Mode: +32   (+0x20) - If the table contains columns that aren't in the listview, add them to the right side of the listview.
  * Mode: +64   (+0x40) - If the listview contains columns that aren't in the table, delete them.
  * Mode: +128 (+0x80) - The topmost table row is used as column options for the columns specified (see LV_ModifyCol for valid options).

'Mode' part-3 is bits 9 to 16, and they indicate the index of the listview column to force to be the match column.

'MatchCol' is short for 'Match Column' and it refers to a column (both the table and the listview have one) used to identify a row during the operation. For example, suppose a listview has 3 columns, A, B, and C and you were to apply a table with 3 columns ( C, B, and D ) to it. By default, the MatchCol for the table would be its first column ('C'), and by default the listview's MatchCol would be the column with the same name. However, by setting the 'MatchCol' parameter to 'B', both the table's and listview's 'MatchCol' would be 'B' (if 'MatchCol' were set to 'D', it could cause an error, because the listview doesn't have a column 'D').

'Mode' part-3 can override the default behavior of looking for a listview column with the same name as the table's 'MatchCol'. In this example, by adding 256 (0x0100) to the 'Mode', the listview's first column ('A') would be its 'MatchCol', regardless of the table's 'MatchCol'.

The 'MatchCol' is used to determine whether a new row should be selected or checked (for mode part-1 = 2,3,4) and which listview row should be updated (for mode part-1 = 5,7) by creating a lookup list with the contents of each cell in that listview column. When the function is ready to add a new row, it check's the row's value in the table's 'MatchCol' against the list to see what it should do.

TLM
  • Administrators
  • 3864 posts
  • Last active:
  • Joined: 21 Aug 2006
Thanks so much VxE for this! Cant believe I missed it..

Posted Image

don't duplicate, iterate!


VxE
  • Moderators
  • 3622 posts
  • Last active: Dec 24 2015 02:21 AM
  • Joined: 07 Oct 2006
YWVM, TLM. I think you'll also be interested in the two functions new to v.05.

Table_ColToList(Table, Column, Delimiters)
    The output of this function is a delimited list containing the values from the 'Column' column in 'Table'. This makes it easy to insert a table column into a gui dropdown list ( GuiControl,, MyDDL, % "|" . Table_ColToList( Employees_Table, "Name", "|" ) )

Additionally, a list of delimiters can be used if the value of 'Delimiters' begins with "multi" (the character following the 'i' is the delimiter for the list of delimiters, if you follow). For example, "multi,+,-" would mean that the first and second values would be separated by a '+', the second and third values would be separated by a '-', and the third and fourth values would be separated by a '+', and so on.

Updated: Table_Aggregate(Table, Column, Fn, GroupBy, Round)
    This function calculates metadata from the table, based on the specified column. For example, Table_Aggregate( Fruits_Table, "Weight", "Med" ) would return the median weight for the entire Fruits_Table.
'Fn' must be one of the following: sum, min, max, med, avg, span, count, dev. By now you're probably wondering what the point of 'count' is... well... read on...

The 'GroupBy' parameter allows you to specify a column that contains ( typically non-unique ) identifiers for each row. In the 'Fruits_Table' example, such a column might be "Color", or "Peak Season". Whatever the column is, the function calculates the requested metadata for the 'Column' column for each unique value in the 'GroupBy' column. The return value is then a table with two columns, one for the group ID, the other for the metadata.

So, the function Table_Aggregate( Fruits_Table, "Weight", "Avg", "Color", 2 ) would return a table that looks like this:Color        Weight
Red          1.23
Yellow      4.56
Orange    7.89Where the value in the second column is the average for each fruit in each color category.

New in version 0.23: the 'round' parameter may be an integer to tell Table_Aggregate to round the return value(s) to that many decimal places. This is handy when dealing with money values in a table.

VxE
  • Moderators
  • 3622 posts
  • Last active: Dec 24 2015 02:21 AM
  • Joined: 07 Oct 2006
Version 0.23 brings some major changes to certain functions. Table_GetCell now has an option for multiple output values. Table_Update has the 'operations' parameter removed, and Table_FromINI has been significantly altered.

UPDATED: Table_GetCell( Table, RowID, Column_Or_Columns, output_1, ..., output_26 )
    Table_GetCell still only retrieves cell values from a single row. RowID specifies which row to use. RowID can be in any of the following formats: "`t<row index>", "<leftmost cell value>", "<column name>`t<cell value>", or "`t<column index>`n<cell value>".
To get more than one value out of the row at a time, use a list (newline separated) of column ids in the third parameter, then, for each column specified, provide an output variable in the subsequent parameters (max = 26 output vars).

Table_FormatTime( Table, Format, Columns )
    This function performs a FormatTime operation on the contents of each cell in the specified columns. I wrote this function specifically to prepare a table for use with Table_ToListview.

Table_FromINI( Text_In_INI_Format )
    I have significantly overhauled this function for version 0.23. Now, this function returns a 3-column table with the columns named 'Section', 'Key', and 'Value'. Each key/value pair is represented in one row of the table. In practice, this turned out to be much easier to use than the original version.

Table_SpacePad( Table, MinPadCount, PadChar )
    This function converts a 9/10 table by replacing the tabs between cells with enough 'PadChar' to make all the cells in a column have the same length. When viewing the output of this function in a monospace font, the columns will all line up. 'MinPadCount' indicates the number of extra characters to append to the longest cell in each column. If 'MinPadCount' is negative, it indicates the absolute width of each column.

UPDATED: Table_Update( TableA, TableB, MatchColA, MatchColB )
    Table_Update is another function that received a major overhaul in version 0.23. Since providing a ful, inline expression parser is frankly beyond this library's scope, and a limited hack like appeared in the original version just isn't good enough, I decided to remove any qualifiers on the update operation.
In case you didn't catch that, it means these functions ONLY do full cell text replacements. The cell values in TableB are copied into the matching cells in TableA.

Table_UpdateAppend( TableA, TableB, MatchColA, MatchColB )
    Similar to Table_Update, except that rows in TableB that weren't used to update TableA are appended to the bottom of TableA.

Table_Width( Table )
    Returns the number of columns in the table, specifically 1 + the number of tab characters to the left of the first newline character. This function will return "0" if the input is empty, or if the first character is a newline.

VxE
  • Moderators
  • 3622 posts
  • Last active: Dec 24 2015 02:21 AM
  • Joined: 07 Oct 2006
Version 0.23 brings the following changes.

Table_Between( Table, Column, GreaterThan, LessThan )
    Removes rows from the table if the value in 'Column' does not satisfy one or two inequalities. Specifically, 'GreaterThan' holds the minimum value that is considered OK to let a row stay in the table and 'LessThan', is the maximum value. In other words, only rows with a value between 'GreaterThan' and 'LessThan' remain in the table.

This function has three additional modes. If the value in 'GreaterThan' is GREATER than the value in 'LessThan', the operation is inverted, and the rows with a value between them are removed. If either of the parameters is left blank, the function does not check that inequality.

Finally, preceding either of the two limit values with a tab character toggles the equality portion of the comparison. Here's a summary of the conditions:
Conditon           Mode          Keep Rows If...
GreaterThan <=  LessThan  Normal        GreaterThan <= RowValue <= LessThan
GreaterThan >   LessThan  Inverted      RowValue < GreaterThan OR LessThan < RowValue
GreaterThan =   ""        LessThan      RowValue <= LessThan
LessThan    =   ""        GreaterThan   GreaterThan <= RowValue


Table_FromHTML( HTML_Text, StartingPos )
    This function examines the input text and attempts to translate the next set of '<table></table>' tags into a 9/10 table. StartingPos may be used to tell the function where the desited '<table>' tag is in case the input text has more than one such table. NOTE: Other HTML tags will remain unchanged inside the table. If this is undesirable, use RegExReplace( table, "<[^>]*>" ) to remove the remaining tags from the converted table.


Table_Join( TableA, TableB, JoinType, MatchColA, MatchColB )
    Combines one or two tables by appending columns from TableB onto the right side of TableA. The 'JoinType' can be "inner", "outer", "left", or "right", and may be qualified with the string "1:1".
The join type determines how the function handles rows with no matches, for "left", "right", and "outer" joins, rows with no match in the other table are merged with a null row and added to the output table.
The "1:1" qualifier tells the function not to match rows that have already been a match for a previous row. Finally, this function has a special mode for joining a table to itself. If 'TableB' is the word "self", TableA will have its rows copied, then joined back to different rows.


Table_RemCols( Table, Columns )
    This is easy enough to explain. Table columns are removed by this function. To remove more than one at a time, separate the column ids with newline characters.


Table_RemRows( Table, RowIds, MatchCol )
    Functionally silimar to Table_Deintersect, this function removes table rows if they have a value in 'MatchCol' that matches one of the values in RowIds. What makes it different is that RowIds may contain cell values and/or row indexes, so it can remove rows by their ordinal position instead of by their contents.


Updated: Table_SetCell( Table, RowId, Column, Value )
    Replaces the indicated table cell with the contents of 'Value'. With version 0.23, this function has the ability to identify a row based on a cell value other then the leftmost one in the row. To use this feature, precede the RowID's cell value with the column id and a newline character. For example, Inventory := Table_SetCell( Inventory, "UPC`n123451234512", "Price", "2.99" )


Table_Validate( String )
    Removed

Version 0.24 comes with the following:

Table_Reverse( Table )
    Reverses the order of rows in the table. NOTE: this function uses the MoveMemory function via DllCall, but I have tested it with both AHK 1.0.48.05 and 1.1.00.00 (unicode 32bit).

Table_FromXMLNode( XML, NodeName )
    Converts an XML node array into a table. This is far from a pure translation. When I wrote this function, I needed a versatile way to convert XML lists (arrays, or whatever) into a more usable format. So, this function takes XML and the name of a node and returns a table where each row is composed of the contents of the subnodes of each 'a' node in the document. Here's an example:
XML =
( %
<?xml version="1.0" encoding="UTF-8"?>
<GetAccountResponse xmlns="urn:ebay:apis:eBLBaseComponents">
	<Ack>Success</Ack>
	<AccountEntries>
		<AccountEntry>
			<AccountDetailsEntryType>BuyItNowFee</AccountDetailsEntryType>
			<Description>Buy It Now Listing Fee</Description>
			<Date>2011-05-01T14:39:33.000Z</Date>
		</AccountEntry>
		<AccountEntry>
			<AccountDetailsEntryType>FeeInsertion</AccountDetailsEntryType>
			<Description>Insertion Fee</Description>
			<Date>2011-05-01T14:39:33.000Z</Date>
		</AccountEntry>
	</AccountEntries>
</GetAccountResponse>
)
msgbox % Table_FromXMLNode( XML, "AccountEntry" )
/*
The message box shows the following table:
AccountDetailsEntryType    Description                    Date
BuyItNowFee                Buy It Now Listing Fee         2011-05-01T14:39:33.000Z
FeeInsertion	            Insertion Fee                 2011-05-01T14:39:33.000Z
*/


VxE
  • Moderators
  • 3622 posts
  • Last active: Dec 24 2015 02:21 AM
  • Joined: 07 Oct 2006
Versions 0.24 through 0.27 were mostly little stuff. Version 0.28 brings Table_FromLvHwnd(). This function extends the ControlGet,, List command where the target listview has a header.

Table_FromLvHwnd( listview_hwnd, selected_or_focused )
   Returns a 9/10 table containing a copy of the listview's contents, including its header. Parameter 2 can either be blank (to return the entire contents), the word "selected", or the word "focused", exactly as specified for the 'options' parameter of ControlGet,, List.

edik76
  • Members
  • 8 posts
  • Last active: Nov 04 2015 08:37 PM
  • Joined: 16 Jul 2012
Congratulations ! It's a work of Giant.
 
This functions are fabulous, but unfortunately the download links are dead. Could you provide new ones?
 
Thank you in advance.


Preactive
  • Members
  • 30 posts
  • Last active: Dec 29 2015 09:18 PM
  • Joined: 17 Jul 2013

I am using Table_append

 

And I am geting this 

   03 	         9628  	  133.72  	   6202.64  	   46.39 	    5.56 	 17.85  	 99.22  	   10.71  	        .34 	         	       2 	         	            	         72 	   .78 	          4 	        771 
   05 	         3366  	  112.21  	   6688.52  	   59.61 	    3.33 	 22.39  	 97.03  	    8.97  									
   07 	         9056  	  104.09  	   5169.73  	   49.67 	    1.15 	 15.34  	 98.01  	    8.68  									
   09 	        12673  	  137.75  	   6462.13  	   46.91 	    2.17 	 20.61  	 99.63  	   11.82  									
   12 	        34096  	  112.53  	   5983.70  	   53.18 	    2.31 	 19.90  	 99.02  	    9.12  									
   13 	         9454  	  118.18  	   6289.02  	   53.22 	   42.50 	 23.67  	 95.11  	    9.98  									
   16 	        43653  	  125.08  	   6386.98  	   51.06 	    2.87 	 18.93  	 98.99  	    9.07  									
   17 	        38920  	  128.45  	   6430.99  	   50.07 	   22.11 	 19.24  	 99.28  	   10.61  									
   18 	        21149  	  141.94  	   6542.70  	   46.09 	    4.03 	 21.54  	 95.47  	    9.92  									
   19 	         5116  	   93.03  	   6172.63  	   66.35 	   12.73 	 22.29  	 94.49  	    7.25  									
   21 	        34124  	  120.15  	   4846.13  	   40.33 	    3.52 	 14.68  	 96.09  	    9.55  									
   22 	         6297  	  101.56  	   6065.85  	   59.72 	    8.06 	 20.90  	 92.68  	    8.37  									
   23 	          286  	  142.96  	   3320.79  	   23.23 	   50.00 	 12.43  	100.00  	   11.50  									
   29 	        25635  	  144.02  	   6241.09  	   43.34 	    2.81 	 17.84  	 97.94  	   11.17  									
   30 	        11992  	  126.23  	   6358.02  	   50.37 	   10.53 	 18.39  	 99.08  	   10.25  									
   35 	        30703  	  123.30  	   5361.87  	   43.49 	   12.05 	 13.53  	 95.58  	    9.63 									
									
									        .09 	         	       1 	         	            	         30 	  2.97 	          1 	        269 
									        .44 	       1 	       1 	         	          1 	         87 	  1.99 	          1 	        755 
									        .42 	       2 	       2 	         	          1 	         92 	   .37 	          2 	       1087 
									       1.16 	      10 	       3 	       1 	          3 	        303 	   .98 	          7 	       2764 
									        .36 	         	       2 	         	            	         80 	  4.89 	         34 	        798 
									       1.67 	       8 	       4 	         	          3 	        349 	  1.01 	         10 	       3165 
									       1.30 	       8 	       4 	       1 	          2 	        303 	   .72 	         67 	       3214 
									        .77 	       3 	       2 	         	          3 	        149 	  4.53 	          6 	       1478 
									        .27 	       2 	       2 	       1 	            	         55 	  5.51 	          7 	        399 
									       1.78 	      13 	       4 	         	          2 	        284 	  3.91 	         10 	       2711 
									        .30 	       4 	       1 	       1 	            	         62 	  7.32 	          5 	        519 
									        .02 	         	         	         	            	          2 	       	          1 	         23 
									       1.03 	       7 	       2 	         	          1 	        178 	  2.06 	          5 	       1988 
									        .37 	       1 	       3 	       1 	            	         95 	   .92 	         10 	        974 
									       1.01 	       5 	       5 	         	          2 	        249 	  4.42 	         30 	       2399 
																	

The First row is correct but past that its is no on the right row level.  (you can paste that whatever flavor of Spreadsheets you prefer so see it better)

The Chunk that this two rows down but in the right column alignment is what I want. just like the first row.

I tired all the modes but to no avail.   Joined := Table_Append( PGOneLeft, PGOneRight,1) is the one that gave me the closest to working results.

 

I scrapping this data off of a green screen where I have user numbers on the left page and then I have to Page right to see the continuation of that line and so on.  

 

I can rewrite it so it pages right for everline item but that's crazy looking.

 

Thanks to any points in the right direction.  

 

Full Script

SetTitleMatchMode 2
DetectHiddenText, on
Process, priority, AutoHotkey.exe, high
Process, priority, tn525032.exe, high

SetBatchLines, 20ms
SetKeyDelay, 10ms
SetMouseDelay, 10ms
SetWinDelay, 10ms
SetControlDelay, 10ms
SetWorkingDir , %A_ScriptDir%

#NoEnv
#include DDE.ahk
#include RumbaDDE.ahk
#include Table.ahk
#singleinstance force

Apptitle = AS/400 Pull

secvarorg = 2  ;Number of seconds to wait till autoconnecting to AS400
	
GUI:
Gui, Color, 0xbbd5f4
Gui, Margin, X
Gui, Font, norm
Gui, Font, s7
Gui, Add, Button, gddecount W130, Please Wait...
Gui, Add, Button, gRunIAFEP W130, Please Wait...
Gui, Add, Button, gSetup W130, Run Rumba Setup
Gui, Add, Button, gBUTTONSTOP W130, STOP
;Gui, Add, Button, gddecount W130, count

Gui, Add, GroupBox, YM x+20 section W150 H130, AS/400 Session Select
Gui, Add, Radio, xs+10  ys+20 vRumbaA  gButtonConnect checked	, Session A
Gui, Add, Radio, xs+10  ys+40 vRumbaB  gButtonConnect 			, Session B
Gui, Add, Radio, xs+10  ys+60 vRumbaC  gButtonConnect 			, Session C
Gui, Add, Radio, xs+10  ys+80 vRumbaD  gButtonConnect 			, Session D
Gui, Add, Radio, xs+10  ys+100 vRumbaT gButtonConnect 			, Session Test

Gui, Add, Text, Ym x+70 W100,Auto-Connecting
Gui, Add, Text,  W100,( In %secvarorg% Seconds )
Gui, Add, Text,  W100,
Gui, Add, Text,  W100,
Gui, Add, Text,  W100,

Gui, Show, , %Apptitle%

loop, %secvarorg%
{
sleep,1000
secvarorg--
seccountdown := "( In " secvarorg " Seconds )"
GuiControl,, Static2 , %seccountdown%
}

If ( RumbaB > 0 || RumbaC > 0 || RumbaD > 0 || RumbaT > 0)
{
return
}

goto buttonConnect
return
;~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`


;~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
ButtonStop:
DDE_Kill()
Return
;~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`


;~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
buttonConnect:
Gui, Submit, nohide
Start := A_TickCount
SetTimer, ActivateWinUM, 100

if RumbaA = 1
{
Rumbawsf :="A.WSF"
}
if RumbaB = 1
{
Rumbawsf :="B.WSF"
}
if Rumba C = 1
{
Rumbawsf :="C.WSF"
}
if RumbaD = 1
{
Rumbawsf :="D.WSF"
}
if RumbaT = 1
{
Rumbawsf :="TEST.WSF"
}

DDE_Connect("Rumbawsf",Rumbawsf)
DDE_Kill()
Sleep,250
DDE_Connect("Rumbawsf",Rumbawsf)

SetTimer, ActivateWinUM, off
if DDEErrorCount > 2
{
GuiControl,, Static1 , % "Failed Connection"
GuiControl,, Static2 , % "Timeout After: "
GuiControl,, Static3 , % round(connecttimercount) " Seconds"
Return
}
Else
{
ToolTip,||||||||||||||||||||||||||||||||`n||||||||||||||||||||||||||||||||`n||||||||||||||||||||||||||||||||`n`nDDE CONNECTED to %Rumbawsf%`n`n||||||||||||||||||||||||||||||||`n||||||||||||||||||||||||||||||||`n||||||||||||||||||||||||||||||||
SetTimer, RemoveToolTip, 1000
GuiControl,, Static1 , % "Connected"
GuiControl,, Static2 , To: %Rumbawsf%
GuiControl,, Static3 , % "In: " connecttimercount " Seconds"
GuiControl,, Button1, % "Run Create List" 
GuiControl,, Button2, % "Run Delete List" 

return
}
Return

ActivateWinUM:
MouseGetPos,,, WinUMID
ToolTip,Please wait `n`nDDE Connecting to %Rumbawsf%

GuiControl,, Static1 , % "Connecting to"
GuiControl,, Static2 , % "To: " Rumbawsf
connecttimercount := SubStr("0" Floor((A_TickCount - Start) / 1000 - Floor((A_TickCount - Start) / 60000) * 60), -1, 2) "." SubStr("0" Mod(A_TickCount - Start, 1000), -1, 2)
GuiControl,, Static3 , % "For: " connecttimercount " Seconds"

if connecttimercount >= 15
{
DDE_Kill("Rumbawsf",Rumbawsf)
SetTimer, ActivateWinUM, off
ToolTip
msgbox, Make sure the appropriate Rumba Session is Open.
}
return

RemoveToolTip:
SetTimer, RemoveToolTip, Off
ToolTip
return
;~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`


;~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
Setup:
Rumba := "ahk_class WdPageFrame"
winactivate, %Rumba%
winwaitactive, ahk_class WdPageFrame
IfWinNotActive, %Rumba%, , WinActivate, %Rumba%,	;activates a window if it is not already active
WinWaitActive, %Rumba%, 		
;---Options>Edit
WinMenuSelectItem, %Rumba%,, Options , Edit 										;opens a windows menu
winwait, Edit Options
;---Options>Edit>Features
Control, Check,, button1, Edit Options
sleep, 50
Control, Uncheck,, button2, Edit Options
sleep, 50
Control, Uncheck,, button3, Edit Options
sleep, 50
;---Options>Edit>Parsing
SendMessage, 0x1330, 2,, SysTabControl321, Edit Options
sleep, 50
Control, check,, button2, Edit Options
sleep, 50
WinWait, Edit Options, 
IfWinNotActive, Edit Options, , WinActivate, Edit Options, 
WinWaitActive, Edit Options, 
;---Options>Edit>Ok Button on Parsing Tab
control, check,, button13, Edit Options
;---Options>Display
WinMenuSelectItem, %Rumba%,, Options , Display
winwait, Display Options
sleep, 50
Control, Uncheck,, button8, Display Options ;Input Error Dialog
WinWait, Display Options, 
IfWinNotActive, Display Options, , WinActivate, Display Options, 
WinWaitActive, Display Options, 
;---Options>Display>Ok Button on Display Tab
control, check,, button12, Display Options
SetTitleMatchMode, fast
return
;~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`

RunIAFEP:
WaitStringUni("IND2762")
Loop 16
{
Cashier := AutoBlockUni((8+a_index),8)
RemoveInString(Cashier,,"C N R T") 
;---
fesls := AutoBlockUni((8+a_index),47)
RemoveInString(fesls,,"C N R T")
;---
avgodr := AutoBlockUni((8+a_index),58)
RemoveInString(avgodr,,"C N R T")
;---
MonPerSgnOnHR := AutoBlockUni((8+a_index),71)
RemoveInString(MonPerSgnOnHR,,"C N R T")
;---
MPH := AutoBlockUni((8+a_index),83)
RemoveInString(MPH,,"C N R T")
;---
SgnOffPerNumTans := AutoBlockUni((8+a_index),97)
RemoveInString(SgnOffPerNumTans,,"C N R T")
;---
IPM := AutoBlockUni((8+a_index),107)
RemoveInString(IPM,,"C N R T")
;---
ScanPercent := AutoBlockUni((8+a_index),117)
RemoveInString(ScanPercent,,"C N R T")
;---
ItemsPerMemb := AutoBlockUni((8+a_index),129)
RemoveInString(ItemsPerMemb,,"C N R T")

PGOne .= cashier a_tab fesls a_tab avgodr a_tab MonPerSgnOnHR a_tab MPH a_tab SgnOffPerNumTans a_tab IPM a_tab ScanPercent a_tab ItemsPerMemb "`n"
}
SendDDEUni("@k")
Loop 16
{
InaTime := AutoBlockUni((8+a_index),42)
RemoveInString(InaTime,,"C N R T") 
;---
NumOur := AutoBlockUni((8+a_index),52)
RemoveInString(NumOur,,"C N R T")
;---
Pickups := AutoBlockUni((8+a_index),62)
RemoveInString(Pickups,,"C N R T")
;---
VoidTrans := AutoBlockUni((8+a_index),72)
RemoveInString(VoidTrans,,"C N R T")
;---
FERew := AutoBlockUni((8+a_index),84)
RemoveInString(FERew,,"C N R T")
;---
NumOfMembs := AutoBlockUni((8+a_index),96)
RemoveInString(NumOfMembs,,"C N R T")
;---
PercentKeyed := AutoBlockUni((8+a_index),105)
RemoveInString(PercentKeyed,,"C N R T")
;---
SignOffs := AutoBlockUni((8+a_index),117)
RemoveInString(SignOffs,,"C N R T")
;---
NumItems := AutoBlockUni((8+a_index),129)
RemoveInString(NumItems,,"C N R T")

PGTwo .= InaTime a_tab NumOur a_tab Pickups a_tab VoidTrans a_tab FERew a_tab NumOfMembs a_tab PercentKeyed a_tab SignOffs a_tab NumItems "`n"
Joined := Table_Append( PGOneLeft, PGOneRight,1)
}
SendDDEUni("@k")
/*Loop 16
{
InaTimePerMem := AutoBlockUni((8+a_index),42)
RemoveInString(InaTime,,"C N R T") 
;---
TendTimePerMem := AutoBlockUni((8+a_index),52)
RemoveInString(TendTimePerMem,,"C N R T")
;---
RingTimePerMem := AutoBlockUni((8+a_index),62)
RemoveInString(RingTimePerMem,,"C N R T")
;---
NonSlsTimePerMem := AutoBlockUni((8+a_index),72)
RemoveInString(NonSlsTimePerMem,,"C N R T")
;---
TotTimePerMem := AutoBlockUni((8+a_index),84)
RemoveInString(TotTimePerMem,,"C N R T")
;---
TotSgnOnTime := AutoBlockUni((8+a_index),96)
RemoveInString(TotSgnOnTime,,"C N R T")

PGThree .= InaTimePerMem a_tab TendTimePerMem a_tab RingTimePerMem a_tab NonSlsTimePerMem a_tab TotTimePerMem a_tab TotSgnOnTime "`n"
}
*/
;clipboard := PGO
msgbox % Joined
clipboard := Joined
return
;~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`


Joe Glines
  • Members
  • 118 posts
  • Last active: Jan 24 2016 03:08 PM
  • Joined: 23 Dec 2009

VxE thank you so much for your work in this!   :D

 

I've been seeking an alternative for my main stat package and this will provide the ground-work for a lot of what I need!  I'm playing with it and am getting most to work but the Avg command returns nothing and Standard Deviation are returning zeros (most probably because the Avg is blank)   Any idea what's going on?

 

Also, I don't remember where I found it but the above links for downloading do not work.  I have version .28 (and see version .27 on Github) but was wondering if you wanted to update the above to allow others to download.


Automating the mundane 1 script at a time...
https://www.linkedin.com/in/joeglines
The-Automator