Import file format rules

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

 TIP: See our article for more information on the data fields you can import.

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

Format rules for Microsoft Excel files

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

In an Excel file, each row represents one record and each column represents a data field. The columns don't need to be in any specific order as you can change these when importing.

 TIP: We recommend you use our Excel templates to create your import file. These include tips on what to include in each column, compulsory fields appear in blue and optional fields are yellow.

Where can I find 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 data item, and each line of the file represents one record. For example, a typical customer record in CSV format 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 change the order as you import
• Quotes around a data field ensure commas contained within it read as part of the data, not a separator. 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 4th 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 ten characters in an eight-character field, it shortens to the required length. No error is reported. For example

  An account reference of SAGE0001354 truncates to SAGE0001

   

   

• Spaces within numeric fields aren't allowed. For example, a price of 1 000.00 won't import, but 1000.00 will

• Spaces within customer or supplier records aren't allowed. For example,

  Sage Group PLC won't import but SageGrou imports successfully. The text crops to the maximum 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.