The Power of Unix
Posted December 8th, 2003 @ 09:03pm by Erik J. Barzeski
Ahhh, behold, the power of Unix. I hadn't "processed" orders for Freshly Squeezed Software for over 190 days, leaving me with some thousands of emails to process. Every day we get an order summary (tab delimited text file listing all orders for the day prior). For each order we get two emails, one mirroring the email sent to the customer and another for us. All told: thousands of emails.
The daily tab-delimited text files are stored in an Excel document (with a different tab for each product). The order emails (two per order) are stored in a FileMaker Pro database (one database per product). With thousands of emails in a single folder, how was I to do this easily? Would you believe me if I told you that I spent about ten minutes in total getting the data out of Entourage and into Excel and FileMaker Pro? Here's how…
First, I used the "Find in Message Body" feature of Entourage to look for unique strings like "SKU-M." Those five digits uniquely identify MailDrop orders. Repeat as necessary for SKU-B, SKU-F, SKU-W. Once all MailDrop orders (two emails per order) are in the results window, run the AppleScript that scuttles all the emails to the FileMaker Pro database. Repeat for the other products, then delete all messages with "Order Notice" or " License" in the subject.
Phase one done.
The daily summaries are attachments and are the only emails remaining. Select all, choose "save all attachments," and save them all or "~/Desktop/Orders." In the Terminal:
cd ~/Desktop/Orders/ cat * | grep 'SKU-M' | pbcopy
Switch to Excel, select the next available cell, and paste. Select all, sort by the proper column (I use header rows too), and repeat as necessary for the other three products.
Heck, the whole process was so easy I can't see myself doing this for another 180 days. To do this one by one as orders come in is soooooo much more a waste of time. Ten minutes! And about four of those minutes were spent wondering why that last GREP produced the same results for every search string (the BBEdit file had Mac line endings, not Unix, for some reason).
Ahhhh, behold the power of Unix. 😀
Donate Life
Posted 08 Dec 2003 at 9:23pm #
Useless Use of Cat Award.
grep 'SKU-M' Orders-All.txt | pbcopy
😀
(note: this is meant to be a humorous post and not a condescending-gray-bearded-unix-guy post) 🙂
Posted 08 Dec 2003 at 10:06pm #
I usually forget the order of grep's arguments (filename first? second?) and thus cat is easier to remember and faster (no looking in man pages).
Posted 09 Dec 2003 at 12:26am #
Geez, you are using Applescript, so why not make it really easy on yourself?? Automate it all so that an Entourage rule (Entourage does allow Applescript in its rules does it not?) does the text search as emails come in. Once it knows it's an order email, what's keeping you from running it all automatically in an AppleScript fired off from that rule? So no need to even do this every half year.
Posted 09 Dec 2003 at 12:54am #
Because, that'd take me 30 minutes to set up properly, plus maintenance. If I do this twice a year, I'm only looking at 20 minutes.