A complete technical reference for the custom multi-line Spend Money CSV importer. Covers the problem, architecture, sign correction logic, CSV format spec, and Xero API integration details.
Businesses migrating from QuickBooks Desktop or Online to Xero face a critical data migration bottleneck: expense and bank transactions have no native bulk import path into Xero's accounting engine.
QuickBooks allows exporting all transaction registers—including expense payments, cheque transactions, and journal entries—as structured CSV files.
Xero's CSV import is limited to bank statement feeds (OFX/CSV bank imports), which only create unreconciled bank lines—not posted accounting entries with GL codes.
Companies with hundreds of monthly transactions across employees, vendors, and statutory payments (EPF, SOCSO, EIS, Zakat, PCB) cannot manually re-enter each transaction one by one.
A single payment in QuickBooks may have multiple line items split across different GL accounts. Xero's Spend Money supports this natively via the API but not via any import tool.
Xero's native import wizard only creates bank statement lines (unreconciled). These are not equivalent to Spend Money or Receive Money transactions—they do not post to the GL and cannot have multiple account splits. There is no official Xero tool for bulk-importing posted bank transactions with line items.
A custom web application that accepts a pre-processed transactions CSV and a Xero Chart of Accounts CSV, then intelligently posts all transactions directly to Xero via the API as fully-posted Spend Money or Receive Money bank transactions.
Export the relevant transaction register (e.g. cheque register, expense report) from QuickBooks as CSV. The export includes date, payee name, account code, amount, and memo per line item.
✓ QuickBooks built-in featureMap the QB export columns to the importer's expected CSV format: Date, ContactName, Description, AccountCode, Amount, TaxType. Multi-line transactions naturally share the same Date + ContactName.
Export the Xero Chart of Accounts as CSV (Accounting → Chart of Accounts → Export). The importer uses this to identify which accounts are liabilities vs assets/expenses, driving the sign-correction logic.
↓ Xero COA exportThe app groups rows by Date + ContactName into transaction objects. For each group, it applies sign correction on liability accounts, calculates the net amount, and determines whether to post as SPEND or RECEIVE.
Each transaction group is sent as a single POST /BankTransactions request with all line items. The app handles rate limiting with progressive back-off to stay within Xero's 60 requests/minute cap.
This mirrors how QuickBooks organises transactions. A single payment to EPF on 15 March includes both the employee contribution and the employer contribution as separate line items under one payment reference. Grouping by date + contact recreates this structure as a single multi-line Spend Money in Xero, matching the actual bank statement entry.
This is the most nuanced part of the migration. QuickBooks records liability account movements as negative amounts (credits), but Xero's BankTransaction model requires all line item amounts to be positive—the transaction type (SPEND vs RECEIVE) determines the direction of cash flow.
| Condition | Account Type | Sign Correction | Net Calculation | Posted As |
|---|---|---|---|---|
| All asset/expense accounts, net positive | Asset / Expense | None | Sum of amounts | SPEND |
| Liability accounts present | Liability | Negate amount (×−1) | Sum after flip | SPEND if net > 0 |
| Net amount is negative after correction | Mixed | Applied | abs(net) | RECEIVE |
| Refund or credit note scenario | Any | — | Negative input | RECEIVE |
// coaMap: { accountCode → accountType } — built from uploaded COA CSV function buildTransaction(group, coaMap) { const lineItems = group.rows.map(row => { let amount = parseFloat(row.Amount); const acType = coaMap[row.AccountCode] ?? 'ASSET'; // Liability accounts: flip sign before net calculation if (acType === 'LIABILITY') amount = -amount; return { AccountCode: row.AccountCode, Description: row.Description, UnitAmount: Math.abs(amount), // always positive in Xero LineAmount: Math.abs(amount), TaxType: row.TaxType ?? 'NONE', _signedAmount: amount, // retained for net calc only }; }); const net = lineItems.reduce((s, l) => s + l._signedAmount, 0); return { Type: net >= 0 ? 'SPEND' : 'RECEIVE', Contact: { Name: group.contactName }, Date: group.date, BankAccount: { Code: '091' }, // bank account code from Xero LineItems: lineItems, Total: Math.abs(net).toFixed(2), Status: 'AUTHORISED', }; }
When you record paying EPF, QuickBooks debits EPF Payable (reducing the liability) and credits the Bank. The EPF Payable line appears as a negative amount in the register because it is a debit to a liability—which decreases the balance.
Xero's Spend Money model does not use debits and credits directly. Instead, all line items must have positive amounts, and the transaction type (SPEND) determines that cash flows out. The bank account is credited automatically.
Flipping the sign on liability line items converts them from "debit to liability" (negative in QB) to "amount paid against liability" (positive in Xero), producing the correct total cash outflow for the transaction.
The POST /BankTransactions endpoint accepts an array of line items in a single call—exactly mirroring what you would do manually in the UI when splitting a payment across accounts. This is the only way to create posted GL entries, not just bank statement lines.
QuickBooks organises multi-account payments under a single cheque or payment reference. Grouping by Date + ContactName faithfully reconstructs this structure in Xero without requiring the user to manually define transaction boundaries.
Rather than requiring the user to manually tag every row as asset or liability, the app ingests the Xero COA export and builds a lookup map automatically. This eliminates a major source of user error during high-volume imports.
Xero enforces 60 API calls per minute. The importer queues transactions and applies exponential back-off on 429 responses, allowing unattended bulk imports of hundreds of transactions without manual intervention or import failures.
Using the API entirely bypasses Xero's UI bottleneck. A migration that would take days of manual data entry can be completed in minutes, with full audit trail and AUTHORISED status on all transactions from the moment of import.
The transactions CSV must conform to the following column specification. Rows sharing the same Date and ContactName are automatically grouped into a single transaction.
The highlighted cells (Date + ContactName) form the grouping key. Rows 1–2 become one Spend Money transaction to KWSP / EPF with two line items. Rows 3–4 become a separate transaction to PERKESO. Both are posted on the same date but as distinct bank transaction records in Xero.
| Column | Type | Required | Notes |
|---|---|---|---|
| Date | YYYY-MM-DD | Yes | Transaction date. Part of grouping key. |
| ContactName | String | Yes | Payee / contact name. Part of grouping key. Must match or will create new Contact in Xero. |
| Description | String | Recommended | Line item description. Appears in transaction detail view in Xero. |
| AccountCode | String | Yes | Xero chart of accounts code. Must exist in the connected Xero org. |
| Amount | Decimal | Yes | Signed amount from QB. Negative = liability debit or credit. Sign correction applied automatically. |
| TaxType | Xero Tax Code | Optional | Defaults to NONE if omitted. Use Xero tax type codes (e.g. INPUT2, OUTPUT). |
Xero enforces a hard limit of 60 API requests per minute per org. For large imports, the application queues transactions and applies progressive back-off to ensure all records are posted without triggering hard failures.
async function postWithRetry(transaction, maxRetries = 5) { let delay = 1000; // start at 1 second for (let attempt = 0; attempt < maxRetries; attempt++) { try { const res = await xeroPost('/BankTransactions', transaction); return res; // success — exit immediately } catch (err) { if (err.status === 429) { // Rate limited — wait and retry with exponential back-off const retryAfter = err.headers['retry-after'] ?? delay / 1000; await sleep(retryAfter * 1000); delay = Math.min(delay * 2, 30000); // cap at 30s } else { throw err; // non-rate-limit error — surface immediately } } } throw new Error(`Failed after ${maxRetries} attempts`); } // Sequential queue with inter-request delay to stay under 60 req/min async function importAll(transactions) { for (const tx of transactions) { await postWithRetry(tx); await sleep(1100); // ~54 req/min safe ceiling } }
| Xero Limit | Value | Importer Behaviour |
|---|---|---|
| Requests per minute | 60 | Inter-request delay of 1.1s → ~54 req/min effective rate |
| Requests per day | 5,000 | Logged per session; warning shown at 4,500 |
| Concurrent connections | 1 | Sequential queue only — no parallel API calls |
| Max line items per transaction | No hard limit | Groups with >50 lines split into sub-transactions automatically |
| HTTP 429 retry behaviour | Respects Retry-After header | Falls back to exponential delay (1s → 2s → 4s … → 30s cap) |