Is this a feasible Macro? Extracting details from text file?

Hello all,

Would appreciate some confirmation if it is worth my time to go down this rabbit-hole – i.e., would this be feasible?

I sit with emails coming in that contain unique "client" identifiers – as in, only that client has that specific numerical number associated with them.
I then need to enter that numerical number into our CMS to open up our database information about the client.
When saving PDFs/records from the CMS, I use a combination of Hazel and Textexpander to save each file with a particular filename structure, as in: [date]-[description]-[client name]-[unique numerical number].

Would KM be able to run a search through a text file/Excel/Numbers spreasheet, find the numerical number, and copy the adjacent cell, that would have the client's name-surname, to the clipboard?

I could then use that combination (name-surname_client number) in later macros, to save the files I print-to-PDF.

Would that be possible? Or would it possibly be more hassle than its worth.
I might point out that I currently copy the name manually from inside the CMS, anything from 5 to 20/30 times a day – and occasionally, up to 50 times a day.

With this in Numbers:

15-pty-fs8 (55.5 KB)

…and assuming the sheet is open in Numbers, this works for me:

[test] Search for Number and Return Adjacent Cell in (3.4 KB)


  • In the sheet, the cell with the “unique numerical number” has to be formatted as number. If it is formatted as text, remove the as number in line 3 of the script.
  • Not tested with a large dataset; maybe it is too slow.
    • If the unique number is always in the same column, you could limit the search to that column.

The contained script:

tell application id "com.stairways.keyboardmaestro.engine"
  set inst to system attribute "KMINSTANCE"
  set targetNumber to (getvariable "Local__Search for Number" instance inst) as number
end tell

tell application id "" -- Numbers
  tell document 1
    tell active sheet
      tell table 1
        set theMatch to (first cell whose value is targetNumber)
        set {r, c} to {row's address, column's address} of theMatch
        tell row r to set adjacentCell to cell (c - 1)
        value of adjacentCell
      end tell
    end tell
  end tell
end tell

Wow! Really appreciate your help – will jump in then, and see if I can get everything to work!

As an alternative, something could be written combining Keyboard Maestro and Python (Python Example Code? would be a useful resource) with the Openpyxl library to read an xlsx file even if it is not open on your mac at the time.

If all you want to do is look up the client's first and last name based on the number, then I would stored the list in a simple text file. If you have that information already stored in a spreadsheet, then you can easily make a corresponding text file simply by copying the range of cells that contain the numbers and names and pasting into a text file (e.g., BBEdit). Then, do a search & replace to turn the tabs that separate the number, the first name, and the last name into a string of characters of your choice that will act as a delimiter. Something like &&& will work well. To search for tabs within BBEdit, for example, search for \t. Put &&& in the replace box, then choose replace all. After the text file is generated, you can create a Prompt for User Input action within KM. There is a very similar macro that I posted here:

1 Like

If you have the data in an exported text file (CSV), you can use something like this:

[demo] Search for Number and Return Adjacent Cell in CSV File.kmmacros (3.8 KB)

This works with the CSV file exported from my little sample table in Numbers above.
( (918 Bytes) )


Of course, you can also export the table as a tab-delimited text file (TSV). The only change you have to make then is adapting the delimiter in the script.

Here a tab-delimited export: (919 Bytes)

1 Like

@Cassady, as you have seen there are a number of solutions that KM can provide. It mostly depends on what you actually want your data source to be.

If your master source is Excel or Numbers, then the approach I would take, which I've use many times, is to export the data to a TAB delimited text file whenever you update the master. Of course, you could have KM check the Modification Date of the Excel file and the export file, and if Excel is more recent, then do the export from KM.

Once you have the data in a tab-delimited text file, I would simply use the KM Search using Regular Expression action to search the tab-delimited file, and find the numerical ID and extract the client name. This is very easy, and very fast.

I have built many KM macros using this method.

If you are interested in this approach, let me know and I'll provide details.

Inspired by @JMichaelTX’s suggestion…

I would simply use the KM Search using Regular Expression action

…here an example macro using a regex:

[demo] Search for Number and Return Adjacent Cell in Text File (KM Regex).kmmacros (4.1 KB)

This works with tab-delimited and with comma-delimited files, tested on the sample files from above.
(Though, once you have decided which format you are going to use, you should optimize the regex for that format.)

Though I’m not a friend of using regexes where it isn’t necessary, I have to admit that this is way faster than the AppleScript solution from above:

On a file with 10K lines (rows) where the AppleScript took 45 seconds, the KM regex did the job almost instantly(!)

So, credits go to @JMichaelTX

1 Like

Oh wow. Thank you to all for the replies. Plenty to try here - will leave this all for the weekend, to really get my teeth into! Thank you all again!!

When you try the macros, I suggest to first try them with the minimal sample files I provided throughout the posts. This way we know that any issues you run into are not caused by the input data but by other factors.

Once the macro(s) work for you with the minimal samples, try it on your real-world input data. Then we can make the necessary script/macro adjustments.

Here all the sample file downloads in one place: (55.5 KB) (918 Bytes) (919 Bytes)