Import CSV & Excel Into Expense Books
On this page5 sections
Switching away from clunky legacy trackers or messy annual spreadsheets shouldn’t require abandoning two years of categorized purchase history. Receipt Scanner’s browser-side CSV and .xlsx bulk importer lets you map historical tables directly into isolated local Books up to 1,000 rows per batch without touching external servers.
Key takeaways
- Row & byte limits: Import up to 1,000 rows (max 10 MB, 50 columns) per CSV, TXT, or modern
.xlsxupload directly in memory.- Clean template starter: Start with headers aligned to our canonical schema so
date,merchant,total,currency,taxId, andnotessnap cleanly into place.- Protect against corrupted signs & totals: Strip footer
=SUMrows and check date-order defaults (DMYvsMDY) before clicking confirm import.
Preparing CSV and Excel files before importing locally
Clean row-1 headers make imports painless regardless of whether spreadsheets originated in Microsoft Excel, Apple Numbers, or Google Sheets. Export raw transaction rows strictly as modern OpenXML .xlsx or comma-separated .csv conforming to RFC 4180 CSV specifications, reserving Sheet 1 solely for line transactions without decorative banner logos above Row 1.
Download pre-aligned starter headers straight from Receipt Scanner Excel and CSV templates whenever migrating legacy workbooks from scratch. Keeping column names standardized ensures smooth round-trip compatibility later.
| Column Requirement | Canonical Header Names Accepted | Friendly Aliases Auto-Mapped | Validation Rule |
|---|---|---|---|
| Mandatory Transaction Keys | date, merchant, total |
txn date, vendor, payee, amount |
Future dates (> +1 day) or blank totals flagged |
| Optional Tax & Currency | currency, subtotal, tax, tip, taxId, invoiceNumber |
ccy, gst, vat, gstin, vatid, bill no |
Valid ISO currency codes default if omitted |
| Categorization & Context | category, paymentMethod, time, notes |
cat, type, payment mode, memo |
Unknown labels fall back cleanly to other |
Header synonyms and data-cleaning rules
Our browser importer normalizes row-1 column headers by stripping UTF-8 BOM marks, converting underscores and slashes to spaces, and matching aliases case-insensitively. Review this cleaning reference whenever migrating downloads from regional banks, accounting software, or European spreadsheets.
| Field or Spreadsheet Quirk | Accepted Header Synonyms | Problematic Source Format | Clean Import-Ready Value |
|---|---|---|---|
Date (date) |
Date, Dated, Transaction Date, Txn Date, Bill Date |
Excel serial 45980 or ambiguous 04/11/2026 |
ISO 2025-11-19 or 2026-11-04 |
Merchant (merchant) |
Merchant, Payee, Vendor, Store, Description |
Blank payee or =Store formula prefix |
Plain vendor text up to 200 chars |
Total (total) |
Total, Amount, Amount Paid, Sum, Value, Price |
Negative -42.50, (42.50), or split Debit/Credit |
Positive expense number 42.50 |
Tax (tax) |
Tax, GST, VAT |
Combined note text 18% (9.00) |
Clean numeric tax amount 9.00 |
Currency (currency) |
Currency, Ccy, Curr |
Currency symbol $ instead of code |
3-letter ISO code (USD, EUR, INR) |
Category (category) |
Category, Cat, Type |
Unrecognized custom department code | Standard label (Groceries, Travel, Fuel) |
Invoice (invoiceNumber) |
Invoice Number, Invoice, Bill No, Bill Number |
Excel scientific notation 1.23E+16 |
Text-formatted invoice ID string |
Why do spreadsheets break or swap dates during bulk import?
Three tiny Excel formatting traps cause ninety percent of bulk import errors: hidden summary rows, mixed slash dates (04/11/2026), and leading formula characters left inside merchant names. Always strip bottom total footer lines and formatted currency strings containing weird notes before saving.
If your source table was pulled from third-party exports, ensure leading apostrophes or quotes protect merchant strings cleanly against spreadsheet formula evaluation rules explained by OWASP CSV Injection guidance.
How do you fix negative bank debits and credit columns?
Bank statement CSVs commonly export card purchases as negative numbers (-42.50 or (42.50)) or split outflows and repayments into separate Debit and Credit columns. Because expense ledgers only record spending above zero, negative outflows and zero-value rows fail validation with a total error.
Create a helper total column in Excel or Google Sheets using =ABS(B2) on outflow rows, filter out positive credit card repayments or incoming transfers, and paste values before exporting. Removing non-expense repayments keeps category totals and budget burn bars accurate.
Handling Excel serial dates and European comma decimals
Raw spreadsheet cells sometimes store dates as integer serials such as 45980 rather than formatted strings, or format currency totals using European decimal commas like 1.234,56. Our importer converts numeric Excel serial dates between 20000 and 80000 against the 1899 epoch automatically, turning 45980 cleanly into 2025-11-19.
For mixed separators like 1.234,56 or 1,234.56, the parser detects whichever separator appears last as the decimal point and resolves 1.234,56 to 1234.56 and 42,50 to 42.50. Note that a single separator followed by three digits (such as 1.234 in USD or EUR) parses as a thousands grouping separator (1234), so always verify three-decimal rows or format amounts with standard decimals beforehand.
Notice also how ambiguous numeric dates parse: when the first token exceeds twelve (28/03/2026), Receipt Scanner locks onto day-first interpretation automatically, whereas ambiguous values (04/11/2026) obey the date-order toggle you choose prior to parsing. Converting numeric dates to unmistakable ISO YYYY-MM-DD strings in Excel eliminates month-day swaps altogether.
Step-by-step bulk import workflow in My expenses
Before loading historical files, verify which active Book should receive the imported transactions—such as creating a clean historical year Book or dedicated business folder inside My expenses.
- Select the target Book and choose Import (
?action=import-reportopens this modal automatically). - Confirm whether slash dates in your workbook follow
DMYorMDYordering in settings (or supply unmistakable ISOYYYY-MM-DDstrings). - Drop your
.csvor.xlsxfile (up to 1,000 rows, max 10 MB) and inspect the local preview table showing the first 40 rows alongside exactreadyandwith errorscounts. - Confirm import to commit rows straight into browser IndexedDB—knowing imported CSV/XLSX rows carry zero attached photo images by design.
Splitting ledgers over 1,000 rows and avoiding duplicates
When a historical spreadsheet exceeds 1,000 data rows, the preview banner flags that only the first 1,000 rows were evaluated and truncates remaining lines. Divide large multi-year archives into clean chronological slices—such as quarterly workbooks (2025-Q1.csv, 2025-Q2.csv) or calendar-year files—so zero transactions drop silently at row 1,001.
Because bulk spreadsheet imports write rows directly without merging against existing entries, never overlap start and end dates across batches. While single camera scans display a Possible duplicate warning on Review when a matching amount appears within one day, bulk spreadsheet uploads rely on your pre-split date ranges and post-import sort checks in My expenses.
Keeping budgets accurate and categorizing imported history
Once historical CSV rows live inside your Book, monthly spend charts, category budgets, and recurring subscription detection populate immediately alongside newly photographed bills. Friendly category aliases (food to Food & dining, credit card to Card) normalize messy legacy sheets automatically without forcing hours of find-and-replace cleanup.
For full round-trip workflows covering all 19 export columns, inspect our complete reference on scanning receipts to Excel and CSV. Keeping one unified schema eliminates friction between Excel pivot tables, phone camera capture, and printable annual packets.
Matching migrated records cleanly against card statements
Migrated historical rows work hand in hand with monthly card audits once past receipts sit inside local browser storage. Whenever a legacy bank CSV contains charges missing itemized category tags or supplier tax IDs, running a quick reconciliation check exposes gaps clearly.
Follow steps inside reconciling statements inside Excel to line up old bank downloads alongside imported expense logs cleanly. After completing any bulk import, always check Book totals against your original source spreadsheet sums and confirm currency decimals before preparing budgets, reimbursements, or tax returns. Never treat automated spreadsheet parsing as a substitute for verifying original receipts and accounting records.