Convert csv to qbo

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


I was interested in the script. Although, I found a lot of redundant properties and operations. I tried to remove all unnecessary and optimize the script. Unfortunately, I did not find a CSV file with the correct structure to test. Here is the code:

NOTE: is either “CHECK”, “DEBIT”, OR “CREDIT”


property DTSTART : "19700101120000" --this seems to be the same for all qbo files that I looked at
property org : ""
property FID : ""
property BankID : ""
property 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>
" ----------------------------------------------------------------------------------------------------------

set aFile to choose file with prompt "Select a CSV file:"
tell application "Finder"
	set aParentFolder to (aFile's container) as alias
	set baseName to aParentFolder's name
end tell
display notification "This could take up to 30 seconds. You will be notified when this is done."
set theFileContents to paragraphs of (read aFile)
display notification "The reading of CSV flie's contents is done."
set totalItems to count 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 accountNumber to item 1 of theFileContents
-- item 2 of this_item is the start date; item 3 is the end date
set theTextItems to my parseCSV(accountNumber)
set end_date to item 3 of theTextItems
set parsedDate to 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
set aPath to (POSIX path of aParentFolder) & baseName & " webconnect.qbo"
write_to_file from qbo_header into aPath without append
--add the start and end dates
write_to_file from qbo_date into aPath with append
set {previousTransactionDate, previousTransactionNumber} to {"", 0}
--------------------------------------------------------------------------------------------------------------------

--store all of the transactions
repeat with i from 2 to totalItems
	set anitem to item i of theFileContents
	set theTextItems to my parseCSV(anitem)
	--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
	set parsedDate to (my parseDate(item 1 of theTextItems)) & "120000"
	set {DTPosted, DTUser} to {"<DTPOSTED>" & parsedDate, "<DTUSER>" & parsedDate}
	--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.
	set MemoTemp to my removeQuotes(item 3 of theTextItems)
	set NAME_ to my truncateTo32(MemoTemp)
	
	--step 4: store the memo
	set trimmed to my removeQuotes(item 4 of theTextItems)
	if trimmed is " " then
		set MEMO to MemoTemp
	else
		set MEMO to trimmed
	end if
	
	--step 5: store the transaction ID
	set FITID_temp to acctNo & parsedDate
	-- then I need to increment the previousTransactionNumber to allow the FITID to be constructed
	if previousTransactionDate is not parsedDate then set previousTransactionNumber to 0
	set currentTransactionNumber to previousTransactionNumber + 1
	set previousTransactionNumber to currentTransactionNumber
	set FITID to FITID_temp & currentTransactionNumber
	set previousTransactionDate to parsedDate
	
	
	--step 6: set the transaction type
	set trimmed to 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 is "CHECK" then
		set transactionList to "<STMTTRN>
<TRNTYPE>" & TRNTYPE & "
" & DTPosted & "
" & DTUser & "
<TRNAMT>" & TRNAMT & "
<FITID>" & FITID & "
<CHECKNUM>" & CHECKNUM & "
<NAME>" & NAME_ & "
<MEMO>" & MEMO & "
</STMTTRN>
"
	else
		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 aPath with append
end repeat

--add the footer, then done
write_to_file from qbo_footer into aPath with append
display notification "Done." & return & "The quickbooks webconnect file is now ready in the same folder as the .csv file that was processed."


--------------------------------------- SUBROUTINES (HANDLERS) --------------------------------------
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
	return trimmed
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
	return theTextItems
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
	return parsedDate
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
	return truncated
end truncateTo32