Select or Show a Menu Item

First things first. I am truly a KM beginner. So laugh all you want.

Everyday I download data that comes from the website as a TextEdit file. I want to import the data into Excel. Below is my macro "Import well data" and below that is the Alert I receive after I "Try" the macro. My next question will be how do I get the macro to "click" the Text file button in the Import dialog box?

Also, is there any way to read the entire Alert when it pops up?

Thanks.


Think sideways! KM Rocks! :sunglasses:

Have you tried to just OPEN (rather than “Import”) the .csv file in Excel?
It should open/convert without any prompts.
You can also just double-click on the .csv file and Excel will open it.

Hey Neil,

When asking for help it's always a good idea to tell us what OSX version you have and what version of the software in question you have (Excel in this this case).

A lot of users ask us things like “How do I import data into a spreadsheet?”, and they fail to tell us what spreadsheet – and that they're using an old version of Keyboard Maestro on Snow Leopard.

You did much better than that – good on you for posting images, so we can see for certain what you're up to.

Carey has given you the basic better way of doing this, but I'm going to show you the ugly hard way to demonstrate the methods you can try when there's not an easier way.

-Chris


Excel → Import CSV Data.kmmacros (8.2 KB)

Hey Neil,

Okay. Now I'm going to show you the most efficient method (if the URL of the downloaded file is consistent and discoverable).

My test file is zipped, so the shell script has an unzip command in it. If your file is just a bare CSV file then the job is that much easier.

You can do a lot with 5 lines of code.

A) Download a file.
B) Unzip the file.
C) Open it with Microsoft Excel.

The test file was created on a Mac and has OSX resources associated with it, so unzip leaves a folder named __MACOSX in the ~/Downloads folder. That folder has an invisible file in it. The folder needs to be deleted, but I'm not going to do that IN the script at this time.

-Chris


Download CSV Data and Open File in Excel Using Bash.kmmacros (2.2 KB)

Note that my version does require you associate CSV files with Excel. I forgot about that.

Hi and good day,

What is the difference here btw. enabled and exists?

Pause Until Conditions Met
(until a button with this name NN is enabled).

(until a button with this name NN exists.)

/
with best regards,
Omar K N
Stockholm, Sweden

Hey Carey,

No, your version is using the correct action to open the file with Excel – so no other association is needed.

The one thing I’d change is to make the path the path the file $HOME-based:

~/Downloads/Sacramentorealestatetransactions.csv

It’s more portable that way.

-Chris

Hey Omar,

Exists means just that – Keyboard Maestro can see it.

Enabled means that it can be clicked with the mouse.

A button may exist but NOT be clickable (enabled).

-Chris

Okay – I’ve changed the Bash script a bit, so the __MACOSX folder is no longer an issue.

I’m going to leave the old macro intact, so there’s an example of unzip on the forum, but here’s a superior example using ditto to extract the file from the archive.

#! /usr/bin/env bash
# Christopher Stone, Author
# 2015/12/20 15:51,  Modified

cd ~/Downloads/;
curl --remote-name-all -Ls --user-agent 'Opera/9.70 (Linux ppc64 ; U; en) Presto/2.2.1' \
--url "http://spatialkeydocs.s3.amazonaws.com/FL_insurance_sample.csv.zip";
ditto -x -k --rsrc --extattr --qtn FL_insurance_sample.csv.zip .
open -a 'Microsoft Excel' 'FL_insurance_sample.csv'

* Note that I was able to get rid of the sleep command, so the script is down to 4 lines. (The curl line is wrapped using a continuation character ‘’ for readability, but it’s a single line of code.)

-Chris

To careyB, JMichaelTX, and ccstone,

Thanks very much for all the input. I'm beginning to get it!

As for my details: OS X 10.10.5, Excel for Mac 2011, 14.4.1, all on a 3.1 GHz Intel Core i5, 27 inch

I have attached one of the files that I am importing into Excel. These files have to be exported individually from the web site as their data is specific to a certain item. The files end in .Txt, as you can see. That is the reason I wanted to click the "Text file" button that is on the bottom of the Import dialog box. Also, when I did this manually, the Excel file that was created was beautiful and needed zero adjustments. If a .csv file is the same as the .Txt files I'm downloading . . . my apologies for not grasping the situation. Because I did not see any commas in my downloaded files separating the values I went with the Text file button.

And, to ask again, is there a way to read all of the Alert that pops up in the top right corner of my screen?

Chris,

When I clicked "try" on your inefficient method, I got the same Alert in the upper right corner of my screen. Do you think I have something set wrong in Excel? When I open Excel and pull down File from the menu, the "Import . . ." is solid black and works fine when I click on it.

I have attached the Alert that keeps popping up.

Neil

Neil,

It can get confusing. So, hang in there, and you’ll get it.

Please don’t be offended if I’m telling you stuff you already know, as I don’t know what your know. I’d rather tell you too much than too little. :grinning:

CSV files are just TEXT files (you can open with TextEdit, TextWrangler, etc) with an extension of “.csv”, but they are supposed to have a special format inside, with each field separated by a comma.

The .txt file image you showed above is NOT a CSV file. It looks like a fixed format file, meaning each field in the file has the same number of characters on every line.

Excel handles both of these very well.

Based on my limited understanding of your workflow, here’s what I’d suggest:

  1. Setup/create a specific folder for this use. I’ll call it “Acme Parts Update”. You call it whatever makes sense to you, that you will remember. I rarely let anything download to my “Downloads” folder.
  2. Download the files from the web for this use case into the “Acme Parts Update” folder manually, as you are doing now.
  3. Run a KM macro that will:
  4. Open Excel
  5. Make Excel open a file, looking in the “Acme Parts Update” folder.
  6. You select the file, whether it’s a CSV or TXT file (that’s the end of the KM macro)
  7. If Excel has any prompts, you will manually respond.

I think step (3) is best done in AppleScript, so you can avoid using the UI elements in a KM Action.

I just wanted to share this with you now. I should have some time later and I’ll make the KM macro & AppleScript as an example. But please give it a shot yourself, if you like. You can google “Excel AppleScript” to get some ideas, and even search this KM forum. I think Chris may have written some Excel AppleScripts previously.

Good luck! I’ll try to get back to you soon.

Neil, see my macro, which is mostly AppleScript:

Hey Neil,

Send me one of these files off-list to kmforum@thestoneforge.com

I’m guessing it’s a tab-delimited file rather than a CSV file, but it’s best to work with real data and not have to guess.

No. Notifications have no way to expand.

But – you can open the notification center and look for it in the list – you may be able to see more of the message there.

Hmm… In your original macro I see you don’t have the correct suffix on the menu item.

Correct:

Menu Title:

File

Menu Item:

Import...

Your version leaves off the ellipsis on Import.

Keyboard Maestro will accept either the ellipsis character (’…’ via ;) or three periods to represent this kind of menu item.

See the Menu pop-up on the right hand side of the Select or Show a Menu Item action?

With that you can actually pick the exact menu item from your target application.

I’m surprised that you had a failure with my version, since I have it working here with Excel 2011 and El Capitan 10.11.2. Are you using an English localization on your system or something else?

In any case try using the pick mechanism for the menu action, and see if that will work.

-Chris

1 Like