Sage™ 50 import errors: nominal codes, dates and tax codes
You paste your data into a workbook, save it as CSV, and Sage 50 either refuses the import or accepts it and posts something you did not mean. Most of the time the cause is the spreadsheet, not Sage.
Nominal codes lose their leading zeros
A nominal code such as 0040 is a code, not a number. Spreadsheets treat anything that looks numeric as a number, and a number has no leading zeros: 0040 becomes 40. In Excel this happens when you open a CSV directly (double-click, or File › Open), and when you type or paste into a column formatted as General or Number. Sage then cannot find the nominal code, or finds a different one.
Check by hand: look at the nominal code column for codes shorter than the rest, and format the column as Text before pasting. Sage 50's default chart of accounts uses four-digit codes, but yours may differ, so check against your own chart.
Importing a CSV into Excel safely: use Data › From Text/CSV, then in the preview choose Transform Data and set the nominal code and date columns to Text. The Text Import Wizard (Data › Get Data › Legacy Wizards › From Text (Legacy), if your Excel shows it) works too: choose Text for those columns in step 3.
Dates not in DD/MM/YYYY
The audit trail import reads dates as DD/MM/YYYY. A date Excel has turned into a real date is displayed using your regional settings, and a date it could not read stays as text. Open the CSV you will actually upload in a text editor, not the Excel view, and check a date you know.
Tax codes and amounts
- Tax codes run T0 to T99. Anything else is outside what Sage 50 can import.
- Net and Tax amounts are numbers with at most two decimals. A currency symbol, a thousands separator or a comma decimal turns the cell into text.
- A space in a cell that looks blank is not blank to an importer, and trailing spaces on codes and references can stop them matching.
- Double quotes and line breaks inside a cell can break a CSV row apart.
One thing the spreadsheet cannot tell you
A nominal code can be perfectly formatted and still not exist in your chart of accounts. That needs your Sage data. Import Cleaner for Sage explains the problem but does not check it against your chart yet. Sage 50 may also need to be in single-user mode for an import to run.
Check the file before you trust it
Import Cleaner for Sage scans a Sage export, or a sheet you are about to import, and explains every finding. The full scan, every explanation and Undo are free, and your data stays on your machine.
About Import Cleaner for Sage — the Excel add-in, coming soon to Microsoft AppSource. Also available for Google Sheets™.