Importing Data

Modified on Fri, 6 Sep at 3:32 PM

For a video tutorial on how to import employees into the P11D Organiser please click on the link below:

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 flexible and intuitive, it is best to spend some time to ensure that files are correctly prepared before starting the data loading/importing process. There are a number of things to consider:

Import File Preparation

Please refer to help article Importing data - What fields of data are available to import for information on formatting requirements.

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 select your file - click OPEN

NOTE: It is also possible to import XLS and XLSX files

It is worth noting that you can import XLSX files with multiple sheets, an example would be, here we have created a file that contains employees and all different benefit types, after I have selected the file I can choose which one I want to import, for instance:

In this respect therefore, it is possible to have your entire import routine within one file if you wish to do so.

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 “Map Data Columns” button to connect your source fields in your file to the database fields in the system. The target fields will initially be un-selected and displayed as ** EXCLUDED ** in the TARGET FIELD column if it is not naturally identified the same as the source.

Data Mapping Screen

This screen will show Mapped/Un-Mapped (excluded) as filtered lists or show all types together using the top controls (1).

You map by clicking the target field you wish to associate with the highlighted source field. In the bottom area of the screen, you can see a sample of the field data from your source file (2) and the design of the field you are targeting as the place for that data to be stored (3).

You can click Exclude to remove the mapping for elements you no longer wish to use for importing.

When you have finished mapping, click "OK" to return to the summary screen. Then click "NEXT " to continue.

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 and 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.

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 records 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.

Save As - 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.