Introduction to Importing Data

This document is designed to help you through the importing process when working with the P11D Organiser. Although the software is very flexible and powerful, it is best to ensure that files are correctly prepared before starting the process. There are a number of things to consider:


Import File Preparation 


File Type

The system can import data in numerous file types but a CSV (comma separated values) file recommended, it removes the complexities of Excel formulas and formatting.  This means you need to keep an eye out for commas in your data. Make sure you remove commas from addresses and make sure your financial values are presented simply e.g. 1850.14  (no commas or currency symbols)


Header row

The header row is used by the system to automatically select the correct import target field to match for import. It is an option to modify and use the exact naming convention so to remove the need to have to manually select the appropriate system target field. However, if you are extracting data from another system, you can elect to use your internal/normal naming convention for the data from that system and simply map it to the target fields with the P11D Organiser. Don't worry, the system will remember how you mapped it last time when you come to import again (and again) under the same import routine.


Square block of data

Try to remove all data above header row, below the last record and after the last column of data. This just helps the software interpret the data correctly during the importing process. 


Consistent data

The final column of data should contain consistent data i.e. a value in every cell. This ensures that the software moves to the ‘next row’ at the correct time. As you can select the data that is imported, it is sometimes wise to simply paste a column of ‘xxx’ on the far right to provide an import end. 


Formatting

The format of some of the data to be imported is very important – the following pages clearly show what data and formats the software is expecting to import.


Key field

ALL data imported must contain a Key field - NI Number or Payroll Number for employees and Registration Number for cars – this is necessary to ensure that data can be married up correctly. Key fields are clearly highlighted in the table. 


Column Order

The column order in the spreadsheet is not important, as the P11D Organiser will automatically map as many fields as it can, and will remember the settings made during the import process as a configuration file for future use. 


Compulsory Fields

Certain other fields must also be included in order for the import routine to correctly allocate/calculate benefits particular to individual import routines. These Compulsory fields are also clearly highlighted in the tables. 


Optional Data

The remainder of data that can be imported is categorised as Optional and is displayed in standard type.


File Format for each import routine

You can see what content each import file type requires below.


Employee Information

Full Description
Database Field
Data Type
Field Length
Inclusion
Pre-Format
Address line 1ADR_1

Character

50

Optional

 

Address line 2ADR_2

Character

50

Optional

 

Address line 3ADR_3

Character

50

Optional

 

Address line 4ADR_4

Character

50

Optional

 

Cost CentreCOSTCENTRE

Character

10

Optional

 

DepartmentFull DescriptionDEPARTMENT

Character

15

Optional

 

Is Employee a Director
(Yes or No)
DIRECTOR

Character

3

Optional

Yes

No

Date of BirthDOB

Date

8

Optional

dd/mm/yyyy

Date of CommencementDOC

Date

8

Optional

dd/mm/yyyy

Date of LeavingDOL

Date

8

Optional

dd/mm/yyyy

E-mail addressEMAIL

Character

50

Optional

 

Exempt from Class 1A
(True of False)
EXEMPT

Logic

1

Optional

T

F

Fax NumberFAX

Character

15

Optional

 

ForenameFORENAME

Character

20

Optional

 

InitialsINITIALS

Character

5

Optional

 

LocationLOCATION

Character

15

Optional

 

Mobile Phone NumberMOBILE

Character

15

Optional

 

National Insurance NumberNI_NUMBER

Character

10

Key field

 

PAYE to datePAYETODATE

Numeric

10

Optional

 

Payroll Number (Employees unique ID)PAYROLL

Character

20

Key field

 

PostcodePOST_CODE

Character

10

Optional

 

RegionREGION

Character

30

Optional

 

Salary

SALARY

Numeric

10

Optional

 

Sex (biological) of employee
(Male or Female)
SEX

Character

1

Optional

M

F

SurnameSURNAME

Character

20

Optional

 

Telephone NumberTELEPHONE

Character

15

Optional

 

TitleTITLE

Character

5

Optional


User Defined / Free Purpose Field
USER_1

Character

15

Optional

 

