Excel File Won't Import into Your Accounting Software? Here's What's Going Wrong

You've got data in Excel — bank transactions, journal entries, payroll records, budget figures. You've found the import function in your accounting software. You've selected your file. And then something goes wrong.

Maybe the import fails with a cryptic error. Maybe it completes but the data looks scrambled. Maybe the software refuses to open the file entirely. Maybe everything imports fine except the dates are all wrong.

This is a more common problem than it should be, and in the vast majority of cases, the issue isn't with your accounting software or even your data. It's with the Excel file itself.

Why Excel files cause import problems

Excel is a remarkably complex file format. A spreadsheet that looks like a simple grid of numbers can carry a surprising amount of hidden content — content that accounting software import functions were never designed to handle.

Macros and VBA code

Macros get embedded in Excel workbooks easily, often without the user realising. Someone uses a macro to format data, or the file started life as a template that had automation built in. VBA (Visual Basic for Applications) modules sit in the file quietly, and when accounting software opens the file to read the data, the unexpected executable content can cause parsing to fail or trigger security warnings that block the import entirely.

Embedded objects

Charts, images, OLE links, ActiveX controls — all of these can be lurking in a spreadsheet, sometimes in sheets you've never even looked at. They inflate the file size significantly and many accounting importers simply don't know what to do with them, causing the import to fail or silently produce incomplete data.

Formulas instead of values

A cell that contains =SUM(A1:A10) looks like a number on screen, but it's a formula. When accounting software reads the raw file, it may read the formula text rather than the calculated result — importing the string "=SUM(A1:A10)" into a date or amount field. Worse, formulas that reference other workbooks will fail the moment the file is taken out of its original folder context.

Date formatting

This one catches people out even when everything else is fine. Excel stores dates internally as numbers — the number of days since 1 January 1900. What you see on screen depends on your locale settings. When you export to CSV and your accounting software reads the file under different settings, dates can come out with month and day transposed (a very common UK/US confusion), or as raw serial numbers like "45678" instead of a recognisable date.

Protection flags

Worksheets or entire workbooks can have protection flags set — sometimes deliberately, sometimes as a leftover from a template. Protected sheets prevent certain types of programmatic access, which can stop accounting software from reading the data even though it looks fine to you in Excel.

Which software is affected

In practice, almost every accounting package with an Excel or CSV import function is vulnerable to some or all of these issues. These are the ones we see most often:

The manual fix — and why it's a pain

You can clean an Excel file manually. Here's what that involves:

• Open the Developer tab and remove all macros and VBA modules from the workbook
• Go to Insert → Object and hunt through every sheet for embedded objects
• Select all cells and use Paste Special → Values Only to replace formulas with results
• Check each sheet's protection settings and clear them
• Review and reformat date cells to ensure they'll export correctly
• Save as CSV and check the output looks right before importing

On a clean, simple file with one or two sheets and no history, this takes about 15 minutes. On a file that's been passed around, accumulated macros over time, or was exported from another system, it can take much longer — and it's still easy to miss something. One embedded object in a sheet you forgot to check, one formula that looks like a value, and the import fails again.

Automating the clean-up with SheetPrep

SheetPrep handles all of this automatically. Drop your Excel, XLS, or CSV file onto the tool, choose your output format, and it takes care of the rest:

• Strips all macros and VBA code from the workbook
• Removes embedded objects and ActiveX controls on every sheet
• Optionally converts all formulas to their calculated static values
• Normalises date cells to explicit DD/MM/YYYY text strings on CSV export
• Optionally converts negative amounts to positive (required by VT Transaction and some other packages)
• Clears sheet and workbook protection flags
• Exports multiple sheets as individual CSVs in a ZIP file when needed

The whole process runs in your browser — your file never leaves your computer. You get a clean file back within seconds, ready to import.

Choosing the right output format

For most accounting software imports, CSV is the right choice — it's the simplest format, has no hidden content by definition, and is what most import functions are designed to read. SheetPrep can also output XLS (Excel 97–2003 format, the broadest compatibility option for older software) and XLSX.

If your source spreadsheet has multiple sheets and you need to import each one separately — common when you have transactions from different bank accounts or different periods on different tabs — SheetPrep will export each sheet as its own CSV file and package them into a single ZIP for download.