Business

Tidy Your Bank Export Before a QuickBooks or Zoho Books Import

Clean up a tradie's or contractor's bank CSV so a QuickBooks or Zoho Books import works first time, with A$ examples and a fix-before-import list.

Editorial illustration for Tidy Your Bank Export Before a QuickBooks or Zoho Books Import

Importing a raw bank CSV straight into QuickBooks or Zoho Books often produces duplicates, wrong signs or rejected rows, so tidy it first. If you work on jobs and pay for fuel, materials and tools from one account, this is the cleaning routine that gets a file ready for the import screen.

What does a raw bank file usually get wrong?

  • Extra header or footer lines that are not transactions
  • Dates written day first while the import expects another order
  • Amounts with currency symbols, commas or trailing spaces, which turn into text
  • Debits shown as positive numbers in a separate column
  • Description fields stuffed with long reference strings

How do you fix the signs and dates?

Say the bank gives Debit in column C and Credit in column D. For a net amount, put this formula in E2 and fill down: =D2-C2. A fuel purchase of A$86.40 in C becomes -86.40 and a customer payment of A$1,250.00 in D becomes 1250.00. For dates, check one row you know. If a payment you made on 3 April shows as 04/03, your sheet is reading month first, and you must correct that before saving.

FixQuick testResult if skipped
Remove non-transaction linesFirst and last rows are real paymentsImport error on row 1
Convert to one signed columnSum equals statement movementSpending shows as income
Standardise datesCheck a known payment dateRows land in the wrong month
Shorten descriptionsPayee readable in one glanceHard to categorise

What is a worked reconciliation?

Suppose your week has these rows: fuel -86.40, timber -312.00, a customer payment +1,250.00 and a tool hire -140.00. The total is 1,250.00 - 86.40 - 312.00 - 140.00 = 711.60. If the bank says your balance rose by A$711.60 across those dates, the file is complete.

Which categories should a contractor keep simple?

Fewer is better: materials, fuel and vehicle, tools and hire, subcontractors, phone and software, and income. Ask your bookkeeper for their list and use it word for word. A custom label such as hardware store run is useful to you but means nothing to their chart of accounts.

Could an app keep this tidy for you?

Rather than rebuilding the same fixes monthly, a tool that remembers your corrections helps. SpendixAI is built around India and INR and is not aimed at Australian dollar accounts, but it flags possible duplicate imports and offers Zoho Books and QuickBooks export templates, which is the kind of feature to look for.

How do you test an import before committing to it?

Make a small test file from the first five rows and import only that. Look at how dates, amounts and descriptions landed, and whether the signs are right. A fuel purchase should appear as money out and a customer payment as money in. If everything matches, import the rest. If not, you have lost two minutes rather than untangled a month of wrong entries. Many bookkeeping tools also let you undo an import, but checking your own settings and your bookkeeper's advice beforehand is far less stressful.

How often should you export?

Monthly suits most people, but a busy contractor may prefer fortnightly so the file stays short and the memory of each job stays fresh. Whichever rhythm you pick, make the date ranges exact and non-overlapping: the 1st to the 14th, then the 15th to the end of the month. Overlaps are the usual source of duplicates, and an exact range makes the row count easy to check against the statement.

Before you import

  1. Back up the raw file.
  2. Clean lines, signs, dates and descriptions.
  3. Add categories.
  4. Check the total.
  5. Import a few rows first to test, then the full file.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports