Spreadsheet import - employee data

Alerts and notices
Leave feedback

Internal Employees: Submit feedback

Contact information (optional):

Leave this blank:

Please tell us how we can make this information more helpful.


Characters left:

Change to CS Support service hours

Our Support department is closed on Friday, April 28th for a company event. However, limited Accounting CS phone support is available on that date from 9 AM - 5 PM ET due to the approaching deadline for quarterly payroll tax forms.

Links to our most popular payroll tax processing topics are available in the Alerts and notices section on the right side of most pages.

IT number: 408791

Use the Spreadsheet Import wizard to import employee data from a spreadsheet file using .XLS or .XLSX file format. You can use this method to add new employee records to a client record.

You can import setup information only one time for each employee. You cannot import changes or additional information for that employee’s setup information using the spreadsheet import. You will need to update those records using the Setup > Employees screen. Subsequent imports for the client will import only earnings information for existing employees and setup and earnings information for employees that are being imported for the first time.

Special information

Pre-import information setup

Before importing employee information, you may need to set up the following information.

Excel spreadsheet formatting requirements

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. 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.
  5. Select the worksheet within the spreadsheet file to import.
  6. Select the year for which you are importing data.
  7. 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.
    Mapping item Additional info Additional info 2 Additional info 3 Required?
    Employee ID Yes
    First Name --
    Middle Name --
    Last Name Yes
    Suffix --
    SSN/EIN --
    Type --
    Address Line 1 --
    Address Line 2 --
    City --
    State --
    Zip --
    County --
    School District --
    Municipality --
    Phone Business
    Fax
    Car
    Home
    Mobile
    Pager
    Other
    --
    --
    --
    --
    --
    --
    --
    Email --
    Payroll Schedule (Primary) --
    Payroll Schedule (Alternate) --
    Location --
    Department --
    Hire Date --
    Last Raise Date --
    Inactive Date --
    Birth Date --
    Job Title --
    Gender --
    Marital Status --
    Race --
    Direct Deposit Allocation Routing Number
    Account Number
    Account Type
    Amount
    Percent
    Status
    Yes, if Direct Deposit Allocation is mapped
    Yes, if Direct Deposit Allocation is mapped
    Yes, if Direct Deposit Allocation is mapped
    --
    --
    --
    Federal Filing Status --
    Federal Allowances --
    State Allowances <State> or <Territory> Additional Amount
    Dependents
    Filing Status
    --
    --
    --
    Accruable Benefits Beginning Balance
    Allowance
    Carryover Maximum
    Available Limit
    Annual Limit
    Per Check
    Per Month
    Used
    Accrued
    --
    --
    --
    --
    --
    --
    --
    --
    --
    Pay Item Setup <Item>
    (includes all pay items set up for the client)
    Amount
    Regular Hours
    OT Amount
    OT Hours
    DT Amount
    DT Hours
    Rate
    GL Expense
    <Month>
    <Month>
    <Month>
    <Month>
    <Month>
    <Month>
    --
    --
    --
    --
    --
    --
    --
    --
    Deduction Item Setup <Item>
    (includes all deduction items set up for the client)
    Deduction Amount
    Rate
    GL Liability
    <Month> --
    --
    --
    Employer Contribution Item Setup <Item>
    (includes all employer contribution items set up for the client)
    Amount
    Rate
    GL Liability
    GL Expense
    <Month> --
    --
    --
    --
    FICS-SS Tax Amount
    GL Liability
    GL Expense
    <Month> --
    --
    --
    FICA-MED Tax Amount
    GL Liability
    GL Expense
    <Month> --
    --
    --
    EFRICA-SS Tax Amount
    GL Liability
    GL Expense
    <Month> --
    --
    --
    ERFIA-MED Tax Amount
    GL Liability
    GL Expense
    <Month> --
    --
    --
    FIT Tax Amount
    GL Liability
    GL Expense
    <Month> --
    --
    --
    ERFUTA Tax Amount
    GL Liability
    GL Expense
    <Month> --
    --
    --
    Withholding State Yes, if importing earnings and taxes
    SIT Tax Amount
    GL Liability
    GL Expense
    <Month> --
    --
    --
    Employee State Tax <State Tax>
    (includes all state taxes, based on the client/employee addresses)
    Tax Amount
    GL Liability
    GL Expense
    <Month> --
    --
    --
    Employer State Tax <Employer State Tax>
    (includes all employer state taxes, based on the client/employee addresses)
    Tax Amount
    GL Liability
    GL Expense
    <Month> --
    --
    --
    Local Tax (Resident) Tax Amount
    GL Liability
    GL Expense
    <Month> --
    --
    --
    Local Tax (Workplace) Tax Amount
    GL Liability
    GL Expense
    <Month> --
    --
    --
    Ohio School District Tax Amount
    GL Liability
    GL Expense
    <Month> --
    --
    --
    Employer Local Tax <Employer Local Tax>
    (includes all employer local taxes, based on the client/employee addresses)
    Tax Amount
    GL Liability
    GL Expense
    <Month> --
    --
    --

    Note: 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.

  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 State Allowances as a column, the next column-selection field prompts you to select a state, etc.

  • Employee ID
  • First Name
  • Middle Name
  • Last Name
  • Suffix
  • SSN/EIN
  • Type
  • Address Line 1
  • Address Line 2
  • City
  • State
  • Zip
  • County
  • School District
  • Municipality
  • Phone
  • Email
  • Payroll Schedule (Primary)
  • Payroll Schedule (Alternate)
  • Hire Date
  • Last Raise Date
  • Inactive Date
  • Birth Date
  • Job Title
  • Gender
  • Marital Status
  • Race
  • Direct Deposit Allocation
  • Fed Filing Status
  • Fed Allowances
  • State Allowances
  • Accruable Benefits
  • Pay Item Setup
  • Deduction Item Setup
  • Employer Contribution Item Setup
  • FICA-SS
  • FICA-MED
  • ERFICA-SS
  • ERFICA-MED
  • FIT
  • ERFUTA
  • Withholding State
  • SIT
  • Employee State Tax
  • Employer State Tax
  • Local Tax (Resident)
  • Local Tax (Workplace)
  • Ohio School District
  • Ohio Local Tax

