Use the ListView GUI Control to Find Duplicate Entries in Data Table Files (AutoHotkey Legal ListView Part 2)

ListView Control Functions in a Loop Work Quickly Locate Repetitious Data

In my last blog, “ListView GUI Control for Viewing Data Table Files (AutoHotkey Legal ListView Part 1)“, I introduced using the ListView GUI control to view and correct a data table file—in this case, an INI file (LegalInput.ini). While sorting and viewing a data table in the ListView control offers many benefits, the most power comes from the 11 built-in functions available for manipulating the control and editing data.

All GUI controls (e.g. Edit, Text, MonthCal, etc.) offer options you can call with the initial Gui, Add command. ListView (and its sister TreeView) include similar options plus special functions for directly manipulating the control. Last time, we used LV_Add() to load the data table rows into the ListView control. This time, we use the LV_GetCount() function (the number of ListView rows) to limit the total number of iterations in a loop, LV_Modify() to focus on each table row in sequential order, and LV_GetText() to retrieve and store data in the row.

The Duplicate Problem

Once we implement our editing routine (coming soon), AutoHotkey will use the INI file key (the Hotstring column) to locate and update the entry in the file. However, only the first occurrence of the key pops up for the INIRead command search. The search cannot find any subsequent records (or rows) with an identical key. For that reason, in the original file, we must locate and change those duplicate INI file keys to unique values.

Last week’s script loads the INI file keys into the first column of the ListView GUI control. That means all the duplicated keys end up next to each other in the sorted column. We could manually scroll through the ListView looking for matching entries, but wouldn’t it be quicker and more accurate to write a script which locates identical Hotstrings? We do this by running a Loop which scans the entire file while comparing the current value for the INI key with that from the previous row. If we get a match, we stop the action.

Legal ListView Match Key
The routine for finding duplicates loops through the ListView while comparing the current value of the Hotstring with that of the last record. If matched, the scan pauses while displaying a message showing the key value. Click “Yes” to search for the next duplicate and “No” to break the loop.

This technique uses the basic Loop command which increments through the data until it encounters specified conditions. You might use a similar approach to search for data or locate records which match any criteria. In fact, this Loop setup may act as the basis for many additional ListView tools.

A ListView Loop

Library Benefits

You could use a simple Loop command with no parameters for incrementing through the table. However, to prevent accidental infinite loops, you would need to include a loop-exiting trap. A safer method for running a ListView loop limits the loop iterations to the number of rows:

Loop, % LV_GetCount()  ; Loop the number of rows
{

}

LV_GetCount() contains the number of rows in the ListView control. While nothing in the above loop currently forces a scan of the file, it limits the number of passes to the number of rows.

Tip: To use a variable with the Loop command, you must either employ macro substitution (%variable%) or force the expression with a single percent sign %, as shown above.

Now, we place the loop inside a Hotkey combination (CTRL+ALT+Q). This allows us to start the routine at any time without any extra ListView code:

^!q::
  Gui, LegalList:Default
  Loop, % LV_GetCount()
  {

  }
Return

Since the Hotkey initiates a new routine, we must tell AutoHotkey which GUI ListView we want to use (Gui, LegalList:Default). Otherwise, AutoHotkey looks for a default no-name GUI—coming up empty.

The ListView LV_Modify() Function

The LV_Modify() function uses the same row options as the LV_Add() function. This allows you to select a specific row (Select), set cursor focus by row number (Focus), force the row to scroll into view (Vis) in the ListView window, as well as, a number of other features. By employing the Loop command’s A_Index variable (which counts each iteration of the loop), we can step through the ListView one row at a time:

^!q::
Gui, LegalList:Default
Loop, % LV_GetCount()
{
  LV_Modify(A_Index, "Focus Vis") 
}
Return

Now, AutoHotkey loops through the GUI ListView rows without stopping.

The LV_GetText() Function for Retrieving Column Values

We want to identify matching keys in the first column. For that, we must save the current row value for comparison with the next row value.  The LV_GetText() function retrieves the contents of the row in focus and stores it in OutputVar (or any other specified variable):

LV_GetText(OutputVar, RowNumber [, ColumnNumber])

With no ColumnNumber assigned, AutoHotkey returns the data from the first column. Since we need to later compare this data to the next row, we assign it to another variable before starting the next loop iteration:

^!q::
Gui, LegalList:Default
Loop, % LV_GetCount()
{
  LV_Modify(A_Index, "Focus Vis") 
  LV_GetText(Text, A_Index)  ; Save value of current row

  [Code for action "if Text = TextCheck"]

  TextCheck := Text   ; Save value to check against next row
}
Return

Although this code captures both the last row’s key value (TextCheck := Text) at the end of the loop and the new row’s key value for comparison (LV_GetText(Text, A_Index)) at the beginning of the loop, until we add code for matching the two, the loop runs straight through to the end of the ListView.

Comparing Values from Two Rows

Legal ListView MsgBox
A list of all redundant INI keys.

We have a number of options for saving the matched data:

If (Text = TextCheck)

We could build a list of the keys by concatenating each matched term to a variable:

KeyList := KeyList . "`r" . Text

then, after running through the entire file, display the list using a MsgBox (as shown at right):

MsgBox %KeyList%

We could force the loop to stop on the first match:

If (Text = TextCheck)
  Break

then display the matching key with the MsgBox command:

MsgBox %Text%

But, I chose a combination of techniques which make use of a MsgBox to pause the loop when finding each match and displaying the key value:

If (Text = TextCheck)
 MsgBox, 4, ,Row %A_Index%, key value "%Text%"

This allows the editing of the original INI file before continuing.

Then, I added the option to Continue? or stop the loop:

 IfMsgBox No
  Break

The following routine jumps through the Legal ListView looking for redundant INI keys. When locating a fit, a MsgBox pauses the script while displaying the duplicate keys in the ListView window by scrolling to the proper location:

^!q::
Gui, LegalList:Default
Loop, % LV_GetCount()
{
  LV_Modify(A_Index, "Focus Vis") 
  LV_GetText(Text, A_Index)
  If (Text = TextCheck)
    MsgBox, 4, ,Row %A_Index%, key value "%Text%"
         .`r`rContinue? 
    IfMsgBox No
      Break
  TextCheck := Text
}
Return

You have the option to either continue the loop to the next match (“Yes”) or exit (“No”). (See the image at the beginning of this blog.)

This snippet of code represents a multipurpose routine for looping through any ListView. For example, you can convert the above loop into a search routine by replacing the center code with conditions matching a search term.

Next time, I plan to demonstrate how to build a second pop-up GUI for editing and saving the INI file data.

jack

This post was proofread by Grammarly
(Any other mistakes are all mine.)

(Full disclosure: If you sign up for a free Grammarly account, I get 20¢. I use the spelling/grammar checking service all the time, but, then again, I write a lot more than most people. I recommend Grammarly because it works and it’s free.)

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s