User Defined / Free Purpose Field
USER_2

Character

15

Optional

 



Section A - Assets transferred 

Full Description
Database Field
Data Type
Field Length
Inclusion
Pre-Format
Value of Benefit
CASHEQV

Numeric

10

Compulsory

 

Annual amount of cash foregone (OpRA)
CASHFGONE

Numeric

10

Optional


Benefit Date

DATE

Date

8

Optional

dd/mm/yyy

Description of Benefit

DESCRIPTIO

Character

50

Compulsory

CARS 

OTHER 

Amount Made Good

MADEGOOD

Numeric

10

 Optional

 

Value of asset at transfer

MKTVALUE

Numeric

10

Optional


NI Number

NI_NUMBER

Character

10

Key field


Additional description

NOTES

Character

30

Optional

 

Payroll Number

PAYROLL

Character

20

Key field

 

Value of Payrolled benefit
PAYROLLED

Numeric

10

Optional




Section B - Payments made on behalf of the employee 

Full Description
Database Field
Data Type
Field LengthInclusion
Pre-Format
Value Of Benefit
CASHEQV

Numeric

10

Compulsory

 

Annual amount of cash foregone (OpRA)
CASHFGONE

Numeric

10

Optional


Benefit Date
DATE

Date

8

Optional

dd/mm/yyyy

Description of Benefit
DESCRIPTIO

Character

50

Compulsory

 

NI Number
NI_NUMBER

Character

10

Key field

 

Additional description
NOTES

Character

30

Optional

 

Payroll Number
PAYROLL

Character

20

Key field

 

Payrolled benefit
PAYROLLED

Numeric

10

Optional





Section C - Vouchers and credit cards 

Full Description
Database FieldData Type
Field Length
InclusionPre-Format

Value Of Benefit

CASHEQV

Numeric

10

Compulsory

 

Annual amount of cash foregone (OpRA)
CASHFGONE

Numeric

10

Optional


Benefit Date

DATE

Date

8

Optional

dd/mm/yyyy

Description of Benefit

DESCRIPTIO

Character

50

Compulsory

 

Amount Made Good

MADEGOOD

Numeric

10

Optional

 

NI Number

NI_NUMBER

Character

10

Key field

 

Additional description

NOTES

Character

30

Optional

 

Payroll Number

PAYROLL

Character

20

Key field

 

Payrolled benefit
PAYROLLED

Numeric

10

Optional




Section D - Living Accommodation 

There is currently no import wizard for this section of the P11D – records must be keyed in manually using the system user interface.



Section E - Mileage allowance (Privately Owned Cars)

Vehicles - Privately Owned Vehicles

Full Description
Database Field
Data Type
Field Length
Inclusion
Pre-Format

Cubic Capacity

CC

Numeric

10

Compulsory

 

Car Description

DESCRIPTIO

Character

50

Compulsory

 

Optional Extras

EXTRAS

Numeric

10

Not Required

 

Type of Fuel

FUEL

Character

1

Compulsory

D = Diesel 

= RDE2 Diesel

A = All Other


-----------------


Expanded Fuel Description & HMRC Fuel Type


D = Diesel = D
L = Euro4 = D
   E = Electric = A 

H = Hybrid = A

P = Petrol = A
B = BiFuel = A
C = Retro = A
G = GasPetrol (LPG)= A


NI Number

NI_NUMBER

Character

10

Optional

Check only

Payroll Number

PAYROLL

Character

20

Optional

Check only

List Price

PRICE

Numeric

10

Not Required

 

Date car first registered

REGISTERED

Date

8

Not Required


Registration Number

R_NUMBER

Character

10

Key field





Mileage Allowance - Privately Owned Cars business mileage payments

Full Description
Database Field
Data Type
Field Length
Inclusion
Pre-Format

Expenses value

EXPENSES

Numeric

10

Optional

 

Available From Date

FROM

Date

8

Compulsory

dd/mm/yyyy

Business Mileage value

MILEAGE

Numeric

10

Compulsory

 

Mileage Rate Scheme ID

MILESRATE

Numeric

1

 Optional

 

