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?
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.
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?
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.
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.
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:
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.
Download the files from the web for this use case into the “Acme Parts Update” folder manually, as you are doing now.
Run a KM macro that will:
Make Excel open a file, looking in the “Acme Parts Update” folder.
You select the file, whether it’s a CSV or TXT file (that’s the end of the KM macro)
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.