Common Trial Balance Import Errors (And How to Fix Them)

Introduction

You click "Import." The progress bar fills. And then: "Error: Unable to process file. Row 82: Invalid data type."

Trial balance import software for tax preparation helps prevent many of these errors by automatically validating file structure and data formats before import.

Row 82. Out of 200 rows. You scroll to row 82. It looks identical to row 81 and row 83. Nothing obviously wrong. You stare at it for thirty seconds trying to spot the difference. Eventually you notice that the debit amount is formatted as text instead of a number because it has a trailing space character that's invisible unless you click into the cell.

Trial balance import errors fall into predictable categories once you've seen enough of them. File structure problems. Data type mismatches. Formatting issues. Character encoding disasters. The error messages tax software gives you are rarely helpful ("Invalid data" doesn't explain what's invalid or why), but the actual problems are usually straightforward once you know what to look for.

This guide walks through the most common trial balance import errors, explains why they happen, shows you how to fix them, and (more importantly) how to prevent them from happening in the first place.

File Structure Errors

The Problem: Subtotal Rows Mixed with Data Rows

Accounting systems love subtotal rows. Every section gets a subtotal. Assets, Liabilities, Equity, Income, Expenses—all subtotaled. These look helpful in reports meant for human reading. They break import processes because tax software expects one row per account with no summaries mixed in.

When your import hits a subtotal row, it tries to process "Total Assets" or "Total Income" as if it's an actual account with a number and a category. The import fails because there's no account number (just text) or because the number format is different (subtotals are often bold or formatted differently), or because the structure doesn't match what the software expects.

**The fix:** Open the file in Excel. Sort by account number. Subtotal rows usually don't have account numbers, so they'll group together when you sort. Delete all the rows without account numbers. Or, if you're feeling fancy, use Excel's "Go To Special → Blanks" feature to select all rows where the account number column is blank, then delete them all at once.

Better solution: Use trial balance software that detects and removes subtotal rows automatically during import. You shouldn't be manually cleaning file structure—that's exactly the kind of pattern-based work software handles better than humans.

The Problem: Multiple Header Rows

Someone thought it would be helpful to have the company name in row 1, the report title in row 2, the date range in row 3, and then the actual column headers in row 4. Maybe there's a blank row 5 for spacing. The data starts at row 6.

Tax software assumes your data starts at row 2 (row 1 is headers, row 2 is first account). When it encounters company names and report titles where it expects account data, the import fails with messages about invalid column structure.

**The fix:** Delete rows 1 through 5. Move your column headers to row 1. Make sure row 2 is the first account. Save the file.

This is tedious when you're processing 80 trial balances during tax season. Every file needs the same cleanup. Every time.

The Problem: Hidden Columns or Worksheets

The Excel file has seven worksheets. One contains the trial balance. Three contain pivot tables analyzing the data. Two contain notes. One contains a template someone used to build the export.

Or there's one worksheet, but it has columns A through Z. Columns A through E contain trial balance data. Columns F through R are blank. Columns S through V contain summary calculations. Columns W through Z are hidden.

Tax software looks at everything—all worksheets, all columns, hidden or not. When it encounters data that doesn't match expected structure, the import fails.

**The fix:** Delete all worksheets except the one with trial balance data. Delete all columns except the ones with account number, account name, debit, and credit. Unhide everything so you can see what you're working with. If there's data in hidden columns that you need, move it to visible columns before deleting.

Then save the file with a new name like "ClientName-TrialBalance-Cleaned.xlsx" so you still have the original if you deleted something important.

Data Type and Formatting Errors

The Problem: Numbers Stored as Text

The debit column shows $15,420. It looks like a number. It's formatted like a number. But Excel treats it as text because of how it was exported from the accounting system. When tax software tries to import it, the import fails because "fifteen thousand four hundred twenty dollars" can't be processed in a field expecting numeric data.

You can spot numbers-stored-as-text because they're usually left-aligned in Excel cells instead of right-aligned, or they have a little green triangle in the corner warning you something's wrong.

**The fix:** Select the entire debit column. In Excel, look for the warning dropdown that says "Convert to Number." Click it. Do the same for the credit column. This converts text that looks like numbers into actual numbers that math works on.

If that doesn't work, create a new column next to debit called "Debit2". In the first cell, type =VALUE(A2) where A2 is the first debit amount. This function converts text to numbers. Copy that formula down the entire column. Then copy the new column and paste it as values over the old column. Delete the helper column.

The Problem: Negative Numbers in Parentheses

Your trial balance shows credits as ($15,420) instead of -$15,420 or just $15,420 in a separate credit column. This is an accounting convention—parentheses mean negative numbers or credits depending on context.

Tax software expects numbers in a recognizable format: positive numbers or negative numbers with minus signs. Parentheses confuse the parser.

**The fix:** Use Excel's Find & Replace. Find "(" and replace with "-". Find ")" and replace with nothing. This converts ($15,420) to -$15,420. Then make sure the column is formatted as numbers, not text, or you'll have the numbers-stored-as-text problem on top of the parentheses problem.

The Problem: Account Numbers Losing Leading Zeros

Your chart of accounts uses numbered like 00100, 00200, 00300. Excel sees these as numbers and displays them as 100, 200, 300. The leading zeros disappear. When you try to match this year's trial balance to last year's categories, the account numbers don't match, and you lose all your year-over-year mapping.