NI Number

NI_NUMBER

Character

10

Key field

Check only

Notes (text)NOTES

Character

50

Optional


Payroll Number

PAYROLL

Character

20

Key field

Check only

Private mileage value

PRIVATE

Numeric

10

Optional

 

Registration Number

R_NUMBER

Character

10

Key field

 

Available To Date

TO

Date

8

Compulsory

dd/mm/yyyy




Section E - Mileage allowance (Company cars)

Company Car business mileage payments

Full Description
Database Field
Data Type
Field Length
Inclusion
Pre-Format
Expenses paid
EXPENSES

Numeric

10

Optional


Mileage from Date

FROM

Date

8

Compulsory

dd/mm/yyyy

Business Mileage value

MILEAGE

Numeric

10

Compulsory

 

Mileage Rate Scheme ID

MILESRATE

Numeric

1

Optional

 

NI Number

NI_NUMBER

Character

10

Key field

 

Additional Notes

NOTES

Character

30

Optional

 

Payroll Number

PAYROLL

Character

20

Key field

 

Private Mileage value

PRIVATE

Numeric

10

Optional

 

Registration Number

R_NUMBER

Character

10

Key field

 

Mileage to Date

TO

Date

8

Compulsory

dd/mm/yyyy



Section F - Company cars and car fuel

Vehicles - Company Car Details

Full Description
Database Field
Data Type
Field Length
Inclusion
Pre-Format

Cubic Capacity

CC

Numeric

10

Compulsory

 

Grams of CO2 per Km

CO2

Numeric

10

Compulsory

 

Car Description

DESCRIPTIO

Character

50

Compulsory

 

Optional Extras

EXTRAS

Numeric

10

Optional

 

Fleet Number

FLEETNO

Character

15

Optional


Type of Fuel

FUEL

Character

10

Compulsory

D = Diesel 

= RDE2 Diesel

A = All Other


-----------------


Expanded Fuel Description & HMRC Fuel Type


D = Diesel = D
L = Euro4 = D
E = Electric = A
H = Hybrid = A
P = Petrol = A
B = BiFuel = A
C = Retro = A
G = GasPetrol (LPG)= A



National Insurance Number

NI_NUMBER

Character

10

Optional

ONLY USED TO CHECK THAT AN EMPLOYEE RECORD EXISTS

Car has no CO2 rating

NOAPPCO2

Character

3

Optional

 

Payroll Number

PAYROLL

Character

20

Optional

ONLY USED TO CHECK THAT AN EMPLOYEE RECORD EXISTS

List price of car

PRICE

Numeric

10

Compulsory

 

Date Car Registered

REGISTERED

Date

8

Compulsory

dd/mm/yyyy

Registration Number

R_NUMBER

Character

10

Key field

 



Main Company Car – (Standard company car allocation)

Full Description
Database Field
Data Type
Field Length
Inclusion
Pre-Format

Accessories added after date made available

ACCESSORY

Numeric

10

Optional

 

Capital Contributions

CAPITAL

Numeric

10

Optional

 

Annual amount of cash foregone for the Car (OpRA)
CASHFGCAR

Numeric

10

Optional


Annual amount of cash foregone for the Fuel (OpRA)
CASHFGFUEL

Numeric

10

Optional


Fleet Number

FLEETNO

Character

15

Optional

 

Intended Private Use Payment frequency (for P46 car notification) based on PAYMENT value stated
FREQUENCY
Character
 10
Optional
 Year
Quarter
Month
Week

Available from Date

FROM

Date

8

Compulsory

dd/mm/yyyy

Fuel Repaid for personal mileage in full

FUELPAID

Character

3

Optional

Y

N

Fuel paid for by Employer

FUELSCALE

Character

3

Optional

Y

N

Mileage

MILEAGE

Numeric

10

Obsolete

 

National Insurance Number

NI_NUMBER

Character

10

Key field

 

Intended Private Use Payment value (for P46 car notification) based on the FREQUENCY stated

PAYMENT

Numeric

10

Optional

 

Payroll Number

PAYROLL

Character

20

