Import a trial balance from an Excel or CSV file
When the client doesn't provide a QuickBooks connection, the TB imports from a .xlsx, .xls, .xlsb or .csv file. Ledger accepts one or two files at once (current year + prior year), auto-detects the header row, the period, the columns, and merges automatically.
Three import routes, one covered here
The Import button at the top of the TB opens a menu with three options :
- Import a file (Excel / CSV) : this article.
- From QuickBooks : see QuickBooks, connect and import. Only appears when the client is linked to QBO.
- Paste text (CSV / Excel) : quick manual method, reserved for cases where file import doesn't work.
Open « Import a file »
Engagement → Trial balance in the left menu. At the top, button Import → Import a file (Excel / CSV). The « Import an Excel balance » window opens.
Drop one or two files
Drag the file into the drop zone, or click to choose. Two typical strategies :
- Single file with two columns N and N-1 side by side (often from an ERP or a spreadsheet prepared by the client).
- Two files exported separately from QuickBooks (one per year). Ledger detects which period belongs to which file via the header date, and merges them into N and N-1.
The Download template button offers two variants : Net balance (single signed column) or Debit / Credit (two columns). Send this file to the client to fill in.
Confirm column mapping
After dropping, Ledger shows for each file :
- The detected Excel sheet (pick another if needed).
- The header row (« Headers detected at row 7 », adjustable).
- The « As of » date and the period (Current year N or Prior year N-1). A Swap N ↔ N-1 button fixes if auto-detection got it backwards.
Then pick the amount mode : Net balance (single signed column) or Debit / Credit (two separate columns).
Ledger auto-suggests columns for Account number, Description, and Balance (or Debit/Credit). Change in dropdowns if a column is mis-recognized :
Verify the preview and import
A preview shows the first X detected accounts (badge « X accounts detected »). Skim the descriptions and amounts, then click Import (X accounts).
| Account # | Description | Balance N | Balance N-1 |
|---|---|---|---|
| 1000 | Desjardins Bank | 15,742.30 | 8,250.00 |
| 1020 | Petty cash | (188.20) | 150.00 |
| 1250 | Accounts receivable | 3,386.89 | 2,100.00 |
| 1440 | Inventory | 13,897.41 | 11,250.00 |
| 1500 | Capital assets | 744,999.34 | 620,000.00 |
The success message says how many accounts were added, how many updated. Reminder : existing groupings are preserved, only new accounts need a code (via the Groupings tab or the Chart mapping tool).
Two accounts sharing the same number
Frequent with QuickBooks : when an account is deleted then a new one created, QBO may reuse an old number. Ledger detects on import :
Balances of the duplicated rows are summed (net) under one account to keep the balance balanced. Ledger shows how many rows were summed. To keep them separate, renumber in QuickBooks and re-export.
5-minute pitfalls
Excel creates a lock file (~$…) when the file is open. This file is empty and Ledger refuses it : « Close the file in Excel then select the real .xlsx file (without the ~$ prefix). » Close Excel, then re-drop the correct file.
In Excel, format amount columns as Accounting. Zeros disappear, parentheses represent negatives, values align. « Number » format works too, but the preview is less readable.
After import
- Open the Trial balance to verify the balance status (the « Balanced » indicator top-right should be green for N and N-1).
- Assign groupings to new accounts (in the TB's Grouping column, or via Chart mapping for a big file).
- Enable relevant worksheets (Capital assets, LTD, Income taxes) via Worksheets in the menu.