Spreadsheet import - time entry 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.

For live payroll processing only

Use the time entry spreadsheet import feature to import time-entry type spreadsheets (in .XLS and .XLSX file format) in the Actions > Enter Batch Payroll Checks screen.

Special information

  • The spreadsheet that you want to import must contain the following column labels.
    • Employee ID. This must be the first column in the spreadsheet.
    • Location. The Location column must appear directly before the Department column.
    • Department. The department must be defined if employees are assigned to multiple departments.
    • Pay item. There must be at least one pay item.
  • The names of the locations and departments listed in the spreadsheet must exactly match the names specified in the application for the selected client for the import to work correctly. The names are case-sensitive.
  • Employees who are assigned to more than one department must be listed on a consecutive line in the spreadsheet for each department they are in. For example, if Employee 100 is in Department 1 and Department 2, then Employee 100 is listed in a spreadsheet row for Department 1 and listed again in the next row for Department 2.

Setting up your client for time entry via spreadsheet import

The time entry Spreadsheet Import wizard is available only for payroll schedules that are set up to use the Excel time entry method.

To modify a client's existing payroll schedule to use the Excel time entry method, follow these steps.

  1. In the Payroll Information tab of the Setup > Clients screen, click the Ellipsis Ellipsis button button in the Payroll Schedules section.
  2. Select the schedule you want to modify and click the Edit button.
  3. In the Time Entry Method section, select the Import option and choose Excel time entry from the Source field drop-down list.
  4. Click Enter to save the changes to the payroll schedule, and then click Done to close the Payroll Schedules dialog.
  5. Click Enter to save the changes to the client.

Note: You can specify Excel time entry as the default time entry method for all of a client's new payroll schedules by marking the same field selections in the Default Time Entry Method section of the Payroll Information tab.

Importing time entry data from a spreadsheet

  1. Verify that the spreadsheet to be imported is closed prior to starting the import process.
  2. Choose Actions > Enter Batch Payroll Checks and select the appropriate client.
  3. Select a payroll schedule that is set up with the Excel time entry method.
  4. In the Spreadsheet Import wizard, browse to, or enter the path for, the Microsoft Excel spreadsheet that you want to import into the Enter Batch Payroll Checks screen.
  5. Select the worksheet name from the spreadsheet currently selected in the Worksheet to import field.
  6. Click Next to map columns in the spreadsheet.

Notes

  • To expand and collapse the Navigation pane in the Spreadsheet Import wizard, click the XLS import nav button buttons.
  • Spreadsheet data cannot be imported if it was created in a version of Microsoft Excel that is more recent than the version you are currently using.
  • Password protected workbooks cannot be imported. You will need to remove password protection and close the workbook prior to beginning the spreadsheet import.
  • If any blank cells in the spreadsheet contain spaces, you may receive an error message when you attempt to import the spreadsheet. If this happens, remove the spaces using the DELETE or BACKSPACE keys, and then re-import the spreadsheet.
  • If the pay rate in the spreadsheet is different than what is currently set up for an employee in the application, the import will update the employee with the new rate from the spreadsheet.

Mapping columns in the spreadsheet

  1. Select a predefined template from the Template field on the Column Mappings page of the Spreadsheet Import wizard or create your own mapping if you are importing a spreadsheet for the first time or creating and adding a new template. Column mapping templates are especially helpful if you are exporting to Excel frequently from the same application, or importing files from the same source for multiple clients. Details.
    • Templates are user defined and categorized based on the import type that is selected on the first page of the Spreadsheet Import wizard. Only those templates that are associated with the import type are available from the drop-down list in the Template field of the Column Mappings page of the Spreadsheet Import wizard.
    • The Template field always defaults to <None>.
    • Templates include all predefined column assignments in addition to any rows that are marked to be omitted.
    • You can right-click on a template in the Template field to add, edit, or delete the item.
  2. For each column in the spreadsheet grid, you can select an item from the drop-down list. Items that appear in the drop-down lists are based on the type of data that is selected to import. For details, see the Time Entry Scenario at the end of this topic.
  3. Mark the Omit Row checkbox for any rows that you would like to exclude from the import.

Notes

  • Employee ID must be the first column in the spreadsheet for the mapping to be successful.
  • All columns are assigned as Not Used by default. Note that any columns that are assigned as Not Used are not imported.
  • Data can be modified in the data grid by clicking within a cell and entering the desired text or amount directly.
  • You can click the row buttons to the left of the grid to highlight the rows, and then press the Delete key to remove them from the grid.
  • To select more than one row at a time, press and hold the Shift key, and select the appropriate rows in the grid. Click in any data cell within the grid to edit its contents.

Time entry scenario

Typically, the client provides the accountant with a spreadsheet of the client's employees by name and/or id with the hours they worked in each department for each pay item. Instead of entering hours in the Payroll Check Entry screen for individual employees, the import process enables you to create checks for multiple employees at once. Using this method, you can specify fixed dollar amount overrides for deduction items.

If you are importing time-entry data, you may want to define columns for employee ID, employee name, regular hours, overtime hours, pay rate, accrual benefits, and so forth. It is only necessary to map pay and deduction items for specified columns to the corresponding payroll items in for the first import or when new (undefined) pay/deduction items are present in the spreadsheet. Because the client's spreadsheet format remains consistent across multiple imports, it is easy for the accountant to set up a template with predefined columns for ease of import. A typical Time Entry spreadsheet could look like this.



Sample spreadsheet for import

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 some 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 are 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

Internal notes


If the spreadsheet needs to be imported again (for whatever reason), you can't simply cancel the batch because of how ACS handles spreadsheet imports in the background.

Follow these steps to cancel the batch and re-import the spreadsheet.

  1. Suspend the batch.
  2. Pull up the batch again by selecting the Payroll Schedule.
  3. Cancel the batch.
  4. The next time you select that payroll schedule, the Spreadsheet Import wizard should display as expected.

See TFS #826108 for more information.

Share This