Spreadsheets & Reports

Import CSV & Excel Into Expense Books

On this page5 sections
  1. Preparing CSV and Excel files before importing locally
  2. Why do spreadsheets break or swap dates during bulk import?
  3. Step-by-step bulk import workflow in My expenses
  4. Keeping budgets accurate and categorizing imported history
  5. Matching migrated records cleanly against card statements

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 .xlsx upload directly in memory.
  • Clean template starter: Start with headers aligned to our canonical schema so date, merchant, total, currency, taxId, and notes snap cleanly into place.
  • Protect against corrupted signs & totals: Strip footer =SUM rows and check date-order defaults (DMY vs MDY) 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.

  1. Select the target Book and choose Import (?action=import-report opens this modal automatically).
  2. Confirm whether slash dates in your workbook follow DMY or MDY ordering in settings (or supply unmistakable ISO YYYY-MM-DD strings).
  3. Drop your .csv or .xlsx file (up to 1,000 rows, max 10 MB) and inspect the local preview table showing the first 40 rows alongside exact ready and with errors counts.
  4. 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.

Common questions

  • How many rows and megabytes can I import in a single CSV or XLSX batch?

    The local spreadsheet importer parses up to 1,000 rows per file (up to 10 MB file size and up to 50 columns), keeping everything inside your device browser memory cleanly.

  • Are older binary .xls workbooks supported directly?

    No. Save older binary .xls files as modern OpenXML .xlsx or UTF-8 .csv first so the lazy-loaded browser workbook parser reads cells reliably.

  • How does Receipt Scanner prevent April 11 and November 4 from swapping on import?

    Always use ISO YYYY-MM-DD dates when possible, or confirm DMY versus MDY ordering before parsing numeric slash dates so rows map to accurate accounting months.

  • Why should I delete Excel bottom TOTAL summary rows before uploading?

    Spreadsheets with footer =SUM(...) summary rows below transactions either trigger row validation errors or double-count totals if imported as expenses alongside raw transactions.

  • Do imported spreadsheets upload across the internet for conversion?

    Never. CSV files parse strictly in pure JavaScript and Excel XLSX reading loads lazily in client memory with zero remote server uploads.

  • Why do bank CSV rows with negative numbers fail import validation?

    Receipt Scanner expects positive expense totals above zero, so bank outflows formatted as -42.50 or (42.50) fail validation. Convert debit charges to positive amounts with =ABS(...) and remove credit refund or payment rows before importing.


Put it into practice

Scan receipt