CSV / QBO to IIF converter.

[+Warwagon MVC]

Quickbooks QBO / CSV to IIF converter

Join my new Telegram Channel for
https://t.me/+XRqUEpR5e_g4Y2Yx <----Telegram Channel link.

New versions announcements

Preview of new and upcoming features.
Betas for just the group to test
Vote on new features
And more

Disclaimer: Use at your own risk. I made this app for my needs and it works fantastically. If you use this app back up your company file before importing the iif file.

This program lets you import a CSV, QBO or OFX file and convert it to an IIF file to import into Quickbooks.

This program is for people who import Bank, Credit card, and Transfer accounts. It does not support accounts receivable in Quickbooks.

It also includes a pattern file editor, which lets you match up, rename, split, and put them in the correct account before you create the iif file. This does A LOT of the heavy lifting for you, so you won't even have to handle them once they get imported into Quickbooks.

I'm giving this app away for free due to Intuit's greed. I'm sure people will be death-gripping older versions of QuickBooks for years. They will need some way to import their transactions into QuickBooks. Before this program, the only other app out there I could find with the same functionality was $120 per year.

Unlike other free converters, you do not have to create an account or provide any personal information to use it.

The app (4.1 or newer) has a donation button if you want to donate.

This is a program I created in conjunction with Chatgpt. It's in Python, but I do have some EXEs available. You can also take the source code available here and compile it on your own. The Dropbox links are toward the bottom

I am aware of only a few other programs that do this, and they cost $120 per year.

The iff file should be imported without issue up to Quickbooks 2018. In Quickbooks 2019 and newer, if you import your iif file the new way, it will work, but descriptions on deposit for bank transactions seem to be left blank, and check numbers are added for each debit.

The iff file should be imported without issue up to Quickbooks 2018. In Quickbooks 2019 and newer, if you import your iif file the new way, it will work, but descriptions on deposit for bank transactions seem to be left blank, and check numbers are added for each debit.

2019 and newer still give you the option to.

**"Import it for me. I'll fix it later."**This is the old method used up until Quickbooks 2018

People on the internet say using that method IIF's import without issue

Starting with version 4.1, if the program tells you that your CSV file is incompatible, you can map the fields yourself by clicking the "Let me try mapping it by hand" button at the bottom right of the box. Even though that should let you open the CSV just fine, I would still like to be contacted about your CSV so I can add native support for the CSV file into the app for everyone else.

There is a tutorial video at the bottom; give it a watch! Come back here for updates on the latest versions, posted at the bottom of this page.

 This is what the program looks like. 

Included in the app is a pattern editor

The Pattern Editor allows you to create, modify, and edit transaction rules to import into the first program. The pattern file allows you to tell the program...

• If a transaction has X in the name, rename the transaction to X for (Example, if a transaction has WM SUPERCENTER in the name such as 648105 PURCHASE WM SUPERCENTER #1625 712-546-4900 IA 10277255 4041 .. rename to "WAL-MART"
• If a transaction is named X and has X amount name it this. If it does not have X amount, still give it a name but just put it over in Premier Offset so I can tell it where to go.
• If a transaction has X in the name, rename it and add it to this account. For example, if a transaction had " HY-VEE F&F" in the name, such as "995471 PURCHASE HY-VEE F&F LE MARS 5382 LE MARS IA 4445043046110 4," rename it to "HY-VEE FUEL" and add it to the account "Business:Automobile Expense:Fuel."
• Split a transaction to the tune of 2 splits. I wasn't aware that you could split a transaction in an IIF file, but it works great! You just enter amount 1 for account 1 and amount 2 for account 2. (it's in the screenshot)
• If a transaction has X in the name and transaction amount is X $ rename the transaction and put it in this account. I did this because my Verizon gets put on my VISA card every month, but other things get put on there too. So in months when the VISA matches $36.38 it automatically puts VISA in Business:Direct Expenses - Business:Telephone:Cellphone.
• You can also have it automatically add memo's for certain transactions who's name/total or just name matches.
• You can mark a vendor as "Transfer," so all of their transactions are always marked as "Transfer and are automatically checked

IIF Import Troubleshooter

