Spreadsheet import - updating employee data

Show expandable text

Use the Spreadsheet Import wizard to update employee data using a spreadsheet file using .XLS or .XLSX file format. 

Choose File > Import > Spreadsheet. In the Spreadsheet Import Wizard screen, select the desired client,  select Employees as the Import type, and then choose the Update employee information option.

Excel spreadsheet formatting requirements

  • The employee information that you import must be active on the employee. For example, if you are updating the rate for a payroll item, that payroll item must already be active for the employee.
  • The spreadsheet must contain the Employee ID as the first column.
  • No blank rows can exist between employee records in the spreadsheet.
  • The spreadsheet can contain extra rows and columns of information that can be omitted during the import process in the Spreadsheet Import Wizard.
  • If you need to import more than one row of information for an employee (to enter rates for multiple locations or departments for the same employee, for example), type an asterisk (*) in the Employee ID column in the subsequent rows.

Be sure that the spreadsheet is closed and remains closed during the import process.

Be sure that the spreadsheet is not password protected.

Selecting the source file

  1. Choose File > Import > Spreadsheet.
  2. In the Source Data screen, select the appropriate client from the Client name field.
  3. Select Employees from the drop-down list in the Data type field.
  4. Select the Update employee information option.
  5. In the Import File section, click the Browse button to navigate to the file, or enter the path and filename of the spreadsheet file to import.
  6. Select the worksheet within the spreadsheet file to import.
  7. Select the year for which you are importing data.
  8. Click Next.

Mapping spreadsheet columns

Use this screen to map the spreadsheet columns to specific data fields in Accounting CS.

  1. If you saved mapping information from a prior import as a mapping template, that template will be included in the drop-down list in the Template field. If applicable, select the appropriate template.
  2. If the spreadsheet includes column headings or other rows of data that should not be imported, mark the checkbox in the Omit row column for that row. The application will not validate or import data in that row.
  3. For each column, click the column heading in the grid, and then select the applicable mapping item from the drop-down list in the Column <x> field above the grid. (See the following section to determine the applicable mapping item.)
  4. After you have mapped all applicable columns, click Next.
  5. The application validates the spreadsheet data. If any issues are found, the invalid items are highlighted. If necessary, correct the data and then click Next.

Column headings available for mapping

The following selections are available for column mapping. Note that for some headings you can map sub-categories to columns also. For example, if you assign Accruable Benefits as a column, the next column-selection field prompts you to select Beginning balance or Used, etc.

Mapping item Additional info Additional info 2 Additional info 3 Required?
Employee ID Yes
Location --
Department --
W-4 Form Year --
Federal Filing Status --
Federal Two Jobs Total --
Federal Claim dependents --
Federal Other income --
Federal Deductions --
Federal Allowances --
State Allowances <State> or <Territory> Additional Amount
Dependents
Filing Status
--
--
--

Notes:

  • To import state-specific W-4 information, you must map a column in your spreadsheet labeled with the name of the checkbox. For example, for Massachusetts, if you want to mark checkboxes for Full time student, Head of household, etc., you will need to map a column for each checkbox. Entering information in this column for an employee (such as an X, Yes, or True) will mark the checkbox for that employee in the Payroll Taxes tab of the Setup > Employees screen. However, if you enter False, 0, No, or leave that field blank, that checkbox will not be marked for the employee.
  • For Independent Contractor employees, the State allowances > Additional Amount value will import as a Fixed Amount.
Mapping item Additional info Additional info 2 Additional info 3 Required?
Accruable Benefits Beginning Balance
Used
--
--
Pay Item Setup <Item>
(includes all pay items set up for the client)
Rate --
Deduction Item Setup <Item>
(includes all deduction items set up for the client)
Rate --
Employer Contribution Item Setup <Item>
(includes all employer contribution items set up for the client)
Rate --

Reviewing import diagnostics

The Data Analysis screen displays a list of the information that will be imported from the spreadsheet and the analysis results for the data. If necessary, you can click the Back button to make changes to any of the mapping and options screens. To view a diagnostic report for any of the items listed, mark the checkbox next to that item, and then click the Preview Selected or Print Selected button.

When you are satisfied with the data that will be imported, click the Finish button.

The Import Complete screen displays a summary of the information that was imported from the spreadsheet. Review the information. You can click the Print button to display a simple report of the import results, or you can click the Close button to close the Spreadsheet Import wizard.

If you are satisfied with the imported data, click the Finish button.

Was this article helpful?

Thank you for the feedback!

Internal only

IT number 408791