CSV / QBO to IIF converter.

[+Warwagon MVC]

Disclaimer: Use at your own risk. I made this app for my needs and it works fantastically. If you use this app BACKUP your company file before importing the iif file. You should be doing that anyway.

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

Quickbooks 2019 and above still give you the option at the bottom of the import screen 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

Do not sort by columns before loading the pattern file. Depending on which version you are using, it will either crash or corrupt the table data, and you'll have to click load CSV or QBO and load your file again.

I'm trying to fix the bug without much luck

Tutorial

 

Known Bugs

If you want to sort by date do so AFTER loading a pattern file.

Compiled EXE and source code on Dropbox.
I will now keep a copy of the previous version in the folder too. In case the latest release breaks something

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

NEW Editor UI! Both apps are now in a single application

 

Wasn't sure where to post this, but I thought this might be a good spot from now on.

This is an QBO/CSV to Quickbooks IIF converter I created with the help of Chat GPT. No, it's not written Powershell, yes it probably could have been written in PowerShell, no I don't want to learn PowerShell. (That was for the Powershell guy that always comes in these threads.)

I'm still using Quickbooks Pro 2018. A couple of years ago my bank stopped offering IIF files for transaction downloads and only offers CSV and OBO. I was never a fan of OBO files, so I went looking for a CSV to IIF converter. I only found one and it was $150 was called "Transactions". Looks like they have now changed the name and are charging $150 PER YEAR for it. https://www.propersoft.net/purchase/

I've been using it for a few years and it does work well. I just refuse to upgrade to the new Quickbooks as Intuit has become Greedy, and now it's a yearly cost of $500+ (when I used to buy it for $250, every 4 years)

So the other day I was wondering, if is it possible to with the help of chatGPT have one written in Python. A couple of days later I finished it. I've since converted it to EXE and added it to my created apps collection. I've put it through its paces and fixed all the bugs that I could see.

I thought I would just put this out there for anyone who wanted a free solution instead of paying $150 PER YEAR!

The blank white lines are lines that I've redacted for this post.

This is a screenshot of an older version. Above is the newer UI, I didn't want to create a new screenshot as it takes a lot of redacting.

It also includes what I call a “Pattern Editor” which is now built right into the main application.

 

 

The editor is pretty cool, it lets you create a Definition file which can be loaded into the main application. This allows you to do the following.

 

•  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 it 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. I did this because my Verizon gets put on my VISA card every month, but some months 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.

•  If a transaction has X in the name, rename the transaction and add it to this account. So 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 you can and it works great! You just enter amount 1 for account 1 and amount 2 for account 2. (its in the screenshot)

• •You can also have it automatically add memos for certain transactions who's name/total or just name matches.

To convert the source code into an exe

First, you would install pyinstaller

First, make sure to run this command from a CMD. This installs the requirement the my python application requires to run.

pip install PyQt5

Then navigate to the following folder depending on your Python version.. Put your Python source code file (.py) in that folder.

C:\Users\(username)\appdata\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\Scripts

Then open a powershell window and cd to the same folder.

Then run the following command

.\pyinstaller --onefile -w --icon=(name of your png if you want to use an icon).png '(Name of your file).py'

Example 

.\pyinstaller --onefile -w --icon=transfer.png 'copythese.py'

You will find the converted EXE inside the dist folder located inside the path given above.

v3.9 Beta 5
April 28th, 2024

• New/Fixed: Disabled Sorting. If you were to sort any column and load a pattern file, it crashed. If you do not sort, load a pattern file, and then sort and export, transfers end up in the wrong transactions. So, I turned off the ability to sort.
• New: Added a button at the top that says " Show uncategorized transactions". I spoke with a beta tester who said he sorted only to find uncategorized transactions. Now, you can just push a button. It's even easier than sorting
• New: I never really mentioned this, but many versions ago, I added CTRL+F Functionality, which would bring up a search box on the bottom of the application, letting you search transactions. Now I have made that box visible and added searching accounts and descriptions.
• New: After a pattern file is loaded, you can no longer load another one unless you reload your CSV or QBO. In some edit cases I've seen the table data get corrupt when loading 1 pattern file after another
• New: After a pattern file is loaded, you can no longer load another one unless you reload your CSV or QBO**.** In some edit cases, I've seen the table data get corrupt when loading 1 pattern file after another
• New: Added a button called "Add names to pattern files"
• New: You can now load an empty patter file into the converter or a file with patterns in it. Then select transactions who's names you want to add to the pattern file to match and then click "Add names to pattern file" in the pattern file will be a list of names of a large number of online reatailers. if it matches any of those names in a transaction, it will truncate the name in the pattern editor.
• Fixed: A bug was introduced in 3.8.5 that if a pattern file only had 2 entries, it would not open, saying that the pattern file was invalid.