The app includes an import troubleshooter. This tool allows you to take the message Quickbooks gives you after a failed import (2019 and newer), paste it in, and convert it to plain English.

Balance Tool

This app also includes something called "Balance Tool". If you import your IIF into Quickbooks your account doesn't balance, you can use this tool to help figure out why.

It lets you

• Enter the balance at the end of the previous month and the balance at the end of the current month after importing the iif file and it will tell you how much you are off by. It will also tell you if any transactions match that exact amount.
• Check off transactions as you go down your transaction list in QuickBooks.
• Import a comma-delimited file exported from Quickbooks and it will compare your running balances in Quickbooks against the Balance tool and let you know which days you balanced. Each date where you balanced will be highlighted in green. You can then quickly figure out when your account stopped balancing.
• It tells you in plain English if you have to much or too little money in QuickBooks.
• You can check for the possibility of transposed numbers
• It tells you if you are missing any despots on QuickBooks or on the bank side

Checking off the transactions as you go down the list

In the example above this account did not balance and per the balance tool, shows it 
stopped balancing only on July 1st.

Once the issue was fixed and the account balanced, the CSV was imported again and 
this time you can see it looks much better.

Download Link

If you find a bug in the "Latest" release, please report the bug to me. In the meantime, try the release ending in "Previous" on the drop box.

Files ending in Beta or Alpha are test versions, so there may be bugs.

https://www.dropbox.com/scl/fo/t088czui5pzgsm998w2hi/AMJrjSJMw2nRzca6jgIIO6k?rlkey=w6vtqby1kzrfy7l7xroh13p5w&st=3y7sdpmp&dl=

Tutorial

Change logs

v4.4.9.5 Latest
August 28th, 2024:

• New: Added a "Check Deposits" button to the top of the Balance Tool. This checks the bank deposits against Quickbooks deposits and vice versa and let you know if either the bank or Quickbooks is missing a deposit.
• New: Once a CSV is loaded from QuickBooks into the Balance Tool, it also checks QuickBooks to see if a transaction matches the amount you are currently off.
• New: Added a "Check Deposits" button to the top of the Balance Tool. This checks the bank deposits against Quickbooks deposits and vice versa and let you know if either the bank or Quickbooks is missing a deposit.
• New:  Once a CSV is loaded from QuickBooks into the Balance Tool, it also checks QuickBooks to see if a transaction matches the amount you are currently off.
• Fixed: Match amount in the rules file now matches both debits AND credits. It was only matching Debits before
• New: When importing a CSV file to compare what files are in QuickBooks, a dialog box now tells you how many transactions are already in QuickBooks.
• New: The dialog boxes in the balance tool have been updated. If your transactions are off, it tells you in plain English what the problem is.
• Update: Check deposits now show the date as well
• New: In the balance tool, there is now a "Check for Transposed Numbers " button. When clicked, it now checks for the transaction amount for amounts that might have been transposed. It does this by taking each transaction amount, rearranging the numbers, subtracting it from the original amount, and seeing if the amount it gets matches the amount you are off.

v4.4.9.1 Previous
August 4th, 2024 :

• New: Includes a new tool called "Balance Tool" its located in the top right under "Other options" You can find more about it above.
• New: Added an entry in the help menu called "Balance Tool"

v4.4.8 Previous
July 28th, 2024 :

• Updated: You can now leave the "Replace with" Blank in the rules editor
• Updated: When you are mapping csv's by hand you can now see the first 7 transactions instead of just the first one.

v4.4.6 Previous
July 22th, 2024 :

• Fix : Fixed the misspelling of a column header for PNC Bank
• New: Added CSV support for Paypal payment exports.

v4.4.5 
July 12th, 2024 :

• Updated : Added CSV support for PNC Bank

v4.4.4 
July 10th, 2024 :

• Updated: If you have transactions with the same name and different amounts, and if you set an amount rule it now matches by amount first. Then it goes back and matches the nonamount rules. This prevents the first rule from renaming the transaction and leaving the 2nd rule no transaction to match by amount
•  

Shortcut Key Cheat Sheet

