Brute Force Data-Set Copy-and-Paste (AutoHotkey Clipboard Technique)

I Prefer an Eloquent Solution for Data-Set Transfer Problems, But Sometimes It’s Just Easier to Build a Simple (and More Universal) Copy-and-Paste Tool

*          *          *

I originally wrote the short script discussed in this blog to solve a single data transfer problem. Only after making the script functional did I realized that it could work in numerous different types of data-set transfer environments such as spreadsheets, Web tables, and many other information lists. This AutoHotkey script offers a little less tedious solution to common Windows cut-and-paste problems.

*          *          *

If I need to copy a single section of text into another document, then the Windows Clipboard works find. However, whenever I want to do something more complicated such as moving that same text into multiple data fields or copying data from a table in a document (or on the Web) before transferring it, using the Windows Clipboard can turn into a real hassle.

For example, if I want to replicate a transaction from my bank’s Web page in another finance software package, on its own, the Clipboard forces me to repeatedly jump between open windows while executing copy-and-paste operations. The standard copy (CTRL+C) and paste (CTRL+V) works like an ax when I need a multi-tasking scalpel. This situation prompted me to write an enhanced copy-and-paste AutoHotkey app.

Library Benefits

Note: In the past, I’ve recommended Clipboard-like AutoHotkey scripts such as ClipJump, written and maintained by Avi Aryan, which represents a major upgrade over the Windows Clipboard. This super Clipboard adds multiple clips and many other cool features for anyone needing more options in their copy-and-paste operations. But, I want something a little simpler. The ClipJump app offers much more than I need for my problem while adding considerably more complication. (You can find the ClipJump app discussed in the book AutoHotkey Applications: Ideas and Tips for Writing Practical AutoHotkey Scripts.)

Complications when Using the Windows Clipboard to Transfer Data-Sets

Even though I can copy (CTRL+C) transaction data-sets directly from the bank Web page, getting the various pieces into the proper fields in the finance program presents a number of hurdles:

  1. The finance program contains an individual input field for each piece of data requiring a separate paste (CTRL+V).
  2. The format for the bank Web page does not match the propriety binary format for a finance program transaction.
  3. Probably due to item number 2, even when I reformat the Web transaction contained in the Windows Clipboard into the seemly proper format, the paste (CTRL+V) operation does not work in the finance program.
  4. The input fields in the target program do not use individual Edit control names for use in an AutoHotkey script.
  5. Basic copy-and-paste generally works for individual data fields, but the hopping back and forth for each data-item quickly grows tedious.
  6. The date field requires special handling since it uses three separate input fields.

I did work on a fix which parsed the Clipboard data-set into individual items and used tabbing in the target program to fill in the transaction fields. This approach more-or-less worked but the variations in the bank data-sets caused problems in the field matching and occasional weird behavior in the receiving window.

If I had taken the time to come up with the perfect single-stroke solution, I may have found success. However, considering the limited number of times I need to execute this type of copy-and-paste, I felt that writing a simpler, yet effective, multi-paste tool would suffice. I sought to complete my data movement with only one window switch—even though I needed to place multiple data items into various fields.

My Motivation for Moving a Bank Transaction into the Financial Program

For years, I used a commercial software package to track my personal finances. I could directly import data from my various accounts via the Internet. I felt the cost reasonable since I only needed to renew the package every three years. When prorated over the three years, the software cost didn’t feel too high. However, the company who now owns the product decided to switch the software to a far more expensive annual fee—treating the software as a service. Although the software did an adequate job of handling my monthly activities, it was never that good. I opted out.

While the penalty for not renewing meant losing the download capability from my various accounts, I could continue to use the software’s bill payment reminders for managing my bank account. As I pay each bill, the software inserts the transaction into the bank account data table. Only occasionally do I encounter transactions in my bank account which I have not previously entered into the finance software’s table—often receipts from Amazon Kindle book sales or credit card transactions. These transactions I need to move by hand. That’s why I designed this short AutoHotkey tool which copies all of the target bank transaction data-set, then allows me to individually paste the appropriate data into the proper field without jumping back-and-forth between the two windows.

Only later did I realize that I could use the same tool for moving almost any type of data-sets from one document, table, or page into separate data fields in another window. I have since discovered that it operates just as well for spreadsheet data (copying to and from), as well as, most other query type windows. The fact that I wrote the script for a financial application does not prevent anyone from using it to copy and paste data-sets, such as medical test results, grades, or almost any other type of tabular data.

Flexible Cut-and-Paste

After pasting the transaction data-set I copied from my bank account Web page into an open page in LibreOffice wordprocessor with the display set to show Non-Printing Characters, I was able to see the special format characters saved in the Windows Clipboard. When I highlighted and copied a transaction (as shown in the image below), the cell separators in the Web HTML data table converted to the tab character (shown as by a small arrow in LibreOffice or `t for AutoHotkey). This gave me an easy key for parsing the data-set.

BankAccountHighlight
Highlight the transaction and use the Hotkey combination to copy and parse the data-set.

Copy and Parse the Transaction

Using the Standard AutoHotkey Window Clipboard Routine for copying the selected transaction, I wrote a short script which quickly parsed the data-set based upon the location of the tab character (`t):

^!F::         ; Hotkey combination CTRL+ALT+F
  OldClipboard := ClipboardAll
  Clipboard = ;clears the Clipboard
  SendInput, ^c
  ClipWait 0 ; pause for Clipboard data
  If ErrorLevel
  {
    MsgBox, Error message for non-selection
  }

