Import file format rules

You can import most record and transaction types with some exceptions, check our article to see what you can import. 

Here we detail the format rules that your file must follow for the import to be successful. 

Format rules for *.xls or *.xlsx files

 NOTE: The option to import Microsoft Excel worksheets is only available if you have a compatible version of Microsoft Excel installed.

In each file, every row is one record and each column holds a data field. The columns can be in any order as you can change them when you import.

 TIP: Use our Excel templates when you create your import file as they have tips for each column. You'll also see compulsory fields appear in blue and optional fields are yellow.

Where are the import templates?

• To enable or disable the tips on the template, click the Review tab and click Show all comments

Format rules for *.csv files

In a CSV file, a comma separates each item of data, and each line of the file represents one record. For example, a customer record looks like this:

"SAGE0001, The Sage Group PLC, Great Park, Newcastle upon Tyne, NE13 9AA, John Smith, 0800 123 4567"

*.csv files have the following rules:

• Each line of the CSV file represents one record
• A comma separates each unit of data
• The data doesn't need to be in a specific order as you can it as you import
• The software reads quotes around a data field to ensure commas are contained as part of the data, not separators. For example,"10, Acacia Avenue" imports correctly into address line 1, while 10, Acacia Avenue splits into two lines
• Two consecutive commas signify a blank data field, which doesn't overwrite existing data. Use this to change selected data in a record. For example, to change only the telephone number in a Customer Record, if the import data reads: 

A00003,,,,,,,,0800 123 4567
The address line fields between the account code and telephone number aren't overwritten.

However, if you mistakenly input: 
A00003,,, ,,,,,0800 123 4567, the fourth field's contents are removed

General format rules

The following rules apply to both xls, xlsx and CSV files:

A unit of data in a record can be one of the following data types:

• Keep all units of data to their specific types of text, integer or decimal numeric. Otherwise, the import will fail. For example, a customer's account reference SAGE001 contains both letters and numbers, so it's considered text
• A department code can only contain numbers, therefore it's considered an integer, for example 080
• A customer's turnover can only contain decimal numbers, therefore it's a decimal number. For example, 1500.00
• Each data unit has a maximum length. If you enter data exceeding the limit, like 10 characters in an eight-character field, it shortens to the right length. You don't see any errors. For example, an account reference of SAGE0001354 truncates to SAGE0001
• You can't enter spaces within numeric fields. For example, a price of 1 000.00 won't import, but 1000.00 will
• You can't enter spaces within customer or supplier records

For example, if you import Sage Group PLC, it won't import. But SageGrou imports successfully. This is because the name gets cut off after the maximum of eight characters.

• You can't import quotation marks (") into Sage 50 Accounts. For example, a product description like 8" * 11" Timber fails because CSV files treat " differently

Next steps

Once you've created your xls, xlsx or CSV data file, you're ready to import your data into Sage 50 Accounts.