Scanning a list of files and adding part of a name to an Excel file or Google sheet

Hey @pmatiakis,

Thanks to @CJK's noticing my mistake I was able to revisit this and do everything in the shell, so the data acquisition and manipulation is very fast.

While I'd prefer to use AppleScript to enter the data in the spreadsheet, I don't have time at the moment to figure out the syntax to locate the correct entry point, but I'll look into this later if time and interest allow.

@pmatiakis – see the orange actions?

Those are the ones you need to alter to correspond correctly to your system.

Drag the correct files into the to: field for each action.

NOTE – it is important that the first actions does NOT end with a backslash (“/”). Keyboard Maestro should NOT put one there, so this should be a non-issue if you drag-and-drop the files.

The macro:

  1. Acquires the data and transforms it to the correct format for entry into your spreadsheet.
  2. Opens Microsoft Excel.
  3. Opens the correct spreadsheet.
  4. Finds the correct entry point.
  5. Pastes the data.

The spreadsheet is NOT saved, so any mistake is easy to recover from.

The data is LEFT on the Clipboard, so it is readily available if corrections to spreadsheet entry are required.

-Chris


Extract Meal Data for Excel Spreadsheet v1.01.kmmacros (13 KB)

Hey Guys,

Yet another reason why I’ve been a devotee of the Satimage.osax AppleScript Extension since 2003.

It made this task almost too simple.  :sunglasses:

-Chris

----------------------------------------------------------------
# Auth: Christopher Stone
# dCre: 2018/03/31 22:27
# dMod: 2018/03/31 23:07
# Appl: AppleScript, Satimage.osax
# Task: Acquire and massage data for entry into an Excel spreadsheet.
# Libs: None
# Osax: Satimage.osax
# Tags: @Applescript, @Script, @Satimage.osax, @Acquire, @Massage, @Data, @Entry, @Excel, @Spreadsheet
----------------------------------------------------------------

set mealsFolder to ((path to home folder as text) & "test_directory:INSYS:Meals:")

set dateToday to current date
set todayFolder to alias (mealsFolder & (strftime dateToday into "%Y:%m:"))
set fileNameList to list files todayFolder starting with (strftime dateToday into "%Y-%m-%d") with names only

repeat with fileName in fileNameList
   set contents of fileName to strftime (strptime "2018-03-31" encoding "%Y-%m-%d") into "%U" & tab & (contents of fileName)
end repeat

set fileNameList to join fileNameList using linefeed
set fileNameList to change " - " into "\\t" in fileNameList with regexp without case sensitive
set fileNameList to change " (\\d+,)" into "\\t\\1" in fileNameList with regexp without case sensitive
set fileNameList to change "\\.pdf$" into "" in fileNameList with regexp without case sensitive

----------------------------------------------------------------

Thanks again!

@ccstone I downloaded and changed your macro like you said.

However when I run it the following error comes up.

04

Just for confirmation I set the path to

40

Any ideas?

Hey @pmatiakis,

You’re misusing the tilde-based path form.

~/Users/panosmacbookpro/Dropbox/Documents/INSYS/Receipts/Mercadona/Meals

Should be:

/Users/panosmacbookpro/Dropbox/Documents/INSYS/Receipts/Mercadona/Meals

Or:

~/Dropbox/Documents/INSYS/Receipts/Mercadona/Meals

~/ is an abbreviation for your Home folder and makes using paths more portable between different computers, boot drives, etc.

-Chris

I’ve updated my macro in post #12 to v1.01 – 2018/04/01 02:03 CST

-ccs

:man_facepalming:sorry didn’t know.

Ok now it’s passed that point.
Now the worksheet comes up but when KM hits “Paste” the cursor goes to the end of the worksheet (line 1048576) but nothing is pasted.

Hey @pmatiakis,

Now you do.  :sunglasses:

The problem sounds like a timing issue, and those can take some tweaking.

Try increasing the pause from 2 seconds to 10 or more.

Also try running the macro with Excel already running.

What version of Excel are you using?

-Chris

Hey Chris,

You were right.
I increased the pause to 10 seconds and it works.
Of course now that we’re in a new month and there are no entries in the excel file, the last (CMD down arrow) step, causes it to jump to the end of the sheet but I added one extra dummy line to get around that.

I can’t thank all of you enough!!!

Panos

Hey @pmatiakis,

Hmm…

You could run this AppleScript and save the result to a variable:

tell application "Microsoft Excel"
   return active cell's value
end tell

If the result is “Week” then you can change the keystrokes as desired.

-Chris

Thanks Chris that’s a great tip.
Working with this for a while showed me a flaw in my design.

I’ve scheduled the macro to run every day.
Some times though, I’m adding receipts in the evening, when my mac is not running.
The next day however when the macro runs, it gets the receipts from the current day, but misses the ones entered the night before.