; Replace all new paragraph marks with tabs for parsing
  Clipboard := StrReplace(Clipboard, "`r`n" , "`t")
; Just in case the data-set includes a stray `r or`n 
  Clipboard := StrReplace(Clipboard, "`r" , "`t") 
  Clipboard := StrReplace(Clipboard, "`n" , "`t") 
; Removes excess tabs 
  Loop 
  {
    Clipboard := StrReplace(Clipboard, "`t`t" , "`t")
    If ! Instr(Clipboard, "`t`t")
      Break
  }
; Sparse data-set
  Transaction := StrSplit(Clipboard , "`t", , MaxParts := -1) ; [v1.1.28} 
; Display MsgBox always-on-top (4096) for pasting into target fields 
; Word-wrapped using line continuation techniques
  MsgBox, 4096, , % "[1] " . Transaction[1] . "`r" 
. "[2] " . Transaction[2] . "`r" 
. "[3] " . Transaction[3] . "`r" 
. "[4] " . Transaction[4] . "`r" 
. "[5] " . Transaction[5] . "`r" 
. "[6] " . Transaction[6] . "`r" 
. "[7] " . Transaction[7] . "`r" 
. "[8] " . Transaction[8] . "`r" 
. "[9] " . Transaction[9]
  Clipboard := OldClipboard
Return

This routine activated by the Hotkey combination CTRL+ALT+F (^!f), copies the selected transaction, replaces new paragraph marks (`r`n) with the tab character (`t), removes excess tabs, then parses the data-set using `t as the delimiter.

BankTransactionCopy

By parsing the data-set and placing the individual items in the variable array Transaction[n] we can recall each piece of data using its array index. But to do that we need to see a list of the values while pasting specific data into individual fields. Therefore, AutoHotkey displays the data-set using the MsgBox command with the option 4060 (always-on-top). This allows us to move to another window for the pasting operation without memorizing the data locations. The MsgBox conveniently remains in view until we have completed our task.

Pasting Data into Multiple Fields

Due to the complications previously discussed, rather than coming up with a routine for pasting the entire transaction in all of the fields in one fell swoop, I simply made it easy to paste individual items into the selected field per the displayed MsgBox.

FinSWPasteBlur
By activating the Input command, a temporary Hotkey (in square brackets) pastes the item into the selected data field.

I could have created a Hotkey for each data item, but that could add both possibly conflicting Hotkeys and more complication. By using the Input command, this relatively short AutoHotkey routine creates temporary Hotkeys for inserting individual data items:

^!#w::
  Input, SingleKey, L2 T5, ,1,2,3,4,5,6,7,8,9
; Special action for date fields
  If (SingleKey ~= "\d\d?/\d\d?/\d\d\d?\d?")  ; Shorthand for RegExMatch
  {
    date := StrSplit(Transaction[SingleKey],"/")
    SendInput, % date[1] . "/" . date[2] . "/" . date[3]
  }
  Else If SingleKey != ""
    SendInput, % Transaction[SingleKey]
Return

Activate the paste action by selecting the target field and pressing the CTRL+ALT+WIN+W (^!#w) combination then press the number corresponding to the desired item appearing in the MsgBox.

The activated Input command terminates after pressing any number key 1 through 9—saving that number to the variable SingleKey. Then, depending upon the format (date or otherwise), AutoHotkey pastes the item (SendInput command) into the field. The script pays special attention to the date format because many programs require entry into a date field in three actions (month, day, year) separated by a slash (/). If a field does not require this format, the routine merely inserts the date as text.

The Complete Multi-Paste Script

To create the Multi-Paste script for your own use, copy each of the Hotkey routines above into the same AutoHotkey script file (.ahk). After running the script, the key combination CTRL+ALT+F copies any selected (highlighted) section of a document or Web page and parses it into its tab (`t) delimited parts. This also works for spreadsheets.

Next, use the items numbers displayed in the MsgBox window to paste individual items. Execute the key combination CTRL+WIN+ALT+W, then press the number key (1-9) listed next to the appropriate data item. After pasting all needed items, close the MsgBox to remove it from view.

More Information on these AutoHotkey Techniques

Virtually every AutoHotkey technique used in this script I’ve discussed in some detail in either another blog or one (or more) of my books. If you need more explanation of a particular aspect of the code presented here, checking the indexes in the books or using the search feature in this blog may help. The AutoHotkey Books page in this blog includes links to external pages which list the Table of Contents and Index for each book. Plus, you can find the same contents and index information for most of the books in the free book AutoHotkey Tricks You Ought to Do with Windows. For Jack’s Motley Assortment of AutoHotkey Tips book, see this Motley Tips page for the Table of Contents and Index.

Next Time, Parsing Addresses

Another paste problem involves copying addresses from the Web for insertion into separate fields (e.g. street address, city, state, zip code). On the Web, the street address format often appears on one line separated by commas. In the next blog, I add code to the Multi-Paste script to enable the parsing of addresses.

Click the Follow button at the top of the sidebar on the right of this page for e-mail notification of new blogs. (If you’re reading this on a tablet or your phone, then you must scroll all the way to the end of the blog—pass any comments—to find the Follow button.)

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.)

2 thoughts on “Brute Force Data-Set Copy-and-Paste (AutoHotkey Clipboard Technique)

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