Migrate from Sage 50 CSV¶

This is a comprehensive guide to import an entire payroll creating a company and workers. This is a fully automated process with manual steps only required where data is unavailable.

Process Overview¶

Automated migration process¶

Collect the required files¶

The following steps are performed in your existing software to collect the required files:

GB Sage 50 input file specification¶
File	Types	Notes
Employees	.csv, .ods, .xls, .xlsx	Employee details
YTD	.csv, .ods, .xls, .xlsx	Year To Date totals
Payment History	.csv, .ods, .xls, .xlsx	Actual payments for the pay schedule to be selected
Holiday History	.csv, .ods, .xls, .xlsx	Holiday earnings (weekly basis)

Before you can perform a data export, you must filter and select employees you wish to export. On the horizontal toolbar, or by right-clicking All Employees, select Criteria You will likely wish to exclude employees who are:

Historical Leavers

Depending upon your payroll setup, you may also wish to exclude employees who are:

On Hold
Current Year Leavers

Exclude employees by clicking the relevant boxes and then click OK

Important

On the left of Employee List click All Employees from the outline tree view (select Outline on the horizontal toolbar if you can’t see the tree view).

First, export a CSV file from Sage 50 payroll with employee details:

File ‣ Data Export… ‣ Payroll Data Export…
Select Employee Details and click Next
Select any suitable location (Path) to store your file e.g. desktop
Tick a checkbox to include column headings
Click Finish
You should have a newly created file Employee Details.csv

Then repeat to export employee year to date (YTD) CSV figures:

File ‣ Data Export… ‣ Payroll Data Export…
Select Employee Year to Date Figures and click Next.
Select any suitable location (Path) to store your file e.g. desktop
Tick a checkbox to include column headings
Click Finish
You should have a newly created file Employee YTD.csv

Next you will need to generate a Payment Type History (Detailed) XLSX for each pay frequency (Monthly, Weekly etc.) you wish to import:

Reports ‣ Employee ‣ Payment Type History (Detailed)
Select Data to Excel – do not choose Report to Excel as these are easily confused
On the pop-up Criteria for Payment Type History (Detailed) ensure the following:
- Employee Reference: choose all employees (the default)
- Processing date: ensure you select 2 years (104 weeks) of history by entering two dates using Between (inclusive)
- Payment Type should be All (the default)
- Click OK.
Excel will start, then File ‣ Save As and select any suitable location (Path) to store your file e.g. desktop
Important

Repeat for each frequency e.g. Weekly, Monthly etc.
You should have newly created file(s) Monthly Payment Type History.xlsx [Weekly Payment Type History.xlsx]

Holiday History¶

If you are only importing a weekly schedule, then you can use the same Weekly Payment Type History.xlsx for Payment History and Holiday History. However, any pay schedules other than weekly can only use the file exported previously as the Payment History. You will need to create a Holiday History file with exactly the same format using 104 weeks of weekly earnings history.

History file columns¶

The Payment History will be the same as the schedule e.g. monthly and is the actual amount paid on historical pay dates. The Holiday History must be weekly and represents the pay earned for each week for each historical week - use the week ending day that matches your pay reference period e.g. Sunday

There are 4 important columns in each history file depending on the history file:

History file columns¶
History file	Employees.Reference	Updates.ProcessDate	PaymentHistory.PaymentDescription	PaymentHistory.PaymentAmtTotal
Payment History	Employee works_id	Actual historical payment date (same as schedule)	Name of the payment e.g. Shift, Bonus, Holiday	Amount paid
Holiday History	Employee works_id	Historical Week ending dates (weekly)	Generally a single value e.g. Holidaypay	Amount earned

Click to download a sample Payment History.csv file. The file can be in CSV or XLSX format and the 2 header rows must match this format.

Other files¶

Whilst not required for the import process, we suggest you also collect/ locate these other files to verify and complete the import. These would include all frequencies to be imported (e.g. weekly and monthly):

Payslip PDFs (standard employee payslips) ideally in a single file which can be quickly searched using Find Ctrl + F on a PC or Command + F on a Mac
Pension CSV files for member enrolment and member contributions (the files uploaded to the pension provider after each payroll)
Bank Payment files (BACS or Faster Payments file)
Previous Pay and Previous Tax for any mid year starters - ideally a spreadsheet