Key field

 

Payrolled value
PAYROLLED

Character

10

Optional


Actual Private use payment made in Tax Year (for P11D)

PRIVATEUSE

Numeric

10

Optional

 

Registration Number

R_NUMBER

Character

10

Key field

 

Available to Date

TO

Date

8

Compulsory

dd/mm/yyyy

Days Unavailable

UNAVAILBLE

Numeric

3

Optional

 



Additional Company Car – (Allowing overlapping allocations of company cars)

Full Description
Database Field
Data Type
Field Length
Inclusion
Pre-Format

Accessories added after date made available

ACCESSORY

Numeric

10

Optional

 

Capital Contributions

CAPITAL

Numeric

10

Optional

 

Annual amount of cash foregone for the Car (OpRA)
CASHFGCAR

Numeric

10

Optional


Annual amount of cash foregone for the Fuel (OpRA)
CASHFGFUEL

Numeric

10

Optional


Fleet Number

FLEETNO

Character

15

Optional

 

Intended Private Use Payment frequency (for P46 car
notification) based on PAYMENT value stated
FREQUENCY

Character

10

Optional

 Year
Quarter
Month
Week
Available from Date

FROM

Date

8

Compulsory

dd/mm/yyyy

Fuel Repaid for personal mileage in full

FUELPAID

Character

1

Optional

Y

N

Fuel paid for by Employer

FUELSCALE

Character

1

Optional

Y

N

Business Mileage

MILEAGE

Numeric

10

Obsolete


NI Number

NI_NUMBER

Character

10

Key field

 

Intended Private Use Payment value (for P46 car notification) based on the FREQUENCY stated

PAYMENT

Numeric

1

Optional

 

Payroll Number

PAYROLL

Character

20

Key field

 

Payrolled value
PAYROLLED

Character

10

Optional


Actual Private Use Payment made in Tax Year (for P11D)

PRIVATEUSE

Numeric

10

Optional

 

Registration Number

R_NUMBER

Character

10

Key field

 

Available to Date

TO

Date

8

Compulsory

dd/mm/yyyy

Days Unavailable

UNAVAILBLE

Numeric

3

Optional

 



Motor Trade Vehicle (HMRC Car Averaging Agreements)

Full Description
Database Field
Data Type
Field Length
Inclusion
Pre-Format

Accessories added after date made available

ACCESSORY

Numeric

10

Optional

 

Capital Contributions

CAPITAL

Numeric

10

Optional

 

Annual amount of cash foregone for the Car (OpRA)
CASHFGCAR

Numeric

10

Optional


Annual amount of cash foregone for the Fuel (OpRA)
CASHFGFUEL

Numeric

10

Optional


Fleet Number

FLEETNO

Character

15

Optional

 

Intended Private Use Payment frequency (for P46 car notification) based on PAYMENT value stated
FREQUENCY

Character

10

Optional

 Year
Quarter
Month
Week
Available from Date

FROM

Date

8

Compulsory

dd/mm/yyyy

Fuel Repaid for personal mileage in full

FUELPAID

Character

1

Optional

Y

N

Fuel paid for by Employer

FUELSCALE

Character

1

Optional

Y

N

Business Mileage

MILEAGE

Numeric

10

Obsolete


NI Number

NI_NUMBER

Character

10

Key field

 

Intended Private Use Payment value (for P46 car notification) based on the FREQUENCY stated

PAYMENT

Numeric

1

Optional

 

Payroll Number

PAYROLL

Character

20

Key field

 

Payrolled value
PAYROLLED

Character

10

Optional


Actual Private Use Payment made in Tax Year (for P11D)

PRIVATEUSE

Numeric

10

Optional

 

Registration Number

R_NUMBER

Character

10

Key field

 

Available to Date

TO

Date

8

Compulsory

dd/mm/yyyy

Days Unavailable

UNAVAILBLE

Numeric

3

Optional

 



 

Section G - Vans

Vehicles - Company Van Details

Full Description
Database Field
Data Type
Field Length
Inclusion
Pre-Format

Cubic Capacity

CC

Numeric

4

