CSV to QBO: format a bank CSV for QuickBooks Online

CSV, QBO, or OFX for QuickBooks Online

QBO means two things in searches: QuickBooks Online itself, and the .qbo Web Connect file that banks can provide for QuickBooks. For QuickBooks Online, Intuit lists CSV, TXT, QBO, and OFX as accepted upload files, so a correctly laid-out CSV is enough.

Fulla Ledger exports CSV and OFX, not .qbo files. If the CSV layout keeps causing trouble, the OFX export is an alternative: it is a structured OFX 1.0.2 file with dates, signed amounts, and a stable transaction ID for each approved row, so there are no columns to map. The OFX vs QFX vs QBO vs CSV guide explains the formats in more detail.

This guide covers QuickBooks Online. QuickBooks Desktop has different import tools, and Fulla does not claim a Desktop import path.

The CSV rules Intuit lists

Intuit’s help articles for uploading transactions to QuickBooks Online set out the requirements below. Each bank formats CSV files differently, which is why a bank download can fail even though it is a CSV.

• 3 columns: Date, Description, Amount, with payments as negative amounts
• 4 columns: Date, Description, Credit, Debit, with money in and money out in separate columns
• Leave any Amount, Credit, or Debit cell that only contains zero blank
• Use one date format for every row; Intuit recommends dd/mm/yyyy
• If the date column includes the day of the week, split it into its own column
• Remove the word amount from the Credit and Debit headers
• Remove numbers from cells in the Description column; check reference or check numbers in descriptions if an upload fails
• The file must be in English and 350 KB or less, with up to 1,000 lines per upload
• On a Mac, save the file as a Windows CSV

Mapping a Fulla CSV to QuickBooks columns

Fulla’s CSV contains more columns than QuickBooks Online needs, because it is also an audit file. For the import copy, keep date and description, then choose one of the two amount layouts.

• 3-column layout: keep date, description, and amount; amount is already negative for money out
• 4-column layout: keep date, description, credit, and debit; both hold positive numbers with the unused side blank, which matches Intuit’s blank-cell rule
• Delete statement_number, balance, currency, source_page, and raw_fields from the import copy
• Dates are written year-month-day; if you resave the file in a spreadsheet, check that every date still uses one format and pick that format during mapping
• Descriptions that begin with =, +, minus, or @ start with an apostrophe in the CSV to stop spreadsheet formulas; remove it in the import copy if you do not want it in QuickBooks
• Remove any row whose amount is zero, or leave its amount cells blank

Credit card accounts and reversed signs

Intuit notes that CSV files for credit card accounts may show amounts in the opposite direction from what you expect: a payment to the card can appear as negative because it reduces the balance. Upload card transactions into the credit card account in QuickBooks, not a bank account, so they are recorded correctly.

Fulla writes card charges as negative amounts, the same direction as money leaving a bank account. After mapping, QuickBooks shows a preview of the amounts; if every sign is the wrong way round, use its option to reverse all values before importing.

Avoiding duplicates

Before uploading, check which dates the QuickBooks account already covers, whether from an earlier upload or a bank connection, and export only the rows after that point. QuickBooks lets you select which transactions to import, so you can leave out rows that are already there. When you split a long file into parts under 1,000 lines, cut on a date boundary so no transaction appears in two files.

Imported transactions appear on the Bank transactions page. Match or categorize them there, then reconcile the account against the statement. The How to reconcile a bank statement guide covers that last step.