The following will be required after import to verify pension auto-enrolment. These are generally available from the pension providers portal:

Pension auto-enrolment dates for all enrolled employees plus contribution percentages
Pension opt-out dates for any employees who have opted-out of auto-enrolment
Postponed or deferred employee dates

Finally collect any statutory information to enter:

SMP SSP SPBP for any active Statutory absences.
Attachment orders for any required attachment of earnings orders.

if applicable, the following files wil be useful to check and reconcile recoverable amounts and apprenticeship Levy:

P32 Report
- Reports ‣ Period End ‣ Form P32 - Employer Payment Record
- Click Preview and in Criteria Values enter the tax month from 1 to the current tax month
- Click OK
- Export to save as a PDF file, select any suitable location (Path) to store your file e.g. desktop and click Save
Apprenticeship Levy Reconciliation report

Be sure to include all employees first:
- Click Criteria and include all employees by clearing the checkboxes:
  - On Hold
  - On Holiday
  - Current Year Leavers
  - Historical Leavers
- Click OK
Then run the report:
- Reports ‣ Period End ‣ Apprenticeship Levy Reconciliation
- Click Preview and in Criteria Values enter Start of the tax year
- Click OK
- Export to save as a PDF file, select any suitable location (Path) to store your file e.g. desktop and click Save

Import the files¶

Click the Import button and select Run.

If the import files include multiple pay schedule frequencies (e.g. weekly and monthly) dates, then you will need to import each frequency separately.

On the first wizard screen:

Leave Company blank as you will be creating a new company called Test company which can be renamed later.
Click each Choose file to select each file.
Click Upload files.

On the second wizard screen:

It is possible to use the content of the Worker Details file to filter out all but the matching subset of FPS entries.
The Found tax reference can be used to check you are importing the intended company.
Mode should be set to the default Create “Test company” for the first import to create a new company. For the 2nd or subsequent import select Merge workers with “Test company” to merge a new import into an existing company.

Note

If you select Delete then re-create “Test company” you will lose any changes made.
If you only have one pay schedule frequency, Select pay schedule and target pay day: will be set. Otherwise, select the first frequency to import.
You must also enter the Pay period end date. This is not the pay date - it is the last day that employees worked and were paid.
AWE (Average Weekly Earnings) history is extracted automatically. You need to specify which payment names are relevant in Extract AWE history from:. AWE normally includes all earnings that are subject to NI. If you have a small number of payment types, you can provide a list of the names separated by semi-colon e.g. Salary;Bonus. If you have many payments this can be tedious. Instead, you can use a shorthand for example .* will match all payments.

Tip

Regular expression stems are a shorthand way of grouping several names using special characters. paiyroll® identifies all top-level payments for you in the help text. For convenience, you can copy/paste these values to save typing. Optionally you can then use .* to match multiple payment names - here are some examples:
- Shift.* would match payment names Shift 1 and Shift 2 and Shift 3
- .* would match any payment name
Extract holiday pay history from: needs to be specified in a similar way to AWE. Because holiday pay should be excluded, the regular expression .* would not normally be used because it includes all names and would, therefore, include the word Holiday Pay. Ensure you match every payment name except Holiday Pay
paiyroll® will create a Holiday Pay Definition for you, just provide the name you would like to use. Holiday is a suitable choice.
Enter the name of salary payment type used into Extract salary history from:. This might be Salary or Directors Salary. If you do not pay a salary, then enter a regular expression that will not match for example Not_Used will not match any payments used.
Enter the Pay Definition name you would like to use in Salary in Save salary history to:. e.g. Salary. If you don’t use a salary for a particular pay schedule, then enter a name you can identify and delete later like Salary (not used)
Click Import files to start the import. The import may take some time to complete depending upon size. Review the log information for any messages. Once complete, a pay run is automatically started and ready in a few seconds. You should review the payslips in My documents
Important

If you have a second schedule to import, you will likely need to modify default values for certain Pay Definitions such as AE Schemes e.g. monthly and weekly. Before running a merge import take the following steps:
1. Decide on a new appropriately named Pension scheme for the next import. Add a new pension scheme Pay Definition using the using the Pension scheme Pay Template. It is principally the new Scheme Definition name which is important and you you may wish to defer setting other scheme values.
2. Change default AE scheme NEST Scheme on the AE Pay Definition to your new scheme above then click Update to ensure your new scheme name is applied to the merge import.
Now repeat the above steps import the next schedule carefully ensuring you select the mode Merge workers with “Test company”.