Not Required

 

Van Description

DESCRIPTIO

Character

50

Compulsory

 

Type of Fuel

FUEL

Numeric or Character

10

IGNORE


No Longer Required

A = All others

P = Petrol

D = Diesel 

National Insurance NumberNI_NUMBERCharacter
10

Not Required


Zero Emissions Van
NOAPPCO2

Character

3

Optional

Yes

No

Vans older than 3 year

OLDVAN

Logical

3

Obsolete

 

Employees PAYROLL number
PAYROLL
Character
20Not Required

Date van first registered

REGISTERED

Date

8

Compulsory

dd/mm/yyyy

Registration Number

R_NUMBER

Character

10

Key field

 



Vans (Van Allocations)

Full Description
Database Field
Data Type
Field Length
Inclusion
Pre-Format
Annual amount of cash foregone for Fuel (OpRA)
CASHFGFUEL

Numeric

10

Optional


Annual amount of cash foregone for the Van (OpRA)
CASHFGVAN

Numeric

10

Optional


Available from Date

FROM

Date

8

Compulsory

dd/mm/yyyy

Fuel cost repaid for personal mileage in full

FUELPAID

Character

3

Optional

Y

N

Fuel paid for by Employer

FUELSCALE

Character

3

Optional

Y

N

Made good value
MADEGOOD

Numeric

10

Not used


Mileage

MILEAGE

Numeric

10

Not used

 

Employee N I Number

NI_NUMBER

Character

10

Key field

 

Payroll Number

PAYROLL

Character

20

Key field

 

Payrolled valuePAYROLLEDNumeric
10Optional

Restricted Private Use

RESTRICTED

Logical

1

Optional

 

Registration Number

R_NUMBER

Character

10

Key field

 

Shared Discount

SHAREDDISC

Numeric

10

Optional

 

Shared use
SHAREDUSE

Character

3

Optional


Available to Date

TO

Date

8

Compulsory

dd/mm/yyyy

Days Unavailable

UNAVAILBLE

Numeric

3

Optional

 




Section H - Interest free and low interest loans

Full Description
Database Field
Data Type
Field Length
Inclusion
Pre-Format

Number of Joint Borrowers

BORROW

Numeric

1

Optional

 

Cash Equivalent 

(Pre-calculated)

CASHEQV

Numeric

10

Optional

COMPLEX Loans Type only

Closing Balance

CLOSING

Numeric

10

Compulsory

 

Description of Loan

DESCRIPTIO

Character

50

Compulsory

 

Available from Date

FROM

Date

8

Compulsory

dd/mm/yyyy

Interest Payable

(Pre-calculated)

INTPAYABLE

Numeric

10

Optional

COMPLEX Loans Type only

Amount Made Good

MADEGOOD

Numeric

10

Optional

 

Maximum Balance

MAXIMUM

Numeric

10

Compulsory

 

N I Number

NI_NUMBER

Character

10

Key field

 

Additional Description

NOTES

Character

30

Optional

 

Opening Balance

OPENING

Numeric

10

Compulsory

 

Payroll Number

PAYROLL

Character

20

Key field

 

Tax payable value 

(Pre-calculated)

TAXTOPAY

Character

3

Optional

COMPLEX Loans Type only

Available to Date

TO

Date

8

Compulsory

dd/mm/yyyy



Section I - Private medical treatment or insurance

Full Description
Database Field
Data Type
Field Length
Inclusion
Pre-Format
Annual amount of cash foregone (OpRA)
CASHFGONE

Numeric

10

Optional


Description of Benefit

DESCRIPTIO

Character

50

Compulsory

 

Available from Date

FROM

Date

8

Compulsory

dd/mm/yyyy

Amount Made Good

MADEGOOD

Numeric

10

Optional

 

NI Number

NI_NUMBER

Character

10

Key field

 

Additional Description

NOTES

Character

50

Optional

 

Payroll Number

PAYROLL

Character

20

Key field

 

Payrolled Value

PAYROLLED

Numeric

10

Optional


Premium Paid

PREMIUM

Numeric

10

Compulsory

 

