For the longest time I searched the web to see if anyone had written an applescript to convert .csv files to Quickbooks webconnect (.qbo) files. No one had. My bank, very annoyingly, lets you download qbo files for the past 90 days worth of transactions, but refuses to let you do this by statement (which I repeatedly requested). If you forget to download the activity until after the 90 days have past, you’re screwed. They only let you download .csv or .xls files of past statements (over 90 days old), which are completely useless to Quickbooks users unless you want to pay a subscription to a commercial vendor for one of their programs. I decided to write the script myself. See below. Make sure you test this on a backup of your Quickbooks file. You will need to fill in your own info into the variables at the top as per the comments in the script. Some of the comments are just for me so I didn’t get confused as I was writing it. The comments that you need to act on are self-explanatory. Some of the code is from a previous script that I used as a template, and didn’t want to take the time to clean up.
-Bob
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
--<TRNTYPE> is either "CHECK", "DEBIT", OR "CREDIT"
set cr to ASCII character 13 --in case the paragraphs are separated with carriage returns
set LF to ASCII character 10 --in case the paragraphs are separated with line feeds
property transactionList : ""
property x : 0
property found_number : ""
property trimmed : ""
property truncated : ""
property z : ""
property len : ""
property totaltransactions : 0
property theFileContents : {}
property totalItems : 0
property theParent : ""
property folderName : ""
property baseName : ""
set theText to ""
set len to ""
set theFileContents to {}
property start_date : ""
property end_date : ""
property previousTransactionDate : ""
property previousTransactionNumber : 0
property currentTransactionNumber : 0
property theTextItems : ""
property DTSTART : "19700101120000" --this seems to be the same for all qbo files that I looked at
property DTEND : ""
property parsedDate : ""
property TRNTYPE : ""
property DTPosted : ""
property DTUser : ""
property TRNAMT : ""
property FITID : ""
property FITID_temp : ""
property CHECKNUM : "" --this only needs to be included in the output file if it is a check
property NAME_ : ""
property MEMO : ""
property MemoTemp : ""
property BankID : "" --enter the bank routing number here, in quotes
property AcctID : "" --enter the account number here. Looks like this needs to be 20 digits, so pad the beginning with zeros to get it to 20 long
property org : "" --the bank name. get this from an example qbo file from the bank's site.
property FID : "" --the bank ID number. required. get this from an example qbo file from the bank's site. This is supposed to be a 5 digit number. I guess they don't miss a leading zero, because Quickbooks takes this and mine was only 4 digits long. The current list of bank ID numbers is here: https://quickbooks.intuit.com/learn-support/en-us/your-books-or-my-company/fix-web-connect-import-errors/00/185848
property Balamt : ""
property Dtasof : ""
property acctNo : "" --your account number, in quotes, without any of the leading zeros like in the AcctID
property qbo_header : "OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE
<OFX>
<SIGNONMSGSRSV1>
<SONRS>
<STATUS>
<CODE>0
<SEVERITY>INFO
</STATUS>
<DTSERVER>20191013174339.459[-4:EDT]
<LANGUAGE>ENG
<FI>
<ORG>" & org & "
<FID>" & FID & "
</FI>
<INTU.BID>" & FID & "
<INTU.USERID>123456789
</SONRS>
</SIGNONMSGSRSV1>
<BANKMSGSRSV1>
<STMTTRNRS>
<TRNUID>0
<STATUS>
<CODE>0
<SEVERITY>INFO
</STATUS>
<STMTRS>
<CURDEF>USD
<BANKACCTFROM>
<BANKID>" & BankID & "
<ACCTID>" & AcctID & "
<ACCTTYPE>CHECKING
</BANKACCTFROM>
<BANKTRANLIST>
"
property qbo_footer : ""
property qbo_date : ""
------------------------------------------------
set theFile to choose file with prompt "Select a csv file:"
tell application "Finder"
set theParent to container of file theFile as alias
set baseName to theFile's container's name
display dialog ("This could take up to 30 seconds. You will be notified when this is done.") giving up after 1
end tell
set theFileReference to open for access theFile
set theFileContents to read theFileReference
close access theFileReference
if theFileContents contains LF then
set theFileReference to open for access theFile
set theFileContents to read theFileReference using delimiter LF
close access theFileReference
else
set theFileReference to open for access theFile
set theFileContents to read theFileReference using delimiter cr
close access theFileReference
end if
set totalItems to count items in theFileContents
-- the first line has the 1) account number 2) start and 3) end dates, and the 4) starting account balance, 5) ending account balance so parse the first item separately
set this_item to item 1 of theFileContents as text
-- item 2 of this_item is the start date; item 3 is the end date
my parseCSV(this_item)
set end_date to item 3 of theTextItems
my parseDate(end_date)
set DTEND to (item 1 of parsedDate & item 2 of parsedDate & item 3 of parsedDate) & 120000
set qbo_date to "<DTSTART>19700101120000
<DTEND>" & DTEND & "
"
set Balamt to item 5 of theTextItems
set Dtasof to DTEND
set qbo_footer to "</BANKTRANLIST>
<LEDGERBAL>
<BALAMT>" & Balamt & "
<DTASOF>" & Dtasof & "
</LEDGERBAL>
</STMTRS>
</STMTTRNRS>
</BANKMSGSRSV1>
</OFX>"
--create the initial webconnect file with the header
write_to_file from qbo_header into ((theParent as string) & baseName & " webconnect.qbo") without append
--add the start and end dates
write_to_file from qbo_date into ((theParent as string) & baseName & " webconnect.qbo") with append
set previousTransactionDate to ""
set previousTransactionNumber to 0
--store all of the transactions
repeat with i from 2 to totalItems
set this_item to item i of theFileContents as text
my parseCSV(this_item)
--item 1 of theTextItems (returned after my parseCSV) is the date, and you have to strip out the slashes with my parseDate
--item 2 is the amount, but the CSV doesn't store the negative sign if it is a check or debit. Will need to eval and multiply by -1 if nec.
--item 3 is the name
--item 4 is the memo, and some of these may be blank
--item 5 a transaction ID that is different in the CSV from the transaction ID that is created when PNC generated the qbo file. Intuit says FITIDs need to be persistent for the lifetime of the transaction. If a customer downloads the same transaction multiple times, then this transaction always has the same FITID value. FITIDs need to be unique for each transaction within the same account. Quicken filters out all transactions that contain duplicate FITIDs. PNC seems to create the transaction ID with a combination of my account number, the posted date YYYYMMDD, and the last digit is the number of the transaction that day.
--item 6 is the transaction type credit or debit
--step 1, parse the date
my parseDate(item 1 of theTextItems)
set DTPosted to "<DTPOSTED>" & parsedDate & "120000"
set DTUser to "<DTUSER>" & parsedDate & "120000"
--need to save this date to allow comparison for construction of the FITID. If the previous transaction has the same posted date, need to increment a last digit to get appended to the end of the constructed FITID.
--step 2: store the amount
set TRNAMT to (item 2 of theTextItems) --set positive or negative in a couple of steps below
--step 3: store the name NOTE: can't be greater than 32 characters long, so have to truncate it if it is.
my removeQuotes(item 3 of theTextItems)
set MemoTemp to trimmed --so I can set the memo to the long file name if the memo field is blank, because the memo field can be 255 characters and none of the names are this long
my truncateTo32(trimmed)
set NAME_ to truncated
--step 4: store the memo
my removeQuotes(item 4 of theTextItems)
if trimmed = " " then
set MEMO to MemoTemp
else
set MEMO to trimmed
end if
--step 5: store the transaction ID
set FITID_temp to acctNo & parsedDate
if previousTransactionDate = parsedDate then
--then I need to increment the previousTransactionNumber to allow the FITID to be constructed
set currentTransactionNumber to previousTransactionNumber + 1
set previousTransactionNumber to currentTransactionNumber
else
set previousTransactionNumber to 0
set currentTransactionNumber to previousTransactionNumber + 1
set previousTransactionNumber to currentTransactionNumber
end if
set FITID to FITID_temp & currentTransactionNumber
set previousTransactionDate to parsedDate
--step 6: set the transaction type
my removeQuotes(item 6 of theTextItems)
if NAME_ contains "CHECK" then
set TRNTYPE to "CHECK"
else
set TRNTYPE to trimmed
end if
--step 8 make the amount negative if it is a debit
if TRNTYPE = "DEBIT" or TRNTYPE = "CHECK" then
set TRNAMT to "-" & TRNAMT
set CHECKNUM to word 2 of NAME_
end if
--put everything together as a transaction (if it is not a check, do not include the checknum field)
if TRNTYPE = "CHECK" then
set transactionList to "<STMTTRN>
<TRNTYPE>" & TRNTYPE & "
" & DTPosted & "
" & DTUser & "
<TRNAMT>" & TRNAMT & "
<FITID>" & FITID & "
<CHECKNUM>" & CHECKNUM & "
<NAME>" & NAME_ & "
<MEMO>" & MEMO & "
</STMTTRN>
"
end if
if TRNTYPE is not equal to "CHECK" then
set transactionList to "<STMTTRN>
<TRNTYPE>" & TRNTYPE & "
" & DTPosted & "
" & DTUser & "
<TRNAMT>" & TRNAMT & "
<FITID>" & FITID & "
<NAME>" & NAME_ & "
<MEMO>" & MEMO & "
</STMTTRN>
"
end if
write_to_file from transactionList into ((theParent as string) & baseName & " webconnect.qbo") with append
end repeat
--add the footer, then done
write_to_file from qbo_footer into ((theParent as string) & baseName & " webconnect.qbo") with append
tell application "Finder"
activate
display dialog ("Done." & return & "The quickbooks webconnect file is now ready in the same folder as the .csv file that was processed.") giving up after 3
end tell
return
---------------------------------------subroutines
on write_to_file from |data| into target given append:append --target is a path string
try
set open_target_file to open for access file target with write permission
if append is false then set eof of open_target_file to 0
write |data| to open_target_file starting at eof
close access open_target_file
return true
on error
try
close access file target
end try
return false
end try
end write_to_file
---------------------------------------------
on removeQuotes(y)
set len to y's length
if len > 1 then
set trimmed to text 2 thru (len - 1) of y as text
else
set trimmed to " "
end if
end removeQuotes
-----------------------------------------------
on parseCSV(z)
set old_delim to AppleScript's text item delimiters
set AppleScript's text item delimiters to ","
set theTextItems to text items of z
set AppleScript's text item delimiters to old_delim
end parseCSV
-----------------------------------------------------
on parseDate(z)
set old_delim to AppleScript's text item delimiters
set AppleScript's text item delimiters to "/"
set parsedDate to text items of z
set AppleScript's text item delimiters to old_delim
end parseDate
on truncateTo32(y)
set len to y's length
if len ≥ 32 then
set truncated to text 1 thru 32 of y as text
else
set truncated to y
end if
end truncateTo32