Skip to content
logo Knowledgebase

Import benefit information using CSV files

Created on  | Last modified on 

Summary

How to import benefits information to Sage 50 P11D.

Description

You can import data from Comma Separated Value (CSV) files into Sage 50 P11D.

Let's take a closer look at the process, and what you can import.

Resolution

When you import data using CSV files, to ensure that the import is successful, the CSV files must contain the correct information, in the correct order. To help you with the import process, templates are included in the CSV Templates folder within your software installation folder. To access the templates:

  1. Log in to Sage 50 P11D as normal.
  2. Click Help then About.
  3. Click the System Information tab.
  4. In the section to the left, click Application.
  5. Under the Item column to the right, right-click P11D then click Open folder.
  6. Double click the CSV Templates folder.

You can open the relevant templates using Microsoft Excel, then save as type *.csv.

CSV templates

The templates included with your software match the fields in the Sage 50 P11D database and you have a set list of templates to choose from in the Data Import Wizard.

The titles of the columns are used by Sage 50 P11D during import to identify where in the database the data should go. This means that you can't change column titles or create your own import templates.

The following rules apply when using the CSV Import Templates:

  • The headings of mandatory fields are coloured turquoise. Unless these fields are completed, the import routine fails
  • Each column heading contains a tool tip comment, which, when the mouse hovers over it, provides advice about the fields below it
  • When the templates are saved as CSV files, only the data is imported
  • Each column contains validation rules to ensure only the correct data is entered. For example, if in a percentage field you don't enter a value between zero and 100, a message informs you that this is not permitted

Recommended order for entering or importing CSV data

Type of dataUsed byNotes
EmployerEvery templateThis is mandatory information, either manual or import entry.
Once the Employer table is imported, before importing any other tables you must update the following:
DepartmentsEmploymentThis is mandatory information, either manual or import entry.
Cost centresEmploymentThis is optional information, either manual or import entry.
Mileage ratesCarsThis is optional information and manual entry only.
Insurance groupsEmploymentThis is optional information, either manual or import entry.
Benefit informationMiscellaneous BenefitsA default set is created for each Employer. You can customise the benefit names by manual entry only.
You can now import tables in the following order:
Employer assetsUse of Assets and Gift of AssetsThis is optional information, either manual or import entry.
Employee / EmploymentAll benefitsThis is optional information, either manual or import entry.
Benefit templatesN/AThis is optional information, either manual or import entry.

Check the CSV information is valid

Sage 50 P11D checks the information in your CSV files before importing the details. To prevent an import failing, this section explains how to check for common errors.

Import informationValidity
Incorrect date formatsYou must use the date format dd/mm/yy. For example, 1 October 2020 is imported as 01/10/20. If you use any other format this appears in the error log and the record is not imported into Sage 50 P11D. You must also use a valid date, for example, 99/99/99 is not imported.
Entering negative valuesYou can only import negative values when importing loan advancements. Values for other benefits must be checked in the CSV file before you import.
Invalid charactersTo ensure accurate importing of your information, you must remove all commas from text fields. For example, if an employer's company name is Jones, Smith and Brown, delete the comma and import the name as Jones Smith and Brown. When entering values, do not use slashes (/) to separate numbers. The PAYREF field is the only field where this character is valid.
Maximum field lengthsThe comment in each field header of the CSV templates gives the number of characters allowed in each field. If the characters exceed the number allowed only the maximum number of characters are imported, any additional characters are discarded.
Employer Tax District and Reference NumberThis information is imported from the PAYREF field in the CSV template. You must enter this information using three characters followed by up to 30 characters, separated by a slash. For example, 123/9876543210.

Import information into Sage 50 P11D

The following detailed steps contain everything you need to help you import using CSV data.

Before importing data into Sage 50 P11D you should take at least two backups of your data.

  1. Select File, then click Data Import, then click Import Sage P11D Data.
  2. Click Next then select the type of information you want to import, then click Next.
  3. Click Browse, then select the .CSV file you want to import.
  4. Click Next.
     TIP: When the import is complete, the number of records read, imported and skipped appear.
  5. Click Next then click View the Import log.
  6. Click OK then click Finish.
  7. If Sage 50 P11D prompts you to recalculate your benefits, click OK then click OK.


  Still have a P11D question?

Get the most out of your P11D software by visiting our dedicated Help Centre.
Visit the Help Centre >