Available to Date

TO

Date

8

Compulsory

dd/mm/yyyy



Section K - Services supplied

Full Description
Database Field
Data Type
Field Length
Inclusion
Pre-Format
Annual amount of cash foregone (OpRA)
CASHFGONE

Numeric

10

Optional


Description of Benefit

DESCRIPTIO

Character

50

Compulsory

 

Available from Date

FROM

Date

8

Compulsory

dd/mm/yyyy

Amount Made Good

MADEGOOD

Numeric

10

Optional

 

NI Number

NI_NUMBER

Character

10

Key field

 

Additional Description

NOTES

Character

50

Optional

 

Payroll Number

PAYROLL

Character

20

Key field

 

Payrolled value
PAYROLLED

Numeric

10

Optional


Value of service

PREMIUM

Numeric

10

Compulsory

 

Available to Date

TO

Date

8

Compulsory

dd/mm/yyyy

Days unavailable in year

UNAVAILBLE

Numeric

3

Optional

 



Section L - Assets placed at the employee's disposal

Full Description
Database Field
Data Type
Field Length
Inclusion
Pre-Format
Annual amount of cash foregone (OpRA)
CASHFGONE

Numeric

10

Optional


Date asset placed

DATE

Date

8

Optional

dd/mm/yyyy

Description

DESCRIPTIO

Character

50

Compulsory

 

Available From Date

FROM

Date

8

Compulsory

dd/mm/yyyy

Amount Made Good

MADEGOOD

Numeric

10

Optional

 

Market Value of asset

MKTVALUE

Numeric

10

Compulsory

 

NI Number

NI_NUMBER

Character

10

Key field

 

Notes

NOTES

Character

50

Optional

 

Other expenses valueOTHEREXP

Numeric

10

Optional


Payroll Number

PAYROLL

Character

20

Key field

 

Payrolled amount/valuePAYROLLED

Numeric

10

Optional


Rental Paid

RENTPAID

Numeric

10

Compulsory

 

Available To Date

TO

Date

8

Compulsory

dd/mm/yyyy



Section M - Other items

Full Description
Database Field
Data Type
Field Length
Inclusion
Pre-Format

Value of benefit

CASHEQV

Numeric

10

Compulsory

 

Annual amount of cash foregone (OpRA)
CASHFGONE

Numeric

10

Optional


Benefit Date

DATE

Date

8

Optional

dd/mm/yyyy

Description of Benefit

DESCRIPTIO

Character

50

Compulsory

 

Amount Made Good

MADEGOOD

Numeric

10

Optional

 

NI Number

NI_NUMBER

Character

10

Key field

 

Additional Description

NOTES

Character

50

Optional

 

Payroll Number

PAYROLL

Character

20

Key field

 

Payrolled amount/value
PAYROLLED

Numeric

10

Optional




Section N - Expenses payments made to or on behalf of employees

Full Description
Database Field
Data Type
Field Length
Inclusion
Pre-Format

Value of Benefit/Expense

CASHEQV

Numeric

10

Compulsory

 

Annual amount of cash foregone (OpRA)
CASHFGONE

Numeric

10

Optional


Benefit payment date

DATE

Date

8

Optional

dd/mm/yyyy

Description of Benefit

DESCRIPTIO

Character

50

Compulsory

 

Amount Made Good

MADEGOOD

Numeric

10

Optional

 

N I Number

NI_NUMBER

Character

10

Key field

 

Additional Description

NOTES

Character

50

Optional

 

Payroll Number

PAYROLL

Character

20

Key field

 

Payrolled amount/value
PAYROLLED

Numeric

10

Optional



A step by step guide on how to import data is available HERE




Templates

**Our recommendation is to use the extract files you create from your systems (or provided from your benefit provider) and use the manual mapping feature in the Import wizard - as the system remembers how you have mapped the data for next time which will save effort on your behalf in the long run. See mapping in the help article Importing Data


*If* you still wish to use a template - 

you can download a zip file below containing sample templates for each section. HOWEVER - please ensure you remove ALL THE COLUMNS you are not using to import.