Download CSV file, move, import to Excel, rename, save

I thought I would post this before I proceed to get suggestions and feedback on how to proceed. Hopefully it will help someone else.

I use a program called Billing to track hours for freelance projects.

When I am ready I can send a report to myself of all the entries on my phone.

It sends a report in two attachments called Report.pdf and Spreadsheet.csv

I download the files and open the .csv file and save it in Excel to a folder where I keep the time sheets. Then I rename it with the current date formatted as Year_Month_Day Timesheet”, save and start manually processing it which involves cleaning up the entries and editing the time. In the entries I reformat the ‘date start’ and ‘date end’ separating the date from the hour to make it easier for me to read as I copy/paste to Word. I would like what I paste to already be the desired font style.

Here’s a playlist of what I would like to do initially…

Mail.app rule hits on
From - is “me@example.com”
Subject - contains “Report”
Message - content contains: “[Report Attached]”

Then…(macro or applescript?)

File gets downloaded (there’s two formats I want only the .csv file)
move the .csv to an existing folder
rename with today’s date (“Year-Month-Day”“Timesheet”
open Excel and import the file
Save the file in Excel format. (.xls or .xlsx)

I hope to do more with the file but this is a good start for a learning project.

Hey John,

I'd probably have to see the .csv (before) and the Excel file (after) to have a feel for how easy this would be to automate.

I'm not sure that's possible, but it should be possible to set it via AppleScript.

Again - I'd need to see the before and after.

-Chris

I could probably post it here if the forum will let me upload a .csv.

I would just be happy if I could get something that recognizes it (or any file) that send to myself for download and date it and put it in a specific or chosen folder.

e.g. When a file is from me to me and has an attachment put it in “SomeFolderName” with an appended date.

(I have a feeling this is similar to your handler for the downloads folder in that Download Bank Statements Script you were helping with, just a slightly different routine branch at the beginning.)

Hey John,

The forum will accept zip files.

I'm a little unclear about how your download works.

Are you manually downloading from the web, or are you getting an email with attachments?

-Chris

Hi Chris,

I am sending from an IOS app called "Billing Hours". I send a "Report" from the app to myself. (From me, to me). Only because it doesn't have any other way to get data out.

The report sends two files, a .PDF and a .CSV file.

Here's the .CSV file...

report example.zip (874 Bytes)

Once I get it into Excel I can probably setup macros to do what ever formatting is needed but the challenge for me is to get the report from mail automatically imported when it comes in into a new Excel doc by using an AppleScript that fires from a mail rule. Then once it's in Excel I will separate the start date/time into two columns... DateStart and TimeStart, then I will separate the end date/time into two columns... DateEnd and TimeEnd.

This is so i can sort by date then time and have a consecutive list of dates followed by times.

Next I copy the dates and a total time for each day to a Word Doc which is the invoice, then I also copy the Notes to the Word Doc into the Description heading. When I do that the font changes. I have got a keyboard shortcut to change it but it would be nice to have it assume the font in the Word Doc which is Helvetica. But like I said before, once it's in Excel I can probably run a macro to format the text styles. Does this make sense?

Hey John,

What version of OSX are you using?

Mail rules have been broken to some degree in 10.10, and I’ve only just upgraded — so I don’t know to what extent — yet.

-Chris

Hi Chris,

I am using 10.9.5. I am having a difficult time determining what’s going to stop working if I upgrade to 10.10. I have one app that I use every day and there have been reports that it doesn’t work, then I see a post from someone that says there’s no problems with that app at all. I like to research before I upgrade but it seems that it’s getting harder and harder to do that. I think I am going to buy a drive to clone my system to and update that and just test on my own. Seems like the accuracy of OSX public forums is like 1% fact, 99% ignorance.

I am not really worried about Mail.app. Since it’s a core app I am sure Apple will update it soon so the rules work. Unfortunately I can’t say the same about Apple’s note module in Keychain App. Tabbing stopped working in 10.7 and I have reported it but it’s still not fixed. So I gave up and wrote a macro for tabbing in KM that works in the note module.

-John