Sign in Talk to us Free trial
Free trial

Design and software studio
Colour

QuickBooks exports (IIF and Online CSV)

Understand what the QuickBooks Desktop IIF file and the QuickBooks Online CSV files contain, how names are cleaned and how to import them.

For
AccountantAdministrator
Hosting
Any hosting
Devices
All devices
Business
All businesses

Checked against Pop Street Dispatch 0.1.0

Dispatch builds files your bookkeeper can import into QuickBooks. You will be able to tell which file suits your QuickBooks, what each holds, and how to avoid import problems.

Before you start #

  • The server builds both file types on request (see the technical details). The console has no export screen yet, so a bookkeeper or an integration makes the call.
  • Pick a date range. Invoices and bill dates are filtered by the range. The customer list always holds every customer.
  • Check that the account names in QuickBooks match the names Dispatch uses for the bank, card and cash accounts. Dispatch keeps three settings for them, and a blank entry keeps the previous name.
  • In QuickBooks Online, each item named in an invoice must exist already, with the income account names Dispatch uses.

What an export leaves out #

Void invoices, payments on void invoices and void bills never appear in the files.

The server holds no chart of accounts, vendors, bills or business expenses yet. Its files carry the default chart of accounts, and the Online bills and expenses files hold their column headers only.

QuickBooks Desktop: the IIF file #

One tab-separated file with Windows line endings. Its sections:

  1. Accounts, with each account's QuickBooks type.
  2. Customers, with address, phone and email.
  3. Vendors.
  4. Transactions. Each transaction has a TRNS row and SPL rows with the columns TRNSTYPE, DATE, ACCNT, NAME, AMOUNT, DOCNUM and MEMO. Positive amounts are debits and negative amounts are credits. Every transaction sums to zero.

The transactions are the non-void invoices issued in the range, payments on them, non-void bills with their payments, and expenses.

QuickBooks Online: the CSV files #

Five files:

  • Customers: Display Name, Company, Email, Phone, Billing Address Line 1, City, State, Zip.
  • Invoices: Invoice Number, Customer, Invoice Date, Due Date, Terms, Location, Memo, Item, Item Description, Item Quantity, Item Rate, Item Amount, Item Tax Code, Item Tax Amount. One row per line, plus a Sales tax row for a taxed invoice. A file holds at most 1,000 data rows, an invoice stays whole in one file, and large ranges give several invoice files.
  • Payments: Customer, Payment Date, Method, Reference, Deposit To Account, Invoice Number, Amount.
  • Bills: Bill No, Vendor, Bill Date, Due Date, Terms, Location, Memo, Account, Line Amount, Line Description.
  • Expenses: Date, Payee, Payment Account, Category, Description, Amount, Reference.

Dates read MM/DD/YYYY, in your business time zone, whatever date format your screens use. A QuickBooks file follows QuickBooks, not your locale.

How names are cleaned #

  • Tabs and line breaks become spaces, double quotes become apostrophes, and runs of spaces collapse.
  • A list name uses at most 41 characters, and a colon becomes a dash, because QuickBooks reads a colon as a sub-account.
  • A customer is named by company when one is set, otherwise by name. A blank name becomes "Customer" and the id.
  • Two customers with the same name (any capitals) get a hash and the id added to the second one, so nothing merges by mistake.

Steps #

  1. Choose the date range and the file type.
  2. Download the files.
  3. In QuickBooks, import customers first, then invoices, then payments, then bills and expenses.
  4. Compare the QuickBooks totals with the Dispatch invoice list for the same range.
Technical details
  • POST /export/iif and POST /export/quickbooks-online take from and to as dates written YYYY-MM-DD in the business time zone, both days included. They need the Money group (owner, manager and advisor). A to before from answers validation_failed (422) with fields: ["to"], and a date in another form answers invalid_date (422).
  • The IIF answer holds file_name (quickbooks-<from>-<to>.iif), content_type, content and counts (transactions, invoices, payments, customers). The Online answer holds files, a list of file_name, content_type and content, and counts for customers, invoices, payments, bills and expenses.
  • Both routes sit under /export, so they still work when the licence is read only, and each export writes an audit entry.
  • The Online invoice header spellings and the IIF DOCNUM and MEMO columns are checked against Intuit's import kit and sample CSV before release.
  • Cash-basis revenue carries fractions of a cent per account and rounds once at the end. Per-invoice fields round on their own, so a total can differ by up to 2 cents.
  • The IIF transactions store their first line as the negative sum of the splits when they are built, and a later edit is caught by the journal check.

If something goes wrong #

  • QuickBooks rejects an item: add the item in QuickBooks with the same name and income account.
  • A customer appears twice: two records share a name. The second has the id added, so merge them in Dispatch.
  • Totals differ by a cent or two: see the rounding note above.