2
\$\begingroup\$

Code Objective

I'm writing an AutoHotkey script which takes in department data copied to the clipboard from a Microsoft Excel spreadsheet. It then uses this data to auto-fill forums in the web-app Kronos Workforce Central.

Source Data

When data is copied from Microsoft Excel, each cell in a row is stored in the clipboard as strings delimited by tabs \t, with each row separated by newlines \r\n.

DeptID     Job Name           Abbreviation     Service Line  
0368       Administrator      ADMIN            OPS
3945       Programmer         PRGRM            NON NRSG
4596       Software Engineer  SFTWRE-ENG       NON NRSG

Current Method

Currently, I'm parsing this copied-data by splitting the data into an array of rows with StrSplit(), then using a second StrSplit() inside a for-loop to parse my data.

; Autofill data from clipboard
#d::
   addLocationsWindow := "Add Locations"
   WinActivate, %addLocationsWindow%        ; Window MUST be active

   rowArray := StrSplit(Clipboard, "`r`n")  ; Split copied rows from Excel by newlines
   for index, row in rowArray {
      tempArray := StrSplit(row, "`t")      ; Split each row by tabs
      deptID := tempArray[1]
      jobName := tempArray[2]

      If (deptID == "")                     ; skip empty cells
         continue

      IfWinNotActive(addLocationsWindow)  ; check for active window
      {
         MsgBox '%addLocationsWindow%' window not found. Stopping script...
         Exit, 1
      }

      ; DoStuff(jobName, deptDisplayName, index)
   }
Return

The Issue

My method of string parsing feels hacky and unintuitive. It means I can only manipulate a single row at a time, as each each row's data is only split temporarily. I would need to re-run the costly StrSplit() to access data from a row again.

In Java, I could store my Excel data in a single 2D array. Unfortunately, arrays are a bit tricky to use in AutoHotkey, which is why my current code is a bit messy.

\$\endgroup\$

1 Answer 1

2
\$\begingroup\$

I realize this is an older question, but hopefully, this will help a future asker, if not the original asker.

I agree with your assessment of using StrSplit() and would recommend using a 2D array. This can be done using a nested parsing loop. Here is a working example of the data in your example (assuming the range was copied from Excel and not from your post):

f1::
aData := {} , nCt := 0 , clipboard := RegExReplace( clipboard , "`r" , "" )
Loop , Parse , clipboard , `n
{
    nCt++
    Loop , Parse , A_LoopField , `t
        aData[ nCt , A_Index ] := A_LoopField
}
Loop , % aData.Length()
    MsgBox , % aData[A_Index,1] . "`t" . aData[A_Index,2] . "`t" . aData[A_Index,3]
Return
\$\endgroup\$
2
  • \$\begingroup\$ @TobySpeight Thank you. This is not an alternative solution, but rather addresses what OP mentions under "The Issue" (also, this is just an example of how to use a multidimensional array in AHK and not meant to be a replacement of any section of code). The portion of the code this address is where StrSplit() is used. The OP mentions the drawbacks of using StrSplit(), which I agree with and edited to make clearer. \$\endgroup\$
    – EJE
    Mar 26, 2019 at 13:53
  • \$\begingroup\$ Thanks for clarification, and have an upvote! \$\endgroup\$ Mar 26, 2019 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.