This week I'm sharing some of my favourite utilities. On Monday I shared my love of ChronoMate. Today we're going to talk about a little app called CSV2QBO.
This is definitely one of those apps that does one thing well. This is about as niche as you can get.
What is it?
CSV2QBO is a file conversion application from Propersoft. It runs on Mac and Windows, and it's sole purpose is to convert csv files to qbo files. I'll explain these formats in a minute. Going against my frugal nature...this isn't a free app. It's actually $47, but it has saved me so much time it's easily paid for itself a few times over.
Why?
If the title didn't immediately click for you, let's break this down a little.
One of the biggest frustrations is trying to import data into QuickBooks. Importing lists is fairly straightforward, but what if I want to bring in transactions? You can always setup your bank accounts to download transactions, but what if you need to import really old transactions? Or, in my case, maybe you've already got the data in another app like Xero or FreshBooks. If only there was a way to turn these transactions into something QB thought was a bank download...
QuickBooks brings in bank transactions with files that have a qbo extension. They're similar to a spreadsheet but specifically formatted for QB. If you can download transactions directly from your bank online then you can probably get them in this qbo format...which makes this utility pointless.
Importing from Another App.
Here's a real-life example I've had to deal with recently. I had customer data in Xero. We are currently making a switch from Xero back to QB and I needed to bring in the full fiscal year. The earlier months of the fiscal year are no longer available for download from the bank, but I have all the details I need in Xero. I generate an "Account Transactions" report in Xero and choose the first bank account on my list. I select the proper dates, and choose to export this as an Excel file.
Next I open the file in Excel, do a little formatting to clean it up, and save it as a .csv. A .csv, or comma-separated values file has all the data in a regular Excel file without all the extra formatting Office software likes to add.
This same thing would work in another app. In something like FreshBooks it would be a bit different. It would be 2 reports...Payments Collected and Expense Report. The expense report has a column for "Bank" so it would be easy enough to separate out each bank account in order to create the .qbo file in the next step.
Now it's time to open this file in CSV2QBO.
This screenshot is from the Windows version. I've been using this since before the move to Mac. It's one of the very few Windows apps I still run. As you can see, it's a very plain looking Windows app. You navigate to the csv file you want to convert and open it in CSV2QBO. Review the imported data to make sure the columns are mapped out properly and click Save. That's it. You now have a qbo file *just as though you had downloaded it from your bank.
- Ok, not quite. When you download, let's say, January 2013's transactions from your bank, the qbo file will include details of the bank and account number. This will be important soon.
Now it's time to bring the data into QuickBooks.
As you see from the screenshot, you're going to want to manually import this qbo or "web connect file". Select the qbo file you just created, and *follow through the instructions. Once you're done you will have all of these transactions imported and ready for you to categorize.
*There are a few weird steps you need to take in order to get this done properly which probably aren't interesting unless you're actually trying to pull this off. Please email me if you plan to do this and I can walk you through the full process.
If you aren't a bookkeeper, or you don't deal with QB this app might not make any sense. If, however, you do work in QuickBooks and want to be able to import transactions...this little app is a huge time saver. It's not much to look at and it's a little pricey for a one-trick pony, but the time you'll save will pay for the app in no time.
If you have a better way to do this, please let us all know in the comments.