If I change the script to look at the day before then I would miss Friday’s receipts, as my Mac could not be running on Saturday.

The ideal would be for the script to get any receipts that are not already in Excel, but I guess that would require too much work.

Can I ask you to please change the script so it gets all the receipts of the current month?
Then I can run it on the last day of the month and get everything.

Thanks again

Panos

Maybe you need to consider changing your workflow logic.

How about processing all receipts that have not been processed?
Do you really need month folders? A Spotlight search can easily limit to a given date range. For example, see MACRO: @SL Mac Spotlight Std Search.

1 Like

Thanks Michael for your suggestion.
This seems like a really useful Macro (adopted :wink:).

However what you’re suggesting is to manually search for and enter all information to the Excel file right?
Maybe I misunderstood.

Hey Panos,

Okay. I've done as you asked.

(I'm assuming you meant the macro and not the AppleScript.)

I've also changed the path and file window actions to automatically get the current year and month.

~/Downloads/%ICUDateTime%Y-MM% - Meals.xlsx

You need to change the ~/Downloads/ portion of that to be correct on your system.

And of course you need to change to root meals folder path to be correct as well.

~/test_directory/INSYS/Meals

-Chris

Extract Meal Data for Excel Spreadsheet v1.02 @working.kmmacros (12 KB)

Hey Panos,

That’s not a bad idea.

I’ve written this task in AppleScript again, but I’ve used only vanilla AppleScript and the Keyboard Maestro Engine’s AppleScript functions.

You need to adjust these two lines:

set mealsRootPath to "~/test_directory/INSYS/Meals/"
set excelFile to "~/Downloads/yyyy-mm - Meals.xlsx"

Do not touch this part of the Excel file path:

/yyyy-mm - Meals.xlsx

Run the AppleScript from the Script Editor.app to see how it works.

Run it from an Execute an AppleScript action in Keyboard Maestro.

The script gets the same data the others do, but it omits the week. Use Excel’s =WEEKNUM(cellRef) function to populate your WEEK column.

The script finds the correct insertion point in Excel and pastes automatically, and that should function more organically than brute-forcing the UI as we did with Keyboard Maestro. This should also prevent the timing issues you were having with the Keyboard Maestro macro.

When you run the script the currently existing files of the current month will be processed and marked with a green label. Subsequent runs of the script will ignore already labeled files.

I’m using Excel from Office 2011, so I can’t guarantee it’ll work with Office 2016 – but chances are that it will.

-Chris

----------------------------------------------------------------
# Auth: Christopher Stone
# dCre: 2018/04/04 05:19
# dMod: 2018/04/04 05:46
# Appl: Finder, Keyboard Maestro Engine, Microsoft Excel, System Events
# Task: Extract Meal Data from file names in Finder and paste them into an Excel spreadsheet.
# Libs: None
# Osax: None
# Tags: @Applescript, @Script, @Finder, @Keyboard_Maestro_Engine, @Microsoft_Excel, @System_Events, @Extract, @Meal, @Data, @File, @Names, @Paste, @Excel, @Spreadsheet
# Vers: 1.00
----------------------------------------------------------------

set mealsRootPath to "~/test_directory/INSYS/Meals/"
set excelFile to "~/Downloads/yyyy-mm - Meals.xlsx"

set yearMonth to text 1 thru 7 of ((current date) as «class isot» as string)
set excelFile to kmReplace("yyyy-mm", yearMonth, excelFile, false, false, false)
set yearMonth to kmReplace("-", "/", yearMonth, false, false, false)
set mealsMonthPath to mealsRootPath & yearMonth

tell application "System Events"
   set excelFile to (path of file excelFile) as alias
   set excelFileName to name of excelFile
   set fileList to path of files of folder mealsMonthPath whose visible is true
end tell

tell application "Finder"
   repeat with theFile in fileList
      set fileRef to theFile as alias
      if fileRef's label index = 6 then
         set contents of theFile to 0
      else
         set fileRef's label index to 6
         set contents of theFile to name of fileRef
      end if
   end repeat
   
end tell

set fileList to text of fileList
set AppleScript's text item delimiters to linefeed
set fileList to fileList as text
set fileList to kmReplace(" - | (?=\\d)", tab, fileList, true, false, false)
set fileList to kmReplace("(?m)\\.pdf$", "", fileList, true, false, false)

set the clipboard to fileList

if fileList ≠ "" then
   tell application "Microsoft Excel"
      if not running then
         run
         delay 5
      end if
      activate
      if document excelFileName exists then
         activate object window excelFileName
      else
         open excelFile
      end if
      
      tell active sheet of document excelFileName
         set lastfilledcell to get end (range "B65536") direction toward the top
         set firstBlankCell to get offset lastfilledcell row offset 1
         select firstBlankCell
         paste
      end tell
      
   end tell
end if

----------------------------------------------------------------
--» HANDLERS
----------------------------------------------------------------
on kmReplace(findPattern, replacePattern, dataStr, regExBool, caseBool, tokensBool)
   tell application "Keyboard Maestro Engine"
      set foundDataList to search dataStr for findPattern replace replacePattern ¬
         regex regExBool case sensitive caseBool process tokens tokensBool
   end tell
end kmReplace
----------------------------------------------------------------

:astonished: consider my mind blown…

I already implemented your first change and it works great!
I’ll try the Apple Script solution and let you know how it works.

Thanks once again!

Hey Chris,

I tried it and at one point i got an error which I think is related to the fact that the name of the excel file as it appears in Excel 2017 is without the extension.

For some reason I don't get the error any more but nothing is pasted in Excel.
Everything is there on the clipboard, but if I try to type "CMD + V" everything is pasted only in the first column.

Thanks,

Panos

Somehow now I don't get the error any more and since I removed the green tag from the finder and tested it again, everything is pasted in the correct excel file but only in one column.

Hey Panos,

That may be a difference in Office 2016 OR in our settings…

On my system Excel knows the data is tab-delimited and acts accordingly.

I’ll see about fixing it later – right now I’m seriously knackered and need a nap.

-Chris

No problem.

I investigated a little more, and discovered that if I paste special, the below three options appear.

37

Only the first (default) option pastes everything in one column.
The other two options have the desired result.

Unfortunately I cannot find an option in preferences to change the default.

Hey Panos,

Try this script. I’ve used the paste special command instead of paste, and I hope that will change the behavior on your system.

-Chris

----------------------------------------------------------------
# Auth: Christopher Stone <scriptmeister@thestoneforge.com>
# dCre: 2018/04/04 05:19
# dMod: 2018/04/06 03:38
# Appl: Finder, Keyboard Maestro Engine, Microsoft Excel, System Events
# Task: Extract Meal Data from file names in Finder and paste them into an Excel spreadsheet.
# Libs: None
# Osax: None
# Tags: @Applescript, @Script, @Finder, @Keyboard_Maestro_Engine, @Microsoft_Excel, @System_Events, @Extract, @Meal, @Data, @File, @Names, @Paste, @Excel, @Spreadsheet
# Vers: 1.01
----------------------------------------------------------------

set mealsRootPath to "~/test_directory/INSYS/Meals/"
set excelFile to "~/Documents/Clients/Panos Matiakis ⇢ KM Forum/2018-04 - Meals.xlsx"

set yearMonth to text 1 thru 7 of ((current date) as «class isot» as string)
set excelFile to kmReplace("yyyy-mm", yearMonth, excelFile, false, false, false)
set yearMonth to kmReplace("-", "/", yearMonth, false, false, false)
set mealsMonthPath to mealsRootPath & yearMonth

tell application "System Events"
   set excelFile to (path of file excelFile) as alias
   set excelFileName to name of excelFile
   set fileList to path of files of folder mealsMonthPath whose visible is true
end tell

tell application "Finder"
   repeat with theFile in fileList
      set fileRef to theFile as alias
      if fileRef's label index = 6 then
         set contents of theFile to 0
      else
         set fileRef's label index to 6
         set contents of theFile to name of fileRef
      end if
   end repeat
   
end tell

set fileList to text of fileList
set AppleScript's text item delimiters to linefeed
set fileList to fileList as text
set fileList to kmReplace(" - | (?=\\d)", tab, fileList, true, false, false)
set fileList to kmReplace("(?m)\\.pdf$", "", fileList, true, false, false)

set the clipboard to fileList

if fileList ≠ "" then
   tell application "Microsoft Excel"
      if not running then
         run
         delay 5
      end if
      activate
      if document excelFileName exists then
         activate object window excelFileName
      else
         open excelFile
      end if
      
      tell active sheet of document excelFileName
         set lastfilledcell to get end (range "B65536") direction toward the top
         set firstBlankCell to get offset lastfilledcell row offset 1
         select firstBlankCell
      end tell
      
      tell active cell
         set theCell to it
         set cellRange to item 1 of (get areas)
      end tell
      
      paste special (theCell) what paste values
      
   end tell
end if

----------------------------------------------------------------
--» HANDLERS
----------------------------------------------------------------
on kmReplace(findPattern, replacePattern, dataStr, regExBool, caseBool, tokensBool)
   tell application "Keyboard Maestro Engine"
      set foundDataList to search dataStr for findPattern replace replacePattern ¬
         regex regExBool case sensitive caseBool process tokens tokensBool
   end tell
end kmReplace
----------------------------------------------------------------