Idiot's Guide to Extracting Specific Text From Excel File – and Placing on Clipboard?

Questions & Suggestions
, ,
#1

Hello all,

I hope someone can point me in the right direction here. I have searched the forums, and found posts/threads that have some of what I think I need, but not all – and I don't actually know what I should be searching for...

One of my most-used Alfred workflows has unfortunately broken with my updating to Monterey (due to Python 2 being deprecated), and I am hoping to replace it with a KM macro instead.

What it did:
1.) I triggered the Alfred macro, typed in a surname – and Alfred would parse a specific Excel spreadsheet, that had the following columns:
Surname >> Name >> Initials >> Title >> Client number.
2.) Alfred would display all the surname matches in its floating palette, including the "Name" on the secondary line – so that I could "see" both Surname and Name of the clients from the spreadsheet.
3.) I could then move up and down on that list, and hit "enter" on the correct one (based on the correct "Surname" and "Name").
4.) That would then copy the corresponding "Client number" of that record, onto the system clipboard – for me to then enter into our CMS.

So, it parses, displays, and then copies the content of the 5th column, in the specified row, based off my selection.

Is this something that could be easily replicated inside KM?
I hope KM can come to the rescue – this saves me hours per month!

#2

Keyboard maestro would work most easily with a CSV file if that’s a possible modification to your situation.

Python3 has available packages for reading Excel spreadsheets if you wanted to keep it within Alfred.

1 Like
#3

Hey @Cassady,

I see you tried to install Python and failed – a good thing to mention in your original post.

Do you have Xcode – or at least the Xcode command line tools installed on your system?

If not that might explain the failure.

-Chris

#4

It sounds like a very nice workflow and I hope you get it working again.

I expect it can be replicated in some form. But not easily. I would guess you would need to make a copy of the Excel file that Keyboard Maestro could read and then do some kind of search of that data to get to the Client Number you want.

But in the meantime, maybe you could look into using Keyboard Maestro to semi-automate what you would have to do if you were working with the Excel file directly. This would be my approach.

In other words, without any automation, in the Excel sheet a search for the Surname would find all Cells with that Surname in. Then clicking through the search results you would find the Surname and First Name you are looking for, then copying the contents of the Client Number Column would get you the number to the Clipboard.

Keyboard Maestro can help with the process as it can invoke menu commands, ask for input, type text in search fields etc. I have used similar approaches with working with online Google Sheets which are very similar to Excel.