This happens when you open CSV files in Excel or when someone exports from accounting software without forcing text format on the account number column.

**The fix:** Format the account number column as Text before you open the CSV file. In Excel, use "Data → From Text/CSV" instead of just opening the file. This lets you specify that the account number column should be treated as text, preserving leading zeros.

If you've already lost the leading zeros, you'll need to know what they should be (check last year's trial balance or the client's chart of accounts) and add them back manually or with formulas.

Character Encoding and Special Character Problems

The Problem: Names with Accents Turn into Gibberish

Your client's business name is "Café Del émont" but when you import the trial balance, it shows up as "Café Delémont" or some variation of character garbage. This is a character encoding mismatch. The file was exported in UTF-8. Your tax software tried to read it as Windows-1252 (or vice versa). Characters that don't exist in both encoding systems get corrupted.

**The fix:** Open the file in a text editor that lets you change encoding. Save it as UTF-8. Or save as Windows-1252. Try both and see which one preserves the special characters correctly when you import.

Better solution: Avoid the problem entirely by asking clients not to use special characters in account names. "Cafe Delmont" imports fine. "Café Delémont" might not depending on system configurations you don't control.

The Problem: Commas in Account Names Breaking CSV Files

You export the trial balance as CSV. Account 6420 is named "Meals, Travel & Entertainment." CSV stands for Comma-Separated Values, which means commas delimit columns. When the account name contains a comma, the CSV structure breaks. "Meals" ends up in one column. "Travel & Entertainment" ends up in the next column. Everything shifts and the import fails.

**The fix:** Remove commas from account names before exporting to CSV. Or use tab-delimited format (TSV) instead of CSV. Tabs are much less likely to appear in account names than commas.

If you're stuck with a CSV file that's already broken, you can sometimes fix it by opening in a text editor and wrapping account names in quotes, but at that point you're spending more time fixing the file than it would take to just ask the client for a clean export.

Common Fixes That Actually Make Things Worse

Don't: Open CSV in Excel and Save It Again

You receive a CSV file. You open it in Excel to review it. Everything looks fine. You save it. Now the CSV file is corrupted because Excel "helpfully" reformatted your dates, converted large numbers to scientific notation, and dropped leading zeros from account numbers. Never open a CSV file in Excel unless you're planning to clean it and save it as Excel format. If you need to view CSV files without corruption, use a text editor or a dedicated CSV viewer.

Don't: Copy and Paste from PDF to Excel

You receive the trial balance as PDF (the worst format). You think "I'll just copy the data from PDF and paste it into Excel." This seems faster than using PDF extraction tools. It's not. Copy-paste from PDF loses column structure, introduces formatting problems, and creates more cleanup work than it saves. Use proper PDF extraction tools if you absolutely must work with PDF, or ask the client for a different format.

Don't: Manually Type Corrections into Tax Software

You discover an error after you've already imported the trial balance into tax software. You think "I'll just fix it directly in the return instead of going back to the source file." This creates version control problems. Your trial balance workpaper no longer matches the tax return, and six months from now nobody will remember what was changed or why. Always fix errors at the source (the trial balance file), then re-import.

Prevention: Better Than Fixing

The best way to handle trial balance import errors is to not have them in the first place. This requires either teaching clients how to export clean trial balances or using software that handles messy files automatically.

**Option 1: Standardize client exports.** Create export instructions for each major accounting system your clients use (QuickBooks, Xero, Sage). Specify exactly which report to run, which format to export, and which options to select. Send these instructions to clients during onboarding. This reduces variability but doesn't eliminate it—clients don't always follow instructions.

**Option 2: Accept variability and automate normalization.** Use trial balance software that detects format automatically, removes subtotals, normalizes data types, and handles the cleanup work without manual intervention. You spend time on categorization (decisions) instead of data cleanup (tedious pattern-matching work that software does better).

Frequently Asked Questions

The import succeeded, but some accounts are missing from the tax software. What happened?

Most likely, those accounts have zero balances, and your tax software is configured to suppress zero-balance accounts during import. Check your import settings. There's usually an option to "Include zero-balance accounts" that's turned off by default. Turn it on if you want all accounts imported regardless of balance. Alternatively, filter zero-balance accounts out of the trial balance file before importing if you don't want them cluttering your return.

Why does the same trial balance file import fine some years but fail other years?

Your tax software was probably updated between last year and this year, and the update changed import validation rules. What was acceptable in last year's version is now flagged as an error. Or the client changed accounting systems and the export format changed even though the file "looks the same." Small structural changes that aren't visible can break imports after software updates.

Can I import a trial balance that has monthly columns instead of just year-end balances?

Depends on your tax software. Most tax software only wants year-end balances for the tax return, so you'd need to use just the December column (or whatever your year-end month is) from a monthly trial balance. Delete the other columns or create a cleaned version with only the year-end balance column before importing. Trying to import all months will either fail or import the wrong data into the wrong fields.

Fix Errors Systematically (Or Avoid Them Entirely)

Ledger IQ handles trial balance imports from Excel or CSV regardless of format quirks. The software automatically detects and removes subtotal rows, normalizes column headers, validates data types, and handles formatting issues that break traditional imports. Import messy files, categorize accounts using dropdowns, and export clean CSV files to Lacerte without manual data cleanup. Currently supports Lacerte with additional tax software platforms in development.