Monday, December 9, 2019

#1 2019-11-30 01:09:03 pm

robsilve
Member
Registered: 2006-06-24
Posts: 6

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

Applescript:


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

Offline

 

#2 2019-12-01 03:34:59 am

KniazidisR
Member
Registered: 2019-03-03
Posts: 768

Re: Convert csv to qbo

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: <TRNTYPE> is either "CHECK", "DEBIT", OR "CREDIT"

Applescript:


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

Last edited by KniazidisR (2019-12-01 03:41:33 am)


Model: MacBook Pro
macOS Mojave -- version 10.14.4
Safari -- version 12.1
Firefox -- version 70.0

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)