Select All: Ctrl + A
Select All Except Ignore: Ctrl + I
Select None: Ctrl + N
Swap Debit/Credit: Ctrl + S
Load Patterns from File: Ctrl + L
Show Uncategorized Transactions: Ctrl + U
Convert to IIF: Ctrl + E
Set New Default Account: Ctrl + Shift + N
Select All Credits Except Ignore: Ctrl + Shift + C
Select All Debits Except Ignore: Ctrl + D
Autocomplete Options: Ctrl + O

 

[+Warwagon MVC]

OLD POST. I thought I would leave it here so that you can see how fugly the first version of the pattern file was was 😄

Here is a definition editor. It's super sweet. I used to add everything to my definitions file. It allows you to create a definition file using the file name of your choosing. Once you save it it opens it up and you can start adding stuff to it. You can also load it or a different one later

On the left shows your code in the definition file as you add new items. The right shows you every company you've added to the file. It automatically saves as you add stuff.

pip install PyQt5
pip install wxPython

Compiled EXE on Dropbox

https://www.dropbox.com/scl/fi/zxw2214v2yphiptyv5r9z/Definition-Editor.exe?rlkey=77k7s6e9i8vcjyc3odg52o9al&dl=0

[NexGen.Media]

Hello!

I was super excited to come across your solution, as I am also looking at the ProperConvert app and thinking that it's priced a little too high for my use case (which would essentially be a once-a-year affair). However, I tried your app and it doesn't work for me. The app loads, however, when I load my CSV (40KB) which I downloaded directly from my bank, the app just closes. I tried the .exe on Windows 11 both natively on an Intel machine and also in a Win 11 VM on Arm via VMware Fusion (running on a M3 mac w/ Somoma) with the same result. The ProperConvert app I am trialing opens the file without issue.

Any ideas?

Cheers,
Dan
NexGen.Media

Edited February 22 by NexGen.Media

[+Warwagon MVC]

It's also possible that all CSV's are not created equal. I created that app, around the CSV I was given to by my bank.

If you wanted to get an error message you would have to install Python from the Microsoft store, then open a command prompt and type in these 2 commands.

pip install PyQt5
pip install wxPython

Then you would have to take the code I posted above, paste it into a notepad document and save it as a py file. Then in the command prompt type python filename.py and load the app that way. then when it crashes, it should give you some error inside the back box. I suspect your CSV file is formatted differently than mine.

I'm super bummed the app crashed on you. I was excited for you to use the app.

If you open your CSV file with notepad do your column headers look like this

[NexGen.Media]

Hello,

I really appreciate the quick response and you trying to assist. I actually run a web/software dev firm so familiar with code/Python. Before I came across your post I was thinking about writing something myself. I did try to load the app via Python 3.12 using the .py file and couldn't get the app to run or even log an error (just opened and closed). Again this is all on Win 11 and never tried on Win 10.  Nevertheless, with the time already spent in researching a solution and then trying your solution I am already past 1-hour of billable time, so decided to purchase the ProperSoft app and move on.

Thanks again though!

[+Warwagon MVC]

Version 2.0 Released

I noticed I never released the code for the JSON editor. Well, now I uploaded it.

It's too bad the one person who tried it, couldn't get it to work. Hopefully, more banks share my banks CSV format. I just used it today to balance my account, to test it out before posting the code here.

[+Warwagon MVC]

On 22/02/2024 at 13:19, NexGen.Media said:

Hello,

I really appreciate the quick response and you trying to assist. I actually run a web/software dev firm so familiar with code/Python. Before I came across your post I was thinking about writing something myself. I did try to load the app via Python 3.12 using the .py file and couldn't get the app to run or even log an error (just opened and closed). Again this is all on Win 11 and never tried on Win 10.  Nevertheless, with the time already spent in researching a solution and then trying your solution I am already past 1-hour of billable time, so decided to purchase the ProperSoft app and move on.

Thanks again though!

I know you already purchased the other solution, but I wanted to let you know that I have added support for QBO files, which should be standard across all banks.  So if The financial institution got rid of iif files but still gives you CSV as well as QBO, you can also import QBO.

[+Warwagon MVC]

Version 3.0 released!

[+Warwagon MVC]