Verifying address information

If the application encountered any invalid addresses in the Column Mappings screen, it will open the Address Mapping screen and list all employees for whom the address information needs to be corrected. Use this screen to enter and look up correct address information.

  1. In the Lookup field, enter a city and state combination, separated by a comma, or enter a ZIP code.

    The application looks up the information and enters valid information in the address fields. If multiple valid entries are available, the application populates the drop-down list in the fields for which you need to select a valid entry. Examples

    • If you enter Dexter, MI in the Lookup field, the application enters 48130 in the ZIP field because that is the only valid ZIP code for Dexter.
    • If you enter Ann Arbor, MI in the Lookup field, the application finds all ZIP codes that apply to Ann Arbor — 48103, 48104, 48105, 48106, 48107, 48108, 48109, and 48113 — and lists those in the drop-down list for the ZIP field.
    • If you enter 48130 in the Lookup field, the application finds all cities that use that ZIP code — Dexter, Dover, Hudson Mills, Scio, and Webster — and lists those in the drop-down list for the City field.
  2. Complete the remaining fields and click the Update button. If all address information for the selected employee is valid, the application marks the checkbox in the Valid column and moves to the next employee record.
  3. Repeat steps 1 and 2 until all employee records have been validated.
  4. Click the Next button to continue with the import.

Selecting import options

In the Import Options screen, choose how the spreadsheet data should be imported.

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.

Diagnostics

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.

Sample spreadsheet file

The following sample spreadsheet file is available for you to download and review. The sample spreadsheet is set up with commonly used columns and sample data. You can modify the formatting, column, and data to fit your needs.

Note: If you import the sample data into a live client record, please remember to delete the imported data when you have finished.

Open the spreadsheet file, and then save the file to the location specified in the Spreadsheet field in the Import Data tab of the Setup > File Locations dialog.

Related topics

Spreadsheet import overview

Employee setup overview

Verifying client and employee addresses

Internal notes


IT#408791 says the users cannot specify the location into which to import the employees.   As a workaround, you can import employees in batches, changing the primary location each time prior to import, so the EEs go into the correct location.  (amh updated this per feedback ticket 2253).

Share This