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.
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 "com.apple.iWork.Numbers" -- 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
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:
This works with the CSV file exported from my little sample table in Numbers above.
(Untitled.csv.zip (918 Bytes) )
PS:
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.
@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.
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(!)
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.
