How To: Import Time Sheets
- Patrick Reynolds
The “Time Sheets” tab allows you to import spreadsheets that you’ve either electronically gathered via a time keeping vendor, or manually entered payroll information onto a spreadsheet, both methods needing to use our import templates. Each tab has a separate focus on what it’s importing and how the information is formatted, with the payroll and vendor web entries gathering time punches via our online time keeping portal vs a file. The documentation for importing web entries can be found on the Arborsoft Online Portal portion of the wiki.
Though we have multiple import formats to accommodate various vendors, the information for our default format and example files can be downloaded below.
Arborsoft Prerequisites
In order to use the import functionality, the various fields within the import file and import ID fields in Arborsoft must be unique, and match. Though various formats will reference different fields within Arborsoft, the import ID’s of Pay Types, Service Code, Consumers, and Employees are always referenced. Below are the locations where the import ID’s are entered for those fields. Other fields within the import file (such as hours, time in, comments) do not reference existing fields within Arborsoft.
Remember that import ID’s need to be unique, but only to it’s own category. Two import ID’s both reading 1234 in separate categories is fine, but two 1234 entries both in Consumers or Employees etc will result in failure.
The Import File
Creating the import file and entering the information can both be done by a time keeping vendor electronically, or simply creating an excel sheet in one of our formats. Once the file is built, it needs to be saved as a CSV comma delimited format to be imported into Arborsoft.
Arborsoft will automatically accommodate certain variations within certain fields, which allows users some flexibility on what information is entered into the file. The variations are:
The hours field is required IF the check in/out fields are blank.
The check in/out fields are required IF the hours field is blank.
If both the hours and check in/out fields have information, the HOURS field takes priority
The pay rate set to 0 will reference the pay rate for the specified consumer>employee>service code> pay rate within the consumer module.
The pay rate set to a rate other than 0 will ignore the service code rate in the consumer module.
The pay rate field MUST have either a rate or 0, it cannot remain blank
General format notes:
The header of the import file can remain or be removed
The relationships of the Consumer/Employee/Service Codes must be established within Arborsoft if they exist in the import file
As the file is saved in CSV comma delimited, commas within the import file (such as service codes) must be removed
Though not required, we typically suggest users mirror the pay type with the import code i.e. REG as the pay type and REG as the import code as it makes the import file easier to review before importing.
Importing a Payroll file
To import a payroll file, first navigate to the Payroll module and either create a new batch, or access an existing open batch. Navigate to the Time Sheets > Payroll tab, select the format the import file will be in, and click “Import”. This will open a dialog box where you can locate the CSV file that you intend to upload.
After you select the file, the information will be added to the screen for your review to ensure everything is correct before processing. To group various columns of information to review various totals, the column titles can be dragged to the highlighted area that reads “Drag a column header here to group by that column”. With the file imported, you’re free to review the information to ensure everything is correct before processing.
If for any reason you wish to remove the imported file and the information has NOT been processed, clicking the delete button will remove all of the displayed information.
Processing the file
Once you’ve reviewed the imported entries, clicking “Process” will move the valid entries to the payroll batch, which can be viewed on the Payroll > Payroll Entry tab. Processed entries will have the “Processed” field change from N to Y, and will change from black to red. Entries that are missing required fields or that are otherwise invalid, will remain unprocessed and in black.
On the Payroll Entry tab, the entry type will display manual for manually entered time, and “IMPORT” for imported entries as shown below. From this point on you’re free to review totals and process as usual.
Common Import Issues
Failing to import some/all of a time sheet can occur for a variety of reasons, such as the import codes on the file not existing for the related consumer/employee, entries being made in the wrong format, etc. If the correction can be made in Arborsoft (such as entering a missing import code to an employees profile) you can right click and select “refresh” for the imported information to attempt to match the information again.
If you have any issues importing the file and can’t find the reason, feel free to reach out to the support team and be sure to include the file you’re attempting to import for review. Some common failure points are:
Importing the file not in CSV comma delimited format.
Commas in any fields. As the file is imported in CSV format, a comma in the service code or any other field will separate the field into two causing an error.
Missing/mismatched import id within Arborsoft and import file
Incorrect format for the field (see the file specs document for details)