Versions

3.01
3.02
3.03
3.04
3.05
3.06

Released

[robertwe4774]

I installed your program today. In the account section imported I entered my categories. When I try to import into QBOOKS as an IIF I get an error that states failed. These are a small few credit card transactions. I review and Failed. The error file shows. 

;
;*** YOUR IIF FILE COULDN'T BE IMPORTED ***   
;
;Some of your records have errors.
;
;1. Search for the errors by typing "[ERROR]" to see only the failed records.
;
;2. Fix the errors and try to import the file again
;
;-----------------------------------------------------------------------------
;
;
!TRNS    DATE    TRNSTYPE    ACCNT    DOCNUM    NAME    CLASS    AMOUNT    TOPRINT    MEMO
!SPL    DATE    TRNSTYPE    ACCNT    DOCNUM    NAME    CLASS    AMOUNT    MEMO
!ENDTRNS
; [ERROR]  is an invalid value for field TRNSTYPE. [15106] 
TRNS    03/17/2024        American Express Bonvoy        Brilliant Dining Credit        22.47
SPL    03/17/2024        PremierEcorp Debit Offset                -22.47
ENDTRNS
; [ERROR]  is an invalid value for field TRNSTYPE. [15106] 
TRNS    03/15/2024        American Express Bonvoy        AUTOPAY PAYMENT THANK YOU        51.88
SPL    03/15/2024        PremierEcorp Debit Offset                -51.88
ENDTRNS
; [ERROR]  is an invalid value for field TRNSTYPE. [15106] 
TRNS    03/12/2024        American Express Bonvoy        TST AUNT LULUS NY SBOCA RATON FL        -22.47
SPL    03/12/2024        PremierEcorp Debit Offset                22.47
ENDTRNS

In the transactions are any new vendors.credit card added into Quickbooks on the fly, or do I need to add them first? 
My edits of those transactions showing the proper account numbers did not save into the IIF file. I did this a few times. 
I must be missing something. I attached a screenshot of what I had before I clicked convert to IIF.

Any ideas?
Thanks

[+Warwagon MVC]

On 22/03/2024 at 10:56, robertwe4774 said:

I installed your program today. In the account section imported I entered my categories. When I try to import into QBOOKS as an IIF I get an error that states failed. These are a small few credit card transactions. I review and Failed. The error file shows. 

;
;*** YOUR IIF FILE COULDN'T BE IMPORTED ***   
;
;Some of your records have errors.
;
;1. Search for the errors by typing "[ERROR]" to see only the failed records.
;
;2. Fix the errors and try to import the file again
;
;-----------------------------------------------------------------------------
;
;
!TRNS    DATE    TRNSTYPE    ACCNT    DOCNUM    NAME    CLASS    AMOUNT    TOPRINT    MEMO
!SPL    DATE    TRNSTYPE    ACCNT    DOCNUM    NAME    CLASS    AMOUNT    MEMO
!ENDTRNS
; [ERROR]  is an invalid value for field TRNSTYPE. [15106] 
TRNS    03/17/2024        American Express Bonvoy        Brilliant Dining Credit        22.47
SPL    03/17/2024        PremierEcorp Debit Offset                -22.47
ENDTRNS
; [ERROR]  is an invalid value for field TRNSTYPE. [15106] 
TRNS    03/15/2024        American Express Bonvoy        AUTOPAY PAYMENT THANK YOU        51.88
SPL    03/15/2024        PremierEcorp Debit Offset                -51.88
ENDTRNS
; [ERROR]  is an invalid value for field TRNSTYPE. [15106] 
TRNS    03/12/2024        American Express Bonvoy        TST AUNT LULUS NY SBOCA RATON FL        -22.47
SPL    03/12/2024        PremierEcorp Debit Offset                22.47
ENDTRNS

In the transactions are any new vendors.credit card added into Quickbooks on the fly, or do I need to add them first? 
My edits of those transactions showing the proper account numbers did not save into the IIF file. I did this a few times. 
I must be missing something. I attached a screenshot of what I had before I clicked convert to IIF.

Any ideas?
Thanks

