Skip to content
logo Knowledgebase

Import file format rules

Created on  | Last modified on 

Summary

This guide explains how to import information into Sage 50 Accounts.

Description

To import information into Sage 50 Accounts, you must save your import data either in Microsoft Excel (*.xls or *.xlsx) files or Comma-separated (*.csv) files.

You can import most record and transaction types but there are some exceptions, so to get started check our guide to what you can import. 

 TIP: For more information on what fields you can import, please refer to the Data fields article.

Here we detail the format rules that your file must follow in order 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 define the columns as part of the import process.

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

Where can I find the import templates?Read more >

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


File nameTemplate used to:
Audit Trail Transaction FULL template.xls

Create a file to import your audit trail transactions. For example, Sale Invoices (SI), Bank Payments (BP), Purchase Payments on Account (PA) etc.

 TIP: For more information on what audit trail transactions you cannot import - Read more >

When importing journal transactions, Account Reference isn't a compulsory field.

You can also use this template for the Audit Trail transactions - Grouped template. This means transactions with the same accounts reference, date and reference appear under one header transaction.

Customer Record FULL template.xlsCreate a file to import your customer record details.
Fixed Asset Record FULL template.xlsCreate a file to import your fixed asset record details.
Nominal Record FULL template.xls

Create a file to import your nominal record details.

 NOTE: You must import bank accounts as nominal records and then convert them manually to bank accounts after import.

Product Record FULL template.xlsCreate a file to import your product record details.
Project Record FULL template.xlsCreate a file to import your project record details.
Project Transaction FULL template.xlsCreate a file to import your project transactions.
Stock Take FULL template.xlsCreate a file to import your stock take information.
Stock Transactions FULL template.xlsCreate a file to import your stock transactions.
Supplier Record FULL template.xlsCreate a file to import your supplier record details.

Format rules for CSV files

Comma-separated values (CSV) means that a comma separates each item of data, and each line of the file represents one record. For example, a typical customer record in CSV format would look 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 any specific order as you can specify the order as you import
  • Enclosing a data field in quotes means any commas contained within it are read as part of the data and not as a separator

     EXAMPLE: 10, Acacia Avenue would incorrectly enter 10 into the first line of the address then Acacia Avenue in the second address line but, "10, Acacia Avenue" imports correctly into address line 1.

  • Two consecutive commas signify a blank or zero length unit of data. Importing a blank field doesn't overwrite existing data in that field, leaving existing information intact. You can use this feature when you want to change selected data in a record. For example,

     EXAMPLE: To change only the telephone number in a Customer Record, the import data might read:



    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 contents is removed.



General format rules

The following rules apply to both Excel and CSV files:

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

    TextABcd234
    Integer1,2,3,4,5,and so on
    Decimal Number23.58,132.05, 0.23 and so on.


    You must keep all units of data to their specific types of text, integer or decimal numeric, if not then the import will fail. For example, a customer's account reference such as SAGE001 can contain both alpha and numeric characters, therefore 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, so it's considered a decimal number, for example, 1500.00.

  • Each data unit has a maximum length. If you enter data that's longer than the defined maximum, for example, ten characters in an eight character field, then the data is simply shortened to the required length an error isn't reported.

     EXAMPLE: An account reference of SAGE0001354 truncates to SAGE0001 because the maximum field length for an account reference is 8 characters.

  • Spaces within numeric fields aren't allowed, for example, a price of 1 000.00 doesn't import but a price of 1000.00 imports successfully.
  • Spaces within customer or supplier accounts aren't allowed.

     EXAMPLE: Sage Group PLC doesn't import but SageGrou imports successfully. The text is cropped to the maximum eight characters.

  • You can't import Quotation marks, ",into any version of Sage Accounts. For example, a product description of, 8" * 11" Timber fails because CSV files treat " differently.



Next steps

Once you've created your Excel or CSV data file, you're ready to import your data into Sage 50 Accounts >

 

Need more help?

Chat to one of our Sage Ambassadors now.

Chat now

Image