Split CSV for Xero: Working Around the 100,000 Row Limit

💡 Quick Answer

Xero has a 100,000 row hard cap per bank statement import but no file size limit — the opposite of QuickBooks Online, which limits by file size (~350 KB) rather than row count. A 250,000-row transaction export requires splitting into three chunks of no more than 100,000 rows each. The split strategy matters: splitting by date range (not arbitrary row count) preserves transaction continuity. Import sequence also matters — importing chunks out of date order creates gaps in Xero's reconciliation view where the account balance timeline is temporarily inconsistent. Use SplitForge CSV Splitter to split by date range, then import chunks in chronological order.

For broader Xero CSV import troubleshooting — date format, encoding, column headers — see Fix Xero CSV import errors. For generic CSV splitting across any platform, see How to split large CSV files without Excel. This guide covers the specific Xero workflow: splitting a large transaction export into Xero-compatible 100,000-row chunks while preserving import order and date continuity.

---

Fast Fix

1. Check your CSV row count. If under 100,000 rows, no split is needed — proceed to import.
2. Open SplitForge CSV Splitter and upload your CSV.
3. Choose "Split by date range" as the split method. Select the date column.
4. Set the split interval to produce chunks under 100,000 rows. For a year-long transaction file with 250,000 rows, quarterly splits produce ~62,500 rows each.
5. Download the split files. Verify each file has a header row — SplitForge CSV Splitter preserves the header in each output file automatically.
6. Import into Xero in chronological order — earliest date range first, latest last.

---

TL;DR:

• Xero's limit: 100,000 rows per import file (no file size limit)
• Split by date range, not arbitrary row count, to preserve transaction continuity
• Import chunks in chronological order — out-of-order imports create reconciliation gaps

---

Methodology: Verified against Xero bank statement import documentation. Row limit and import ordering behavior confirmed via Xero Central documentation and Xero community reports. May 2026.

---

The Constraint That Surprises Xero Users

A firm migrates from QuickBooks to Xero mid-year with 18 months of bank transaction history — 340,000 rows. The file imports cleanly in QuickBooks. In Xero, the first upload attempt returns "The import file contains more than 100,000 rows." The firm has dealt with QuickBooks' ~350 KB file size limit before and knows to split large files — but Xero's row cap, not file size, is the constraint. A 340,000-row file at 150 bytes per row is only 51 MB — well within any file size limit — but three times over Xero's row cap.

The second surprise: after splitting into four 85,000-row files, the firm imports Q4 first (the most recent period), then Q3, Q2, Q1. Xero's reconciliation view shows the Q4 transactions but displays a "pending reconciliation" state for Q1-Q3. The account balance history starts from Q4 and appears to have no prior transactions until Q1-Q3 imports complete — at which point Xero retroactively rebuilds the timeline. The interim state produces incorrect running balance calculations in any report generated during the multi-file import process.

---

Common Error Messages

---

Cause and Fix Summary

---

Contents

• Xero's Row Limit vs QuickBooks' File Size Limit
• Split Strategy: Date Range vs Row Count
• Header Preservation Across Split Files
• Import Order and Reconciliation Continuity
• Step-by-Step Split and Import Workflow

---

Xero's Row Limit vs QuickBooks' File Size Limit

Xero and QuickBooks Online apply different constraints to bank statement CSV imports. Understanding which constraint applies determines the correct split strategy.

For a 250,000-row bank statement export:

• In Xero: three files of ~83,333 rows each stay under the 100,000 row cap. File size is not relevant.
• In QuickBooks Online: four quarterly files of ~62,500 rows each, sized to stay under 350 KB. Row count is not relevant.

For multi-platform workflows — migrating from QuickBooks to Xero or maintaining accounts across both — use date-range splitting to produce chunks that work within both platforms' constraints simultaneously. A quarterly split produces chunks that satisfy Xero's row limit AND stay under QuickBooks' file size limit for most typical transaction volumes.

For per-platform formatting requirements beyond row limits, see Bank Statement CSV Formatting Guide for QuickBooks, Xero, and Wave.

---

Split Strategy: Date Range vs Row Count

Xero accepts splits by either date range or arbitrary row count. Date-range splitting is preferable for bank statement imports.

Why date-range splitting is better for Xero:

1. Transaction continuity. Splitting by row count may cut mid-month, creating two chunks where the boundary date appears in both (overlap) or neither (gap). Date-range splits on month or quarter boundaries align with natural accounting periods.

2. Reconciliation period alignment. Xero's reconciliation view is organised by calendar period. Imports aligned to calendar quarters match Xero's natural statement periods.