Thanks for the error reports. I'm not sure if they are added on the fly or not. Add those new accounts to quickbooks and try it again

[+Warwagon MVC]

On 22/03/2024 at 10:56, robertwe4774 said:

I installed your program today. In the account section imported I entered my categories. When I try to import into QBOOKS as an IIF I get an error that states failed. These are a small few credit card transactions. I review and Failed. The error file shows. 

;
;*** YOUR IIF FILE COULDN'T BE IMPORTED ***   
;
;Some of your records have errors.
;
;1. Search for the errors by typing "[ERROR]" to see only the failed records.
;
;2. Fix the errors and try to import the file again
;
;-----------------------------------------------------------------------------
;
;
!TRNS    DATE    TRNSTYPE    ACCNT    DOCNUM    NAME    CLASS    AMOUNT    TOPRINT    MEMO
!SPL    DATE    TRNSTYPE    ACCNT    DOCNUM    NAME    CLASS    AMOUNT    MEMO
!ENDTRNS
; [ERROR]  is an invalid value for field TRNSTYPE. [15106] 
TRNS    03/17/2024        American Express Bonvoy        Brilliant Dining Credit        22.47
SPL    03/17/2024        PremierEcorp Debit Offset                -22.47
ENDTRNS
; [ERROR]  is an invalid value for field TRNSTYPE. [15106] 
TRNS    03/15/2024        American Express Bonvoy        AUTOPAY PAYMENT THANK YOU        51.88
SPL    03/15/2024        PremierEcorp Debit Offset                -51.88
ENDTRNS
; [ERROR]  is an invalid value for field TRNSTYPE. [15106] 
TRNS    03/12/2024        American Express Bonvoy        TST AUNT LULUS NY SBOCA RATON FL        -22.47
SPL    03/12/2024        PremierEcorp Debit Offset                22.47
ENDTRNS

In the transactions are any new vendors.credit card added into Quickbooks on the fly, or do I need to add them first? 
My edits of those transactions showing the proper account numbers did not save into the IIF file. I did this a few times. 
I must be missing something. I attached a screenshot of what I had before I clicked convert to IIF.

Any ideas?
Thanks

 

Also when entering an account that is a sub of another account it has to be in this format "Business:Indirect Expense:Utilities:Water" each sub-account separated by a colon.

So in your screenshot if meals was a subcategory of 6640 it should be entered as 6640:Meals

*Update*

I tried to recreate the issue without much luck. Those account names did not throw an error, at least for me.  I also sent you a personal message

[+Warwagon MVC]

On 22/03/2024 at 10:56, robertwe4774 said:

 

So I recreated everything as best I could and it imported into QuickBooks without issue. In the future, I'll make a note of this in the app but If you want to add an account for a transaction in the account column, it has to be added from the definition file. If you type in the account field, it's ignored on export.

[+Warwagon MVC]

Created and added a tutorial to the first post.

[+Warwagon MVC]

Version 3.6 released

[+Warwagon MVC]

Version 3.8 released

[Jason Doster]

Thank you so much for delving into this. I am trying out your Python code to convert QBO to IIF and import to QB Desktop 2021. I know you are using 2018 and perhaps 2019. Just for SA, I am getting errors when I try and import into 2021 currently.

I tell ya, with QB Desktop 2021 about to stop allowing transaction QBO imports at end of May, your code here may become the genesis of something very much needed by many. 2021 is the LAST Quickbooks that is an indefinite time license. Many folks will be using 2021 for years to come, including myself, who refuses to go online with my company's data, so I'll fight tooth and nail to continue to do transaction imports from banks and credit cards, as that's the functionality that will be needed to keep QB Desktop 2021 truly functional an efficient.

I know how to Python code and do UIs myself. I'll take a look at your code. My issue is I don't know Quickbook formats things and it's basic construct nearly as well as you. Would like to collaborate.

Thx,

Jason Doster

[Jason Doster]

Belay that, reading through your puts I ignored the errors and imported anyway. It worked in QB 2021. OK, now looking at your patterns editor, what would be awesome is if we could export the current QB accounts, and have them available to choose from automatically in a list. Right now it would be copy and paste, but perhaps that is the way it goes initially until the pattern file is done.