v3.8.7 Stable
April 28th, 2024

• Fixed: A bug was introduced in 3.8.5 that if a pattern file only had 2 entries, it would not open, saying that the pattern file was invalid.

v3.8.4
April 23th, 2024

• New: You can now resize the main app.

v3.8.5
April 18th, 2024

• Update: Redesigned the welcome box
• New: Added a tutorial link to the bottom of the welcome box.
• New: Renamed amount1 account1 and amount2 account2 to Split 1 Amount Split 1 Account Split 2 Amount Split 2 Account

v3.8.5
April 17th, 2024

• New: Added the color Red for items in the pattern editor list that have the account name "ignore"
• New: Added a new button that says "What do the colors mean"
• New: Added a box that now tells you what every color means
• Updated: Added a box that now tells you what every color means
• Fixed: It now checks inside every loaded JSON file to see if on the 3rd line of the file it says "Pattern." If it doesn't, it says, "This file is not a valid pattern file". It will no longer let you load random JSON files.

 

v3.8.4
April 17th, 2024

• New: Redesigned the Create, Open, Import, Help, and Cancel menus. I always thought it looked fugly.

v3.8.3
April 15th, 2024

• New: Added "Import" to the first dialog of the pattern editor. This lets you import a tab-delimited file that you can print from your bank / credit card register. (Print / file / tab delimited). When imported, every vendor and account you've used between the dates specified on the print will show up in the app. Every vendor entered into the "What to match field" will have a # put in the back of their name, so they can not automatically match a transaction when the pattern file is loaded. When the txt file is imported, the account specified for that vendor is also automatically filled on the pattern editor. Every account ever used during those dates is also added to autocomplete.
• Fix: Renamed "Save" to "Create"
• Fix: If there is no defaultaccount.txt and you open the app, leave the "Please set a default account" Blank, click cancel, proceed to change the default import account to something else, and click "set," the program will crash.
• Update:  Updated the YouTube URL in the "Tutorial" Button. It now points to the newly released tutorial for 3.8.3

v3.8.2.1
April 15th, 2024

• Fix: If there is no defaultaccount.txt and you open the app, leave the "Please set a default account" Blank, click cancel, proceed to change the default import account to something else, and click "set," the program will crash.

v3.8.2
April 15th, 2024

• New: The first time you open the app, or if you haven't used this version before, it will prompt you to set an account for uncategorized transactions to go into. You are no longer forced to use PremierEcorp Debit Offset. You can specify which account you wish to use.
• New: Added a button on the top of the app called "Set a new Default account," which lets you set a new account to use
• New: The "Default account" at the bottom of the app has been renamed "Default Quickbooks Import Account."
• Fix: When you have an item open in the pattern editor and click add, it now tells you it already exists in the list, and if you are sure you wish to add it

v3.8.1
April 8nd, 2024

• Fix: Splits were duplicated on the debit and account columns when loading a pattern file containing splits matching a transaction—they where not duplicated in the iif export.

v3.8
April 7nd, 2024

• New: Added Transfers. Now when you click on "Convert to IIF" you get a choice between Bank, Credit Card and Transfer.
• New: In the pattern editor you can now check a box called "Transfer" marking a company as always being a transfer.
• New: Colored Transfers orange in the list on the right.
• New: Added a transfer column next to date in the main app. Now when you load a pattern file with an transaction marked as transfer, the transfer column will get a check mark. Upon export, if you choose bank or credit card, that transaction will be exported as a transfer.
• New: Added the buttons "Select all Credit Except Ignore", "Select All Debits Except Ignore" and "Deselect ALL" to the top of the app.
• New: Improved CSV compatibility, including but not limited to Bank of America.
• Fix: You can no longer click the "Load Patterns from file" button without first loading a CSV / QBO File.
• Fix: Renamed the "Choose your Account" to "Choose your Account or Transaction type".

v3.7.1
April 2nd, 2024

• New: Complete rewrite on the CSV import code. The compatibility was pretty much nonexistent. It has now been changed to field mapping. While it won't import 100% of all CSV's the ones I've thrown and it and had friends throw at it have either worked, or didn't work and I had them send me a sample of the file. Because of the new filed mappings, I had compatibility for the csv working in a few mins. After adding support for Various csv formats, I tried to import them into the $ 150-a-year commercial software and it did not know what to do with most of them. While that software does give you the ability to map custom fields and make it compatible mine worked first try. There will be CSV's it will not work with but over time i'll add compatibility for more csv files.
   
• New: If a CSV file is incompatible it brings up a box telling them it's incompatible. It also tells them to contact me, with links to my Reddit and Neowin profile, and to provide me with A sample of csv so I can add support for that financial institution.

[+Warwagon MVC]

OLD POST. I thought I would leave it here so that you can see how fugly the first version of the definition 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