Copy Paste Payroll Transactions from Excel

Copy and Paste Payroll Transactions from Excel is a Human Resource and Payroll feature added to Microsoft Dynamics GP in the October 2021 release.

In Microsoft Dynamics GP you can now copy and paste payroll transactions from Excel straight into the Payroll Transaction Entry window similar to our General Ledger and Payables Transactions Copy and Paste features. This is a great feature which makes it easier for those who may have an external time keeping system that can export data to Excel in the required format for pasting or for those who might currently be using Integration Manager to enter a larger payroll transaction.

TIPS AND TRICKS:

Baseline testing showed best results with 1000 or less transaction lines. 2000 transaction lines processed within about 8 minutes and will vary depending on the environment. For those with larger numbers eConnect may be a better solution, otherwise it is best practice to break up the data and paste it into separate smaller batches for best results. As you can see in our examples below if you did 4 batches of 1000 transactions it could take 8-12 minutes total. Versus doing 4000 all in the same batch could take 20+ minutes.

Performance can vary widely due to environmental factors and the number of 3rd Party Add-ins installed on top of our code. Copy and Paste works when you have security to the core Dynamics GP window.

Here are some baseline testing examples on SQL Server with Local Install of Microsoft Dynamics GP without any customizations or add-on products.

Transaction Count	Time Elapsed
500	1 minute
1000	2-3 Minutes
2000	6-8 Minutes
3000	10-12 Minutes (extrapolated)
4000	20+ Minutes (extrapolated)

Leaving any fields that you wish to default, don’t include those columns when you copy your range, such as departments, position or dates can help speed up the process. For example if you are using the default position on the employee, don’t put it in the excel sheet as it will be redundant and slow down the import process. Example below:

SUMMARY OF FEATURE:

Below is a quick overview of this feature. For full documentation on this feature and other new features please see the Microsoft Dynamics GP Resource Directory.

A new Paste button will be visible in the Payroll Transaction Entry window. To view this window go to the Transactions menu, point to Payroll, and click on Transaction Entry.

TIP: If the Paste button does not appear, verify that your user security set to the Microsoft Dynamics GP version of the Payroll Transaction Entry window, not the HRM Solutions or any other alternate window. Also make sure that your batch does not contain any previously entered transactions as you cannot add to existing transaction data, best practice would be to create a new batch id.

Once you have selected a batch and the Paste button is available, you can copy data from a table (excluding the column headers) using the following table format.

Employee ID	Trx Type	Code	Amount	Pay Rate	Department	Position	Date From	Date To
BARB0001	Pay Code	HOUR	40.00	20.00	SPTS	CSP	05/12/2027	05/17/2027
ACKE0001	Pay Code	SALY	8		SPTS	CSP	05/14/2027	05/14/2027
BUCH0001	Deduction	EPU	50.00				05/12/2027	05/12/2027
CHEN0001	Pay Code	HOLI	8				05/12/2027	05/12/2027

If any Validation errors or warnings occur, the UPR Transaction Paste Validation Report will print and display the errors that it has identified, and no data will Pasted. The validation report will not print if there are no errors or warnings.

Warning messages such as ‘WARNING – The pay rate amount cannot be edited.’ will appear on the Payroll Transactions Paste Validation Report but will not prevent the data from pasting. Default pay rates would be used in this situation instead of the rates on the Excel spreadsheet.

Defaults:

The Amount, Pay Rate, Department, and Position fields will default from the Employee’s setup records if left blank.
Date From and Date To will default with the values in the Payroll Transaction Entry window if left blank.
Benefit and Deduction Trx Types will use the Amount field for the dollar amount or percentage that you paste. Pasting a blank amount will use the default value from the Employee’s setup for that code.
Salary pay codes will always use a Payroll Salary Adjustment Type of Reduced Hours, and will reduce the employee’s salary by the number of hours entered in the Amount column of the Excel spreadsheet.
Columns that are not available in the Excel Format to be copied will use the defaults for the employee from the user interface. For example, State and Local Tax will default from the Employee Tax Maintenance window and cannot be pasted just like they would when manually keying the a new transaction line.

Formatting Tips:

Column	Description	Required	Accepted Values
A	Employee ID	Yes	15 Character Max; Must exist in company;
B	Trx Type	Yes	Pay Code, Deduction, or Benefit;
C	Code	Yes	6 Character Max; Must be assigned to employee;
D	Amount	No	Number must be between 0 and 9999.99; Cannot be negative;
E	Pay Rate	No	Number must be between 0 and 999999999.99; Decimal Places must follow Payroll Setup. Must be blank for Salary, Benefits and Deductions;
F	Department	No	6 Character Max; Must exist in company;
G	Position	No	6 Character Max; Must exist in company;
H	Date From	No	MM/DD/YYYY Date format;
I	Date To	No	MM/DD/YYYY Date format;