[Lib] String-based Table Manipulation v0.28
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.
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
Could you give an example of table file, please?
Thanks
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 ) exitappThe 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.
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
---------------------------
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.Nice and easy to use. Thanks for sharing.
I get one error:...
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.
don't duplicate, iterate!
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.
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.
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 */
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.
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 ;~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
VxE thank you so much for your work in this!
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.