The following are automatically created by the import process:

Company
Employees
Pay schedules
Pay Run
Workflow Definitions
Report Definitions
Payment Pay Definitions and Pay Items for all payment types from the import files.
Automatic Enrolment (AE) Pay Definitions, default Schemes and Pay Items
A Salary Pay Item is added using the most recent payment value identified for each employee. The annual salary will be correct assuming a full payment was made. If a partial or larger payment was made, e.g new starter, then this will need to be corrected.

Sage 50 files include numbered departments which have been created. You may wish to rename these Departments with more meaningful names.

Note

The Sage50 import places you at the same point where you exported the data. This will be after your previous payrun was approved in your previous system with all Pay and AE. When following the steps below, you will therefore be running your next payroll.

Upload missing items¶

The imported file(s) may still be missing higher level data for example Salary, Shifts etc.. The following steps are therefore required after an import to complete the payroll. Almost all of this data can be uploaded from spreadsheets. If you wish to perform multiple imports for Parallel runs, you will find it quicker to create spreadsheet files to upload. The easiest way is to create the data manually using the GUI first time, then download the data into spreadsheets when complete.

Company Update all Company fields as the imported files only provide limited data. Now is a good time to rename the company.
Pay Definitions for Pay, Pension, Holidays, Deductions and Schemes are required:
- Scheme Pay Definitions e.g. Pensions and Holidays will need to be created before you can create the associated Pay Definitions.
- Automatic-enrolment (AE). The import process will create AE Pay Definitions, Schemes Definitions and Pay Items. The import process uses heuristics and may not be 100% accurate. You will therefore need to verify that the AE migration is correct for each employee. The quickest way to do this is to download the Pay Items: Navigate to Pay Items, then click Download… to download a payitems.csv file. Open the file using a spreadsheet program and then filter on column B definition with AE to exclude all other Pay Items. Verify the following for each worker:
  - Worker percentage (inputs-3-value)
  - Scheme (inputs-4-value)
  - Migration category (inputs-5-value)
  - Enrol date (inputs-8-value)
  - Migration date (inputs-9-value)
  If you need to make and save changes, simply copy the filtered AE Pay Items into a new file and edit the cells. Then upload this file by navigating to Pay Items and then click Upload….
  
  AE migration is also described fully in Migrate Auto-Enrolment Pension data.
- Holiday. Set up Holidays.
- Deductions
- If you have additional salary pay items, e.g. Directors Salary which were not specified on import, they will be imported using a Payment Pay Template instead of the Salary Pay Template. Replace these with a suitable salary Pay Definition.
- If the Importer generated a differently named salary Pay Definition to avoid a clash with history import e.g. Salary (monthly) vs. Salary, then you can revert to the original name as follows:
  - Delete the Salary Pay Definition by confirming on the delete screen, there are no dependent Pay Items.
  - Rename the Salary (monthly) the Pay Definition to Salary
- Reuse. Set the Reuse on each Pay Definition according to each intended usage and whether it is required to pay each pay run. There are 4 values:
  - Use indefinitely pays each pay run e.g. a recurring medical benefit that doesnt change
  - Use once, then set to zero pays once and then changes to zero e.g. a one-off bonus
  - Use once, then unset pays once and expects a new value e.g. commission that varies each month but requires a different value
  - Never prompt even if unset set manually/ infrequent e.g. annual bonus
- Finally, delete any imported Pay Definitions that are not required which may have been created to aid the import process. For example Pay or NonTaxOrNICPmt. When deleting a Pay Definition always carefully check the descriptive text to see if there are any linked Pay Items.
The imported pay schedules may not reflect your dates. Navigate to Pay Schedules and Update… the schedule. You will need to clear the Enabled checkbox to disable the schedule and click Update. Now you can update the schedule with your dates and don’t forget to click Enabled before updating the correct schedule. Once this is done you must start a new pay run using Workflow Definitions and click the Start button . Delete the old payrun from Payruns by clicking Delete and Confirm deletion.