3. Audit trail. Date-range split files are self-describing — a file labelled Q1-2026 is immediately identifiable without counting rows.

Choosing the split interval:

For business accounts with irregular transaction volume (high December, low August), verify each chunk's row count after splitting. A quarterly split where Q4 exceeds 100,000 rows due to seasonal volume requires Q4 to be split again by month.

SplitForge CSV Splitter splits by date column value — enter the date range for each chunk and the tool produces the correctly bounded output files. Processing runs in your browser; the transaction data is never uploaded to a server.

---

Header Preservation Across Split Files

Xero requires the header row in each import file. When splitting by row count manually (e.g., in Excel), the header row appears only in the first chunk — subsequent chunks start at row 2 of the original data and have no header. Xero rejects headerless files with "Invalid column header."

SplitForge CSV Splitter includes the header row in each output file automatically. For manual splits, copy the header row from the first chunk and paste it as row 1 of each subsequent chunk.

Required Xero column headers:

Xero requires Date and Amount at minimum. Payee and Description improve reconciliation matching quality. Reference is optional but helps identify transactions in the reconciliation view.

For pre-import column validation, see Accounting CSV pre-import checklist: 12 checks before every upload.

---

Import Order and Reconciliation Continuity

Xero builds the account's transaction timeline as files are imported. Importing chunks out of date order creates a temporary reconciliation gap where the account register shows missing periods until all chunks are imported.

The correct import sequence:

1. Import the earliest date range first (e.g., Q1 January–March).
2. Import Q2 after Q1 completes.
3. Continue in chronological order through Q4.

What happens with out-of-order imports:

If Q3 (July–September) is imported before Q1 (January–March):

• Xero shows Q3 transactions as the earliest transactions on record
• The opening balance for Q3 is calculated without Q1 and Q2 transactions — producing an incorrect running balance
• Reports generated during the import process show incorrect period-end balances
• After Q1 and Q2 import, Xero recalculates balances retroactively, but reports generated during the interim state may be incorrect

Recovery from out-of-order import:

If chunks were imported out of order and the account balance is incorrect:

1. Delete the imported statement lines from the Xero bank reconciliation screen.
2. Re-import all chunks in chronological order.
3. Verify the opening and closing balance for each period after import.

Xero's "Delete" option for imported statement lines is in the bank account settings under "Statement Lines." Deleting imported lines does not delete reconciled transactions — only the unreconciled import data is removed.

For the year-end bank statement consolidation workflow that produces the file requiring splitting, see Merge monthly bank statement CSVs for year-end import.

---

Step-by-Step Split and Import Workflow

Step 1 — Check row count

Open your bank statement CSV and note the row count (excluding the header). If under 100,000 rows, skip to the import step.

Step 2 — Choose your split interval

Based on the total row count and transaction distribution, choose a split interval. Quarterly is the most common choice for year-long bank exports up to 400,000 rows.

Step 3 — Split by date range

Open SplitForge CSV Splitter and upload your CSV. Select "Split by date range." Enter the date range for each chunk — for a quarterly split on a 2026 full-year file: Q1 = 2026-01-01 to 2026-03-31, Q2 = 2026-04-01 to 2026-06-30, and so on. Download each output file.

Step 4 — Verify each chunk

For each split file: confirm the row count is under 100,000, confirm the header row is present, and confirm the date range matches the expected period. Spot-check the first and last rows for boundary correctness.

Step 5 — Import in chronological order

In Xero, navigate to the bank account and open the import wizard. Import Q1 first. After Q1 completes successfully, import Q2. Continue through Q4. Do not start the next chunk until the previous import confirms success.

Step 6 — Verify account balance

After the final chunk imports, compare the account's closing balance in Xero to the bank statement's closing balance. A discrepancy indicates a gap or overlap in the split date ranges. Investigate the boundary dates of adjacent chunks.

---

Additional Resources

Official Xero Documentation:

• Import a bank statement in Xero — Xero Central — Row limit, accepted file formats, and column requirements

Related Guides:

• Fix Xero CSV import errors — Date format, encoding, and column header errors in Xero CSV imports
• How to split large CSV files without Excel — Generic CSV splitting for files not destined for Xero
• Merge monthly bank statement CSVs for year-end import — Year-end consolidation workflow that produces the large file requiring splitting
• Bank Statement CSV Formatting Guide for QuickBooks, Xero, and Wave — Per-platform formatting and import requirements
• Finance CSV data prep complete guide — Full accounting CSV preparation workflow

---

FAQ