Skip to main content

Solutions

Products

Recommended products

Questions? We are here to help.
Contact us

Purchase

Questions? We are here to help.

Resources

Integrate data with other applications

Update employee data from a spreadsheet

You can use the Spreadsheet Import wizard to update employee data using a Microsoft Excel spreadsheet. To access the wizard, select
File
,
Import
, then
Spreadsheet
. In the
Spreadsheet Import Wizard
 screen, select the client you want, select
Employees
 as the Import type, then choose
Update employee information
.

Excel spreadsheet formatting requirements

  • The employee information that you import needs to be active for the employee. For example, if you're updating the rate for a payroll item, that payroll item needs to already be active for the employee.
  • The spreadsheet needs to contain the Employee ID as the 1st 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 1 row of information for an employee (to enter rates for multiple locations or departments for the same employee, for example), enter an asterisk (*) in the Employee ID column in the subsequent rows.
note
Make sure that the spreadsheet is closed and remains closed during the import process and that it's not password protected.

Select the source file

  1. Select
    File
    ,
    Import
    , then
    Spreadsheet
    .
  2. In the
    Source Data
     screen, select the appropriate client from the
    Client name
     field.
  3. Select
    Employees
     from the dropdown in the
    Data type
     field.
  4. Select the
    Update employee information
     option.
  5. In the Import File section, select
    Browse
     to go to the file, or enter the path and file name of the spreadsheet file to import.
  6. Select the worksheet within the spreadsheet file to import.
  7. Select the year you're importing data for.
  8. Select
    Next
    .

Map 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 dropdown in the
    Template
     field. If applicable, select the appropriate template.
  2. If the spreadsheet includes column headings or other rows of data that shouldn't be imported, mark the checkbox in the Omit row column for that row. The application won't validate or import data in that row.
  3. For each column, select the column heading in the grid, then select the applicable mapping item from the dropdown in the
    Column <x>
     field above the grid. (Refer to the following section to determine the applicable mapping item.)
  4. After you've mapped all applicable columns, select
    Next
    .
  5. The application validates the spreadsheet data. If any issues are found, the invalid items are highlighted. If necessary, correct the data then select
    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
.
Employee information
Mapping item
Additional info
Additional info 2
Additional info 3
Required?
Employee ID
N/A
N/A
N/A
Yes
Location
N/A
N/A
N/A
No
Department
N/A
N/A
N/A
No
W-4 Form Year
N/A
N/A
N/A
No
Federal Filing Status
N/A
N/A
N/A
No
Federal Two Jobs Total
N/A
N/A
N/A
No
Federal Claim dependents
N/A
N/A
N/A
No
Federal Other income
N/A
N/A
N/A
No
Federal Deductions
N/A
N/A
N/A
No
Federal Allowances
N/A
N/A
N/A
No
State Allowances
<State> or <Territory>
Additional Amount
Dependents
Filing Status
N/A
No
No
No
note
  • To import state-specific W-4 information, you'll need to 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
     or
    Head of household
    , you'll 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
     then
    Employees
     screen. However, if you enter
    False
    ,
    0
    ,
    No
    , or leave that field blank, that checkbox won't be marked for the employee.
  • For Independent Contractor employees, the
    State allowances > Additional Amount
     value will import as a
    Fixed Amount
    .
Pay and deduction items
Mapping item
Additional info
Additional info 2
Additional info 3
Required?
Accruable Benefits
N/A
Beginning Balance Used
N/A
No
Pay Item Setup
<Item> (includes all pay items set up for the client)
Rate
N/A
No
Deduction Item Setup
<Item> (includes all deduction items set up for the client)
Rate
N/A
No
Employer Contribution Item Setup
<Item> (includes all employer contribution items set up for the client)
Rate
N/A
No

Review import diagnostics

The
Data Analysis
 screen shows a list of the information that will be imported from the spreadsheet and the analysis results for the data. If necessary, you can select
Back
 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 then select
Preview Selected
 or
Print Selected
.
When you're satisfied with the data that will be imported, select
Finish
.
The
Import Complete
 screen shows a summary of the information that was imported from the spreadsheet. Review the information. You can select
Print
 to show a report of the import results, or you can select
Close
 to close the Spreadsheet Import wizard.
If you're satisfied with the imported data, select
Finish
.
Internal use only
IT number 408791

This article applies to:

  • PRODUCT: Accounting CS
  • Subject: Integration