; AutoHotkey ; Version: 1.x ; Language: English ; Platform: Win9x/NT ; Author: Kevin B. Carpenter ; ; Script Function: ; Convert a QIF file to CSV. The Quicken notes are here and they are from the ; following URL: http://web.intuit.com/support/quicken/docs/d_qif.html. ; #SingleInstance force #Persistent #NoEnv ; Recommended for performance and compatibility with future AutoHotkey releases. SendMode Input ; Recommended for new scripts due to its superior speed and reliability. $qifFile := "" $csvFile := "" $totLines := 0 Gosub ShowMainForm Return ;=============================================================================== ; Gui Subs ;=============================================================================== ShowMainForm: Gui, Font, S10 CDefault, Verdana Gui, Add, Text, x6 y7 w60 h20 , QIF File: Gui, Add, Edit, x76 y7 w600 h20 v$qifFile, Select QIF Input File Gui, Add, Button, x686 y7 w60 h20 gOpenQIFFile, Open Gui, Add, Text, x6 y37 w60 h20 , CSV File: Gui, Add, Edit, x76 y37 w600 h20 v$csvFile, Gui, Add, Button, x46 y547 w100 h30 gConvertFile, Convert Gui, Add, Button, x606 y547 w100 h30 gGuiClose, Quit Gui, Add, Button, x336 y547 w100 h30 , Pause Gui, Add, Tab, x6 y67 w740 h470 -Wrap v$tabs, Accounts Gui, Tab, Accounts Gui, Add, ListView, x16 y97 w720 h430 v$lvAccounts, Name|Type ; Generated using SmartGUI Creator 4.0 Gui, Show, x131 y91 h602 w757, QIF2CSV File Converter Return OpenQIFFile: FileSelectFile $qifFile, 1, , Select QIF File, *.qif GuiControl,, $qifFile, %$qifFile% $csvFile := RegExReplace($qifFile, "i)\.qif$", ".csv", ReplacementCount) GuiControl,, $csvFile, %$csvFile% FileDelete, %$csvFile% return OpenCSVFile: FileSelectFile, $csvFile, 1, Select Output File GuiControl,, $csvFile, %$csvFile% return ConvertFile: ; Start the reading line by line. LV_ModifyCol(1,200) LV_ModifyCol(2,50) ;------------------------------------------------------------------- ; Read the file and process line by line sequentially ;------------------------------------------------------------------- Loop, read, %$qifFile%, %$csvFile% { $fPos := RegExMatch(A_LoopReadLine, "(.)(.*)", $p) ;MsgBox %$p1% - %$p2% - %$as% - %$asAccts% ; Account Switch/List Section if (($p1="!") && ($p2="Option:AutoSwitch")) { $as := 1 Continue } ; account listing? if (($as=1) && ($p1="!") && ($p2="Account")) { $acctList = 1 Continue } ; account name if (($as=1) && ($p1="N")) { $asAccts = %$p2%|%$asAccts% $lvName = %$p2% Continue } ; account type if (($as=1) && ($p1="T")) { $lvType = %$p2% Continue } if (($p1="^") && ($as=1)) { ;MsgBox %$lvDate% %$lvAmount% %$lvCleared%`n %$lvPayee% %$lvCat%`n%$lvMemo% ;MsgBox %$lvName% %$lvType% LV_ADD("", $lvName, $lvType) FileAppend, %$lvName%`t%$lvType%`n, %$csvFile% $lvName := "" $lvType := "" Continue } if (($p1="!") && ($p2="Clear:AutoSwitch")) { $as := 0 GuiControl,, $tabs, %$asAccts% Continue ;Break } ; find start of account if (($as=0) && ($p1="!") && ($p2="Account")) { ; probably start of trans list. $tlHeader = 1 } if (($as=0) && ($p1="N") && ($tlHeader=1)) { ; probably start of trans list. Gui, Tab, %$p2% Gui, Add, ListView, x16 y97 w720 h430, Date|Amount|Cleared|Num|Payee|Memo|Address|LCategory|SCategory|SMemo|SAmount LV_ModifyCol(1,70) LV_ModifyCol(2,70) LV_ModifyCol(3,25) LV_ModifyCol(4,40) LV_ModifyCol(5,200) LV_ModifyCol(6,200) LV_ModifyCol(7,100) LV_ModifyCol(8,100) } if (($as=0) && ($p1="^") && ($tlHeader=1)) { ; probably start of trans list. $tlHeader = 0 } ; This is converting actual account lines. if (($as=0) && ($p1="D") && ($tlHeader=0)) { $lvDate := $p2 Continue } if (($as=0) && ($p1="T") && ($tlHeader=0)) { $lvAmount := $p2 Continue } if (($as=0) && ($p1="C") && ($tlHeader=0)) { $lvCleared := $p2 Continue } if (($as=0) && ($p1="P") && ($tlHeader=0)) { $lvPayee := $p2 Continue } if (($as=0) && ($p1="L") && ($tlHeader=0)) { $lvCat := $p2 Continue } if (($as=0) && ($p1="M") && ($tlHeader=0)) { $lvMemo := $p2 Continue } if (($p1="^") && ($as=0) && ($tlHeader=0)) { ;MsgBox %$lvDate% %$lvAmount% %$lvCleared%`n %$lvPayee% %$lvCat%`n%$lvMemo% LV_ADD("", $lvDate, $lvAmount, $lvCleared, "", $lvPayee, $lvMemo, "", $lvCat) FileAppend, %$lvDate%`t%$lvAmount%`t%$lvCleared%`t%$lvPayee%`t%$lvMemo%`t%$lvCat%`n, %$csvFile% $lvDate := "" $lvAmount := "" $lvCleared := "" $lvPayee := "" $lvCat := "" $lvMemo := "" Continue } } return CreateListView: $LV = $LVCOUNT := $iRows + 1 Loop, Parse, $sResult, `n { If (A_Index = 1) { Gui, Add, ListView, r20 w420 Grid Count%$LVCOUNT%, %A_LoopField% } Else { StringSplit, $LV, A_LoopField, | LV_ADD("", $LV1, $LV2, $LV3, $LV4) } } LV_ModifyCol(1, 35) LV_ModifyCol(2, 35) LV_ModifyCol(3, 150) LV_ModifyCol(4, 100) Gui, Show, Autosize Center, SQLite Result - Close with ESC Return GuiClose: ExitApp /* Quicken Interchange Format (QIF) files The Quicken interchange format (QIF) is a specially formatted text (ASCII) file that enables Quicken transactions to be moved from one Quicken account register into another Quicken account register, or to or from other programs that support the QIF format. Note: For Quicken to translate data from a text file into the Quicken register as transactions, the text file must be in the QIF format. Required File Formatting Each transaction must end with a symbol, indicating the end of entry. Each item in the transaction must display on a separate line. When Quicken exports an account register or list, it adds a line to the top of the file that identifies the type of account or list. Listed below are the header lines Quicken adds to the exported files: Header Type of data !Type:Bank Bank account transactions !Type:Cash Cash account transactions !Type:CCard Credit card account transactions !Type:Invst Investment account transactions !Type:Oth A Asset account transactions !Type:Oth L Liability account transactions !Account Account list or which account follows !Type:Cat Category list !Type:Class Class list !Type:Memorized Memorized transaction list Quicken can be configured to import all transfers, regardless of whether Ignore Transfers is selected when the file is imported. To do this, add a line to the file being imported into the Quicken account. Use a text editor or word processor to put the following line immediately after the header line at the top of the file: !Option:AllXfr Items for Non-Investment Accounts Each item in a bank, cash, credit card, other liability, or other asset account must begin with a letter that indicates the field in the Quicken register. The non-split items can be in any sequence: Field Indicator Explanations Date|Amount|Cleared|Num|Payee|Memo|Address|LCategory|SCategory|SMemo|SAmount D Date T Amount C Cleared status N Num (check or reference number) P Payee M Memo A Address (up to five lines; the sixth line is an optional message) L Category (Category/Subcategory/Transfer/Class) S Category in split (Category/Transfer/Class) E Memo in split $ Dollar amount of split ^ End of entry Note: Repeat the S, E, and $ lines as many times as needed for additional items in a split. If an item is omitted from the transaction in the QIF file, Quicken treats it as a blank item. Items for Investment Accounts Field Indicator Explanation D Date N Action Y Security I Price Q Quantity (number of shares or split ratio) T Transaction amount C Cleared status P Text in the first line for transfers and reminders M Memo O Commission L Account for the transfer $ Amount transferred ^ End of entry Items for Account Information The account header !Account is used in two places, at the start of an account list and the start of a list of transactions to specify to which account they belong. Field Indicator Explanation N Name T Type of account D Description L Credit limit (only for credit card account) / Statement balance date $ Statement balance ^ End of entry Items for a Category List Field Indicator Explanation N Category name:subcategory name D Description T Tax related if included, not tax related if omitted I Income category E Expense category (if category is unspecified, Quicken assumes expense type) B Budget amount (only in a Budget Amounts QIF file) R Tax schedule information ^ End of entry Items for a Class List Field Indicator Explanation N Class name D Description ^ End of entry Items for a Memorized Transaction List Immediately preceding the ^ character, each entry must end with one of the following file indicators to specify the transaction type. KC KD KP KI KE With that exception, memorized transaction entries have the same format as regular transaction entries (non-investment accounts). However, the Date or Num field is included. All items are optional, but if an amortization record is included, all seven amortization lines must also be included. Field Indicator Explanation KC Check transaction KD Deposit transaction KP Payment transaction KI Investment transaction KE Electronic payee transaction T Amount C Cleared status P Payee M Memo A Address L Category or Transfer/Class S Category/class in split E Memo in split $ Dollar amount of split 1 Amortization: First payment date 2 Amortization: Total years for loan 3 Amortization: Number of payments already made 4 Amortization: Number of periods per year 5 Amortization: Interest rate 6 Amortization: Current loan balance 7 Amortization: Original loan amount ^ End of entry Examples of QIF files Normal Transactions Example Transaction Item Comment (not in file) !Type:Bank Header D6/ 1/94 Date T-1,000.00 Amount N1005 Check number PBank Of Mortgage Payee L[linda] Category S[linda] First category in split $-253.64 First amount in split SMort Int Second category in split $=746.36 Second amount in split ^ End of transaction D6/ 2/94 Date T75.00 Amount PDeposit Payee ^ End of transaction D6/ 3/94 Date T-10.00 Amount PAnthony Hopkins Payee MFilm Memo LEntertain Category AP.O. Box 27027 Address (line 1) ATucson, AZ Address (line 2) A85726 Address (line 3) A Address (line 4) A Address (line 5) A Address (line 6) ^ End of transaction Investment Example Transaction Item Comment (not in file) !Type:Invst Header line D8/25/93 Date NShrsIn Action (optional) Yibm4 Security I11.260 Price Q88.81 Quantity CX Cleared status T1,000.00 Amount MOpening Balance Memo ^ End of transaction D8/25/93 Date NBuyX Action Yibm4 Security I11.030 Price Q9.066 Quantity T100.00 Amount MEst. price as of 8/25/93 Memo L[CHECKING] Account for transfer $100.00 Amount transferred ^ End of transaction Memorized List Example Transaction Item Comment (not in file) !Type:Memorized Header line T-50.00 Amount POakwood Gardens Payee MRent Memo KC Check transaction ^ End of transaction */