On the previous page we examined how to use AppleScript to export data and to error trap our scripts. On this page, we look at how to import data from Apple's Mail program, parse it, and bring it into QuickBooks 2009 Mac.
In this example, we are going to assume that you have an email message in your Inbox from Authorize.net. This same script can be used to import just about any Mail message into any program. Notice that this script does not require Mail be the frontmost application so we don't need to send Mail an activate message.
-- This script retrieves the contents of an email message from Authorize.net in Apple Mail 3.6
-- We will assume that the user has either highlighted the message they want to process in their Inbox or has the message open. You could get fancier and use a loop to import multiple messages.
tell application "Mail"
set theSelectedMessages to selection
if the number of items in theSelectedMessages is 0 then
display dialog "Select or open an Authorize.net receipt." buttons {"Cancel"} with icon 0 with title "Oopsie" default button "Cancel"
return
end if
if the number of items in theSelectedMessages > 1 then
display dialog "Whoa, dude, only 1 message at a time, please." buttons {"Cancel"} with icon 0 with title "Oopsie" default button "Cancel"
return
end if
set messageSelected to item 1 of theSelectedMessages
set emailContent to the content of messageSelected
set messageSubject to the subject of messageSelected
-- Mark the message as read.
set read status of messageSelected to true
end tell
And that's it - boom. We have all our data about that message contained in AppleScript variables. At this point, it might be a good idea to do an error check. We could look at the subject line, the sender, or both. We'll just use the subject line:
if messageSubject does not contain "Merchant Email Receipt" then
display dialog "Please select a Merchant Email Receipt." with icon note buttons {"OK"} with title "Oopsie"
return
end if
Now that we are sure we have the merchant email receipt stored in emailContent, we are ready to parse the data.
It's helpful at this point to see what the data contained in emailContent looks like:
**Please DO NOT REPLY to this message. E-mail support@authorize.net if you have any questions. ========= SECURITY STATEMENT ========== It is not recommended that you ship product(s) or otherwise grant services relying solely upon this e-mail receipt. ========= GENERAL INFORMATION ========= Merchant : YOUR NAME HERE (123456) Date/Time : 10-Dec-2010 05:43:39 PM ========= ORDER INFORMATION ========= Invoice : 121212 Description : Widget 10-pack Amount : 230.00 (USD) Payment Method : MasterCard Type : Authorization and Capture ============== RESULTS ============== Response : This transaction has been approved. Authorization Code : 99666 Transaction ID : 9123123123 Address Verification : Street Address: Match -- First 5 Digits of Zip: Match ==== CUSTOMER BILLING INFORMATION === Customer ID : First Name : Support Last Name : Team Company : ILPI Address : PO Box 1003 City : Blackwood State/Province : NJ Zip/Postal Code : 08080 Country : United States Phone : 856-555-1212 Fax : 732-555-1212 E-Mail : ThisWillBounce@ilpi.com ==== CUSTOMER SHIPPING INFORMATION === First Name : Support Last Name : Team Company : ILPI Address : PO Box 1003 City : Blackwood State/Province : NJ Zip/Postal Code : 08080 Country : United States ======= ADDITIONAL INFORMATION ====== Tax : Duty : Freight : Tax Exempt : PO Number : ========== MERCHANT DEFINED ========= CardType : MasterCard CustomerSource : Web site CouponCode : BigDEALZ PhaseOfMoon : Waxing gibbous
Certain data will always appear on certain line numbers, so that makes things easy for the first few items. However, even simple lines like Last Name might contain more than one word. In the examples below we'll look at a few of the different methods you might use to parse data that appears in constant positions.
-- This script parses the data that is currently residing in the emailContent variable we used in the previous script example
-- These two lines are key. We are converting the paragraphs of our data into a list of items because it is much easier to manipulate list items than it is to manipulate paragraphs. However, we will need a copy of the original paragraphs for later, so we'll copy that into a variable first:
set originalContent to emailContent
set emailContent to the the paragraphs of emailContent as list
-- In this example, we know that the Transaction date will always appear on the 9th line and always have the same format.
set transactionDate to item 9 of emailContent
-- transactionDate now has the value "Date/Time : 10-Dec-2010 05:43:39 PM". Now we need to chop that down. Because Authorize.net zero-pads dates, we can reliably do it with the same character range each time.
set transactionDate to characters 13 through 23 of transactionDate as text
-- transactionDate now has the value "10-Dec-2010" which QB will accept as a valid input in its date fields.
-- Here, we can grab the Transaction number. This will always be in the same spot so we can simply say
set transactionNumber to word 3 of item 21 of emailContent
-- Now let's look at grabbing the Last Name. If it was two words, we could not rely on asking for the last word of item/paragraph 27. Instead, we must look all the way to the end of the line:
set lastName to item 27 of emailContent
set lastName to characters 13 thru (length of lastName) of lastName as text
Once we get to the billing address, we can no longer rely on data being in a given paragraph/item number (because any address line could take up more than one line). From this point on in the parsing, we need to dynamically find the beginning and end locations of our data chunks. As we will do this over and over, we will create a subroutine list_position:
set startHere to list_position("Address : ", emailContent)
set endHere to list_position("City : ", emailContent)
set billingStreet to item startHere of emailContent
set billingStreet to characters 11 thru (length of billingStreet) of billingStreet as text
-- We only need to do this extra bit of work for multi-line data such as Addresses
repeat with x from (startHere + 1) to (endHere - 1)
set billingStreet to billingStreet & return & return & item x of emailContent
end repeat
Here's the list_position subroutine we'll need for that to work. Like all subroutines, it goes after the main script so we can call it over and over:
-- This subroutine returns the position of an item in a list
-- From http://www.apple.com/applescript/sbrt/sbrt-07.html, changing "is" to "contains"
on list_position( this_item, this_list)
repeat with i from 1 to the count of this_list
if item i of this_list contains this_item then return i
end repeat
return 0
end list_position
Billing City, which will always be one line, would be found this way:
set startHere to list_position("City : ", emailContent)
set endHere to list_position("State/Province : ", emailContent)
set billingCity to item startHere of emailContent
set billingCity to characters 8 thru (length of billingCity) of billingCity as text
One final fly in the ointment is that the Authorize.net email does not use unique markers for the billing and shipping information. When we look for "Address : ", we will always find that for Billing, and not for Shipping. This is why we made a copy of our original data - we can isolate the Shipping data and then do our extraction on that. This script will use something called AppleScript text item delimiters to make that easy:
set AppleScript's text item delimiters to "==== CUSTOMER SHIPPING INFORMATION ==="
set shippingInfo to text item 2 of originalContent
set AppleScript's text item delimiters to ""
set shippingInfo to the the paragraphs of shippingInfo as list
Notice that we immediately set the text item delimiters to "" as soon as we were done with them. That's because they will stay at the value we set even if a script has ended (naturally or because of error). You'll find it good script hygiene to reset the AppleScript text item delimiters to "" at the beginnning of every script.
Now that we have the shipping information in its own variable list, you can extract those pieces of data in the same way we did above, substituting shippingInfo for emailContent.
In the example we've been using, we have imported and parsed data from an Authorize.net payment. Let's now put this parsed data into QuickBooks. Specifically, we will Receive Payments in QuickBooks.
You might store the customer information by company name or perhaps using a "Last Name, First Name" construction. We'll assume that you are using the latter in this case. We have already seen how to activate menus and navigate in QB so this script should be easy.
tell application "QuickBooks 2009"
activate
tell application "System Events"
-- Open the Receive Payments window by simulating a menu selection.
click menu item "Receive Payments" of menuof menu bar item "Customers" of menu bar 1 of process "QuickBooks 2009"
end tell
set theCoordinates to the bounds of window "Receive Payments"
tell application "System Events"
-- Click the Deposit To button in the Receive Payments pane.
click radio button "Deposit To" of window "Receive Payments" of process "QuickBooks 2009"
-- And activate the Deposit To pulldown menu
move mouse {(item 1 of theCoordinates) + 300, (item 2 of theCoordinates) + 207}
click mouse
delay 0.1
keystroke "Merchant Account" -- Use the name of whatever account you deposit to
delay 0.5
keystroke tab
delay 0.1
keystroke lastName & "," & space & firstName -- or companyName, depending on how you enter your data.
keystroke tab
delay 0.1
keystroke transactionDate
delay 0.1
keystroke tab
keystroke paymentAmount as text -- We left parsing of this up to you
delay 0.1
keystroke tab
keystroke paymentMethod -- We left parsing of this up to you
keystroke tab
delay 0.1
keystroke tab
keystroke transactionNumber
delay 0.1
keystroke tab
end tell
end tell
All your data is now imported into the Receive Payments window. You will probably want to review it rather than letting the script hit the OK button until you are certain that the script works as you expect. This example includes various delays in it, but all of those may not be necessary, so you can delete them and then test to see if things still work correctly. Also remember that for the mouse commands to work that you need to install the XTools OSAX we dicussed on page one of this tutorial. You might also need to tweak the click location through trial and error.
On the next page we will examine how to export documents and data from QuickBooks 2009 to Apple Mail using AppleScript.
Copyright 2008-2024 by Interactive Learning Paradigms Incorporated. All rights reserved. Reposting of this material is not permitted and constitutes a violation of US and International copyright law.