EDIT - The other good thing about this approach is that Macros built this way (a chain of Actions that automate an App's existing features/menus/keys) is that since you build them yourself you can understand exactly how they work and maintain them if they break for some reason.

#5

Appreciate the help, and taking the time to check my post at the Alfred forums.

I have Xcode (installed when I was dipping my toes into learning how to code for iOS14 — a delayed project now! :grimacing:). Haven’t updated it in yonks (12GB size kept putting me off).

Will try updating and running the Alfred suggested script again. To be clear then — this was my trying to reinstall the deprecated(?) Python 2.7(?), to keep the older Alfred workflows working.

#6

Thank you. This is pretty much how I would have thought to do it, since my coding/knowledge of KM is still in its infancy. I just wanted to check that I wasn’t missing anything.

I read somewhere (possibly misunderstood) that one possible scenario could have seen me dumping the data into an KM “dictionary”, and have KM read/action from there — i.e. all internally? But as indicated, not sure if that would be viable in this instance.

My data changes once annually, in the sense that each year, another few hundred lines/record entries get added. Other than that — it remains constant.

#7

If it would be okay for you to save the records as tab-del text instead of in an Excel table, you could get some inspiration here:

You could also copy the Excel table to clipboard first.

1 Like
#8

Keyboard Maestro can certainly launch something, but perhaps the selection process should really take place within Excel, defined in terms of VBA or (even better, if your Excel version includes it, the LAMBDA function).

Not sure which jurisdiction you are working in, but from where I write protection of client data is a serious business, and it sounds suboptimal (as well as slow) to "dump" it all anywhere, including into a (potentially persistent) Keyboard Maestro variable.

Excel has plenty of computational capacity and UI equipment of its own – better to define a search within Excel, and use Keyboard Maestro just to assign a trigger.

#9

Food for thought here, thanks. Never would have occurred to me to really look at what Excel can do on its own, and then simply trigger from KM. Will have a snoop around.

#10

I'm curious, though, as to why the Python 2 script couldn't be tweaked to run with Python 3. (Python 3 can be installed with Homebrew, for example.)

#11

I'm sure it can be, but what I know about Python can be written on the back of half a postage stamp!

So getting this to work properly with Python 3 would (from my perspective) be entirely in the hands of the workflow author over at Alfred, to update things. And I'm not even going to ask that, since they already created the first version as a labour of love for the community, shared freely.

Therefore, short of my learning Python (from scratch), thought I would try and see what I can do in KM, since I am at least marginally more familiar with it.

1 Like
#12

As a fellow "I Sheet You Not" user, I also appreciated the workflow and how opened the gateway to a fuzzy, convenient search of Excel files. I still think Alfred is a better fit for this task and, if your workflow is flexible enough to work with CSV files, you might try dfay's Python based Fuzzy, self-updating list filter workflow template. He has updated the Alfred workflow to work with Python 3. I often use this Alfred workflow to grab a unique identifier based on a Surname search and then I pass that identifier to Keyboard Maestro for it to work further magic.

1 Like
#13

This is going to sound like an offer - but I'm not sure it is :slight_smile: - but how long is this Python 2 script?

#14

Assuming the Excel file is exported to CSV, and assuming the records are in the format "Surname,Name,Initials,Title,Client number" the following script should do what you described

image
image1032×3696 205 KB

This tested successfully with a CSV file containing the following lines:

Surname,Name,Initials,Title,Client number
Duck,Donald,DD,Quacker, 00001
Vader,Darth,DV,Bad Guy,666
Duck,Daffy,DD,Quacker,00002

Select the CSV file before running the macro. The script can also be modified to select the file at run time.
CSV Filter.kmmacros (7.1 KB)

1 Like
#15

Goodness!! Sincere thanks for all the replies here.

Been lost for a few weeks, getting my new MBP up and running. Going to try and work through all of these tomorrow, and see if I can get something working again.

#16

Because I'm me :wink: -- here's an AppleScript. I haven't looked at KM's dialogs yet, so it's written to be run in Script Editor -- it's easy enough make it a KM action and input the search term as a KM variable instead, and you could also return the "Client Number" to KM for further fun rather than putting it on the clipboard.

It works on the first worksheet of the frontmost Excel window, though you could easily change it to reference a particular workbook/sheet so the target needn't be at the front. It's based on the column setup in the OP, and assumes the Client Number is an integer (Excel returns a floating point value). If your Client Numbers include text, eg "CN12345" you should remove the "as integer" coercion.

I've assumed a case-sensitive search so that eg "Smith" would match all the Smiths but not Naysmith -- remove "with match case" if you want it to be case-insensitive -- and it follows Excels matching rules in that "Smith" would match "Smithson". You can also use Excel's wildcards, so "Sm?th" would match Smith and Smyth.

set searchTerm to text returned of (display dialog "Enter name to search for:" default answer "")

tell application "Microsoft Excel"
	tell window 1
		tell sheet 1
			set theList to {}
			try
				set foundRange to find column 1 what searchTerm look in values with match case
			on error
				display dialog "No matches found"
				return 0
			end try
			set firstFound to foundRange
			repeat while true
				set rowNumber to first row index of foundRange
				copy ("" & value of cell ("$A$" & rowNumber) & ", " & value of cell ("$B$" & rowNumber) & " - " & (value of cell ("$E$" & rowNumber) as integer)) to end of theList
				set foundRange to find next column 1 after foundRange
				if foundRange = firstFound then exit repeat
			end repeat
		end tell
	end tell
end tell

set the clipboard to (last word of item 1 of (choose from list theList))