Important

The pay period dates on the pay schedule must continue from the Pay period end date set on import.
Employees: Add any new employees if required. Test employees or template employees are created for you to make it quicker to add the same Pay Items to similar starters. Add an employee and ensure the Test employee check box is ticked. Add any required Pay Items, e.g. Pension. The Add From… tab on the Pay Item editor allows you to add Pay Items from any existing Test employee. Upload Data Feeds can also use Test employees. If you have different types of workers or different schedules, you will want to add addiaitoanl test employees.
Pay Items. Use the Pay Item Editor to review each employee and add any missing Pay Items such as salary, bonus etc. from your Pay Definitions. You can ensure they have the correct values by clicking Update which will generate a live payslip.

If you need to add multiple Pay Items, we recommend the use of spreadsheet upload files. One easy way to start is to create a single Pay Item for an employee using the Pay Item editor . Then navigate to Pay Items and then click Download… to download a payitems.csv file. The file will include all Pay Items for all employees, so be sure to delete every Pay Item except the required one. Once you have created the Pay Items, these can easily be uploaded.
- Previous pay and tax for in-year starters must be entered or uploaded to ensure payroll is correct.
- Enter the annual holiday entitlement and balance for the remainder of the year for each employee. See Set up Holidays for more information.
A basic set of Report Definitions are provided. You should add any required.
- HMRC DPS. It is recommended that you add the HMRC DPS (Data Provisioning Service) to import updated coding notices from HMRC . Enter your HMRC credentials into the GB HMRC DPS report and Start the feed. Note: as the report is a feed, it can only pull data from HMRC and there is no risk of accidental submission. More info can be found here: Data Feeds GB HMRC DPS
- HMRC RTI FPS and EPS. User ID and Password are not imported which means there is no risk of accidentally submitting data to HMRC. However, the HMRC test service is used to test each pay run together with a test receipt. When all the steps are complete, you must enter your user ID and password in FPS and EPS Report Definitions
- Journals in Pre-approval summary2 and/or Journal files. Setup any required journals in the Pre-approval summary2 and add any journal reports e.g. Xero.
Be sure to save all the data you have created in CSV files in case you need to repeat the process for each of the following views:
- Company Setup
  - Pay Definitions
  - Report Definitions
  - Pay Schedules
  - Departments
  - Companies
- Employee setup
  - Pay Items
  - Employees
On each view, simply press the Download… button to create a file. The downloaded Pay Definition and Pay Item files will contain both system created Definitions/Items as well as the ones created in this guide. We recommend you delete system created rows.

Auto Compare pay run reports¶

When import is complete, you can use the automated compare capability to compare the generated pay run files such as:

Bank files
Pension provider files
Online submission files e.g. GB FPS or EPS

Any files in text, csv or XML formats will work.

Ensure you have your reference files ready from your existing payroll system.

Go to Pay runs
After making any changes, redo the pay run to refresh all the reports
View View the required pay run
Click on the Reports tab
Click Compare
Click Choose file and choose your reference file
Click Upload files

After a few seconds your reference file wil be uploaded and automatically compared. On the left wil be your reference file and on the right is the generated file from paiyroll®. All difference are show in Red and Green. You can easily switch between Side-by-side and Line-by-line styles for the comparison.

Going Live¶

See Parallel runs.

Before the first live payroll, be sure to add credentials to any reports, e.g.:

HMRC RTI FPS
HMRC RTI EPS
Pension

P9X. If migration is at the end of a tax year, and the first pay run in paiyroll® will be in the next tax-year, you will need to create a custom pay run before running the GB PAYE tax code changer (P9X) Data Feed. This is because P9X needs to have a last approved run of a tax year before it can run.

Note

Before you approve the first payrun, you will notice that updated RTI PayIds will be sent. This switches the information held by HMRC system from pre-imported PayIds to the new paiyroll® PayIds.

Congratulations, paiyroll® is now ready.

Notes¶

National Insurance calculation¶

Sage50 uses the older manual table method for NI. paiyroll®, together with most payroll vendors, uses the newer automated exact percentage method. This results in a difference in the NI calculated. For weekly schedules, some workers are the same, but others are up to 6p different (up/down). This is higher for monthly. HMRC accepts both methods.