Import File Preparation


This document is designed to help you through the P11D Organiser importing process. Although the import process is relatively easy to manage, it is good practice to ensure that files are correctly prepared before you start the import process, there are a number of things to consider:-


File Type

The system can import data in numerous file types but a CSV (comma separated values) file recommended, it removes the added complexities of hidden formulas and formatting often evident in standard spreadsheets.


Square block of data

Remove all data above the file's header row, below the last record and after the last column of data. This just helps the software interpret the data correctly.


Formatting

The format of some of the data to be imported is very important, for example any date data should be formatted as DD/MM/YYYY 


Key field

ALL data imported must include 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 tables.


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 shown in the help article - Importing data - What fields of data are available to import.


Optional Data

The remainder of data that can be imported is categorized as Optional and is displayed in standard type. These fields are also clearly highlighted in the tables shown in the help article - Importing data - What fields of data are available to import.




Step by step guide to importing data

The P11D Organiser has an import wizard which is a step-by-step process of importing employee and benefit data easily into the system. Each P11D section has its own import routine and there is also an import routine for employee data.


The process shown below is a step by step guide taking you through the import routine for Section I benefits (Private medical treatment or insurance) although each import routine follows the same steps with just a different set of expected target fields and data validation rules.


STEP 1 – IMPORT

Select FILE > IMPORT WIZARD (or use the primary toolbar "IMPORT" icon to go to the Import wizard)



STEP 2 – IMPORT ROUTINE

Select the Import routine to run: 1 Employee details click NEXT




STEP 3 – SOURCE FILE

Navigate to the location of the folder that contains the .CSV file to be used in the import process and click NEXT





The software will also display any other available files from the same source location/directory



STEP 4 – MAP SOURCE DATA TO TARGET FIELD

Using the IMPORT TEMPLATE (refer to help article - Importing data - What fields of data are available to import for further information on formatting requirements), the system will automatically configure and select the matching target field for the source data on the source CSV file (header row displayed on the left will match the correct corresponding "target field" displayed on the right).




NOTE: If you are not using the IMPORT SYSTEM TEMPLATES then you will need to manually select the correct target fields using the “TARGET FIELD SELECTION” button. The target fields will initially be un-selected and displayed as ** EXCLUDED ** in the TARGET FIELD column. Simply Double-Click the Source Data Column item and select the Target field name from the panel provided to map accordingly.


STEP 5- KEY FIELD & DATA VALIDATION
The system will display the data in a table. A key ID selection of must be made between either PAYROLL number or NI Number (unique reference for employee)

GREEN entries are EXISTING records in the system that will be updated/replaced (recognised by the Key field used - e.g. PAYROLL) 
WHITE entries are NEW records being added to the system benefit database (recognised by the Key field used e.g. PAYROLL)






Clicking the NEXT button, the system will begin to validate the data and any records failing validation rules will be referred to in a series of pop-up messages. Any resulting invalid records will be removed from the import process and saved to an error log file which can be viewed at the end of the import process.


Example: Missing/Blank employee's Payroll number record (cannot create a record using a blank key employee ID)



Example: Missing/Blank employee's NI Number and Date of Birth (Invalid for HMRC submission)




STEP 6 - COMMIT DATA CHANGE

The system will now commit the data & create the benefit records. The system will confirm the numbers of records to be added & updated and the total number of records from the source file.

NOTE - checking the Backup DATAFolder is recommended as it provides you with a restore point prior to committing the changes being made during the import.




CLICK FINISH to finalise the import and commit the data change to the system database. 




The system will now display a dialogue box to confirm the total number of records Added, updated and removed during the import process.





If your file contained no errors that need to be re-imported, simply click CANCEL to exit from the import wizard and return to the main P11D Organiser system.



Validation - Managing Errors

If your file *did* contain errors that need to be reviewed/addressed:-


Any records removed during the validation process will be saved as an “Error file”

These error files can then be viewed and saved in MS EXCEL. Having made the necessary corrections and changes to the invalid records contained in the "error file" it can be used as a new source file to import the corrected records and complete the import for ALL RECORDS (initial load plus the subsequent "fixed" error records)



Select the Error Files Tab of the Import Wizard


The system displays a series of "Error Files" These are .DBF files that contain the record that were removed due to the validation rules applied during the import process. These are displayed in date order and are named using the name of the import file used together with a date and time stamp. 




Select the relevant .DBF file and choose to either; 

SaveAS - save a copy of the DBF file to use a different file extension (.CSV recommended) and review the file as a standard spreadsheet file.


Excel - Launch Microsoft Excel program directly and view the DBF file in Excel. 




The STATUS column (Column AJ) displays a code to explain the reason why the record was rejected, in the example below, we can see that:-

The record for employee with PAYROLL PR0025 was removed as the record contained no NI Number or Date of birth and would be invalid to the HMRC Gateway (NI_NUMBERDOB) 

The second record contained a blank employee ID (PAYROLL number) so could not be imported as such.



Having made the necessary corrections and changes to the invalid records contained in the "error file" it can be used as a new source file to import the corrected records and complete the import for ALL RECORDS (initial load plus the subsequent "fixed" error records)



If your file contained no errors that need to be re-imported, simply click CANCEL to exit from the import wizard and return to the main P11D Organiser system.