How to convert CSV to QBO

Quickbooks is an accounting software that helps small and medium businesses perform financial operations such as expense tracking, reconciliation, and invoicing. Quickbooks imports transaction data from uploaded bank statements to perform financial operations.

In order to import transaction data, bank statements need to be uploaded in Quickbooks supported formats such as Quicken (QFX), QuickBooks Online (QBO), Quickbook Supported CSV, or Microsoft Money (OFX).

However, if your bank statements are in a format not supported by Quickbooks such as CSV, PDF, or XLSX, you cannot import your transaction data directly.

You need to convert them to Quickbooks supported format so that they can be uploaded into Quickbooks. Hence, in this blog, we’ll look at methods to convert CSV to Quickbooks supported formats.

How to Convert CSV to Quickbooks Supported Format

We will look at methods that will allow us to import transaction data from bank statements in CSV to QBO or Quickbooks supported formats. Let’s look at these two methods:

Manually converting CSV to QBO using Microsoft Excel/Google Sheets

Google Sheets/ Microsoft Excel do not allow converting CSV to QBO format. However, we can easily convert incompatible bank statements in CSV format to Quickbook supported CSV format. Here’s how you can do it:

Open a blank google Spreadsheet by clicking here.
Import your bank statement to google sheet by clicking on File->Import followed by uploading your bank statement.
The bank statement looks like the image below. Remove all additional text apart from the bank transaction table.
Quickbooks supports CSV in only two formats. Hence, we will modify our CSV in one of the formats. Ensure that the column headers have the same name.
a. Three-column format: This has date, description, and amount.
b. Four Column Format: This has date, description, credit, and debit.
Post modification, the sheet will look like this. We have removed additional columns and have named the headers in accordance with the QBO format.
Modify the date column to ensure that it is in dd/mm/yyyy format. Select all the dates and click on More Formats (123 icon) -> Date dd/mm/yyyy format.
NOTE: If you do not see this option explicitly then click on More Formats (123 icon) -> Custom Date and Time. A window will open. Specify the date format as dd/mm/yyyy and click on Apply.
The bank statement is now correctly formatted. Download it as CSV and upload on the Quickbooks platform
If you see an error, ensure that you:
1. Remove any zeros (0) from the file. Leave those cells blank.
2. Correct any transactions that display numbers in the Description column
3. Make sure the dates are in one format.
4. Some banks add the day of the week in the Date column (for example, 20/11/2018 TUE). You need to remove the day of the week.

Converting CSV to QBO with Nanonets

The above method is suitable for one-time use. If you need to upload files daily to Quickbooks, then it is better to automate conversion of CSV to QBO with Nanonets. Nanonets can seamlessly extract data from bank statements and export it to Quickbooks.

Here’s how to convert a bank statement (Quickbooks Online/Desktop) using Nanonets.

Sign up for free on app.nanonets.com. No credit card is required.
Click on New Workflow->Bank Statement. Your model will be created. Click on Upload to upload your bank statement (in PDF or XLSX). You can also set up auto-forwarding via email to automatically upload your bank statements to Nanonets.
Nanonets’ powerful OCR extracts the data from bank statements.
Click on Back->Workflow->Final Results to select the fields to export to Quickbooks.
Select Date, Description, Debit, and Credit fields from the Final Results.
Go to the Export section and click on Browse all export options. Select Quickbooks from the window.
Name your integration and click on Configure in Workflow.
Sign in to your Quickbooks account and follow the instructions to setup the Quickbooks integration.
That’s it! You can now automatically export bank statements to Quickbooks without any hassle.