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): the route covered in this article.
- From QuickBooks: see QuickBooks, connect and import. Only appears when the client is linked to QBO.
- Paste text (CSV / Excel): a quick manual method, to keep for cases where the file import doesn't work.
Open “Import a file”
Engagement → Trial balance in the left menu. At the top, the Import button → Import a file (Excel / CSV). The “Import an Excel trial balance” window opens.
Drop one or two files
Two typical strategies:
- A single file with two columns, N and N-1, already side by side (often from an ERP or a spreadsheet prepared by the client).
- Two files exported separately from QuickBooks (one per fiscal year). Ledger detects which period belongs to which file from the header date, and merges the two into N and N-1.
The Download a template button offers two variants: Net balance (one signed column) or Debit / Credit (two columns). Send this file to the client so they can fill it in and send it back.
Confirm the column mapping
After the drop, Ledger shows for each file:
- The detected Excel sheet (choose another one if needed).
- The header row (“Headers detected on row 7”, adjustable).
- The “As of” date and the period, Current year (N) or Prior year (N-1). A Swap N ↔ N-1 button corrects it if the detection got it wrong.
Then choose how the amounts are entered:
- Single balance: one signed column (positive = debit, or according to the file's convention).
- Debit / Credit: two separate columns.
Ledger automatically suggests the Account no., Description and Balance (or Debit/Credit) columns. If a column is not recognized correctly, change it in the drop-down list:
Verify the preview and import
A preview shows the first X accounts detected (badge “X accounts detected”). Quickly check the descriptions and amounts, then click Import (X accounts).
The success message shows how many accounts were added and how many were updated, with a reminder: existing groupings are preserved, and only new accounts need a code (through the Groupings tab or the Chart mapping tool).
Two accounts sharing the same number
This is common with QuickBooks: when an account is deleted and a new one is created, QBO can reuse an old number. Ledger detects it on import:
The balances of the duplicated rows are summed (net) under a single account to keep the trial balance in balance. Ledger shows the number of lines summed. If you want to keep them separate, renumber them in QuickBooks, then export again.
Pitfalls that cost you 5 minutes
Excel creates a lock file (~$…) while the file is open. This file is empty and Ledger rejects it: “Close the file in Excel then select the real .xlsx file (without the ~$ prefix).” Close Excel, then drag the correct file in again.
In Excel, format the amount columns as Accounting. Zeros disappear, parentheses show negatives, and the values line up correctly. The plain “Number” format also works, but it makes the preview harder to read.
After the import
Three steps that follow naturally:
- Open the Trial balance to check that it balances (the “Balanced” indicator at the top right should be green for N and N-1).
- Assign groupings to the new accounts (in the TB's Grouping column, or through Chart mapping for a large file).
- Turn on the relevant worksheets (Capital assets, LTD, Income taxes, etc.) through Worksheets in the menu.
Frequently asked questions
Can I re-import over an existing TB?
Yes. Existing accounts are updated, new ones are added, and groupings already in place are not touched. Your manual adjusting entries stay intact.
The file has no account numbers, only descriptions
Ledger generates temporary numbers (?001, ?002…) so the import isn't blocked. You then rename them in the TB by clicking the Account no. cell.
The preview shows 0 accounts, something is wrong
Check the column mapping: does the Account no. column really point to the column of account numbers, and the Balance (or Debit/Credit) column to the right amounts? No valid rows usually means a column is mapped incorrectly.
The N/N-1 detection got it wrong
Use the Swap N ↔ N-1 button on the file panel. Ledger switches the assigned period without making you upload the file again.