Love what you've done here man. Outstanding.

[+Warwagon MVC]

On 14/04/2024 at 23:46, Jason Doster said:

Belay that, reading through your puts I ignored the errors and imported anyway. It worked in QB 2021. OK, now looking at your patterns editor, what would be awesome is if we could export the current QB accounts, and have them available to choose from automatically in a list. Right now it would be copy and paste, but perhaps that is the way it goes initially until the pattern file is done.

Love what you've done here man. Outstanding.

 

Actually, take a look in the download folder on Dropbox for "Pattern Maker." You can go to your bank account on Quickbooks, select print, then choose "File" and select " "Tab delimited file". Then, import it into the program and convert it into a JSON file

This will create a compatible JSON file for the pattern editor with every vendor you've used in that account through the dates you've specified on export and all the categories already filled in. Of course, you can go through and edit them. Plus, now every account is available for auto-complete.

Once they are all imported, you'll have to tell each one what to match with.

I'm thinking of incorporating this program into the dialog when opening the pattern editor. You would have a choice of

save (which should be renamed to create)
Open
Create from Account export (Or something)
Cancel

 

 

[+Warwagon MVC]

In version 3.8.3 beta 6

Added it right into the app

[Jason Doster]

Trying to get to the Dropbox to download the new version. Came up with an error. Is it still there? Thx! Jason

[+Warwagon MVC]

On 12/05/2024 at 18:50, Jason Doster said:

Trying to get to the Dropbox to download the new version. Came up with an error. Is it still there? Thx! Jason

Yes. I just clicked the link and it worked 

[+Warwagon MVC]

Version 4.0 has been released.

[Jason Doster]

Awesome. I've been working with 3.9 today. I'll download 4.0 next. Don't know why I couldn't get to the Dropbox the other day. Working fine now!

I do think this will allow everyone to work their Credit Card and Bank imports for Quickbooks. I've been comparing the IIF output files to intended required format, and it's really good. Thank you so much for doing this!

I used your "print file method" for my QB credit cards and bank accounts to create initial PATTERN files, imported the vendors and their associated accounts. Currently I have separate PATTERN jsons for each credit card and bank statement. I might combine later, but for now that works just fine. I only have 8 CC/Bank imports I have to do each month. Some notes:

- A transaction item given name had a "#" in front upon json creation that needs to be removed, and then of course given the appropriate syntax to line up with a record you want from the transaction list. That was easy, but took a bit as I was going through 250 patterns imported.

- When I have an import that needs to be split, I actually don't typically know the $ split-out until later. Currently the split needs to be defined with both an account and a $ value. It would be awesome if one could just define the account split out WITHOUT a $ value split until later, perhaps default the first split to all of the $ value, and the second to zero initially, and then only a simple change after import to QB. I dunno. No big deal, just an observation.

- In execution, after I import a QBO file and verify a PATTERN json file will suffice to sort, there are some generic bank-isms like "DEPOSIT" and "CHECK" and a few others that I actually know what they are, so I tried manually changing the name of the transaction to that name which is in the PATTERN json I know it is, all before loading the PATTERN. Then when I loaded the PATTERN, I expected it to pickup the changed transaction name, but instead it picks up the original name to sort. I expect this is because when you do the pattern sort, you are accomplishing on the original QBO, vice any change to the name of the transaction after QBO import. If you could somehow apply the PATTERN to the CHANGED transaction names after QBO import and modification, that would be VERY powerful. I could change the generic DEPOSIT or CHECK name to what I know it actually is, THEN apply the pattern and BAM, ready for IFF write without having to make those changes once in QB. 

Dude, once again, spectacular. If any of these are easy fixes, awesome. If not, thank you so much.

Thx! Jason

[Jason Doster]

Downloaded version 4.0 this morning and found you had already implemented the behavior of the 3rd bullet above, perfect. The first two above are still observations. A way to split items without specifying amounts initially, but breaking it out as sub-accounts would be helpful. Also, question:

- In the PATTERN load, would it be possible to place the OLD matched transaction name into the MEMO line for reference after the transaction has been matched and has a new transaction name

Thx! Jason