How do I format a timesheet import file?

If your company is using a third party to collect timesheet information, that data can be imported into PowerSteering to create cost entries. You will start with an Excel spreadsheet and save it in text (tab delimited) (*.txt) file format for uploading. This article describes how to format a file to import timesheet data into PowerSteering.  See the article How do I import timesheet data? for more information about the steps for completing the import process.

Note: Before using the timesheet import, you will need to:

  • Ensure that every user to receive timesheet data exists in PowerSteering (users can be no-access users).
  • Coordinate unique IDs between the third-party billing system and PowerSteering.
  • Ensure that one or more timesheet loader templates have been created. Contact your PowerSteering administrator to understand how your timesheet loader has been designed.
  • Create a properly-formatted timesheet load file.
  • Set Control Costs to "Yes" for any work item(s) to which you will load timesheet data. This is required in order to view the loaded timesheet data, not to use the timesheet Import.

See the Excel file attached to this article for an example of a timesheet import file. Note: Excel files must be saved in text (tab delimited) (*.txt) format before being imported into PowerSteering.

1. Create Header Information

  1. Open an Excel spreadsheet.
  2. Enter header information.

Header Column Names

  1. Action
  2. Unique Person ID
  3. Unique Project ID
  4. Activity
  5. Rate
  6. Rate Code
  7. Currency
  8. Date
  9. Hours
  10. Description
  11. Error Description

2. Enter Timesheet Data

  • Enter import information as needed into Excel spreadsheet.

Below is a list of the data elements that are required to create a timesheet file. The column names displayed in bold must appear exactly as shown here to meet the criteria of the timesheet import file requirements.

The imports do not support special characters. Do not include any special characters, such as tildas, foreign keys, bullets, etc. in import files.

Action: The value in this field determines the behavior of the load if there is already a timesheet record where the new row is targeting. Use "Update" to replace the existing value with a new value. Use "Add" to enter new information into PowerSteering or sum the new value with the existing value. Use "Ignore" to leave the existing value untouched.  

Unique Person ID (Required): The ID of the person for which timesheet data is being uploaded. The Unique Person ID value must map to the field configured in the Timesheet Loader Template. See the article How do I add a new timesheet loader template? for more information.

Unique Project ID (Required): The ID of the project for which timesheet data is being uploaded. The Unique Project ID value must map to the field configured in the Timesheet Loader Template. See the article How do I add a new timesheet loader template? for more information.

Activity (Required): This identifies how the time was spent; this value must match the Activity values that are already established in PowerSteering.

Rate: This identifies the hourly rate of the user. If you leave this column blank, and do not identify a Rate Code, PowerSteering will default to the rate listed in the Standard Rate table for the role of the user.

Rate Code: This identifies which rate code you want to apply to this user's time. If you have loaded both a rate and a rate code, PowerSteering will use the rate code to calculate the cost. If you leave this column blank, and do not identify a rate amount, PowerSteering will default to the rate listed in the Standard Rate table for the role of this user.

Currency: This identifies which currency PowerSteering should use for the time being entered. If you do not enter a currency PowerSteering will use the default currency for your site.

Date (Required): This identifies the date the time was spent. The proper format for this field is DD/MM/YYYY. If you cannot format this field using the date options in Excel you will want to enter this information as text.

Hours (Required): This identifies the number of hours for the timesheet record.

Description: The information you enter in this field will display on the actual cost page of PowerSteering.

Error Description: This column is populated by the timesheet loader when errors occur. If any of your records fail, this column will display an explanation of the error.

2.1. Unique Person ID (User ID)

Unique Person ID (User ID)
  • If User ID is selected for the User column in the timesheet loader template, the Unique Person ID can be found on the user's profile page. This information can also be obtained using a Report Wizard report by selecting Id #.

2.2. Unique Person ID (PowerSteering ID)

  • If User Powersteering ID is selected for the User column in the timesheet loader template, the Unique Person ID can be found in the URL from the user's profile page. The Unique Person ID is the entire string of numbers and letters that starts AFTER the equals sign and upper case letter U. This information can also be obtained using a Report Wizard report by selecting the PowerSteering ID.

2.3. Unique Person ID (Email)

Unique Person ID (Email)
  • If Email is selected for the User column in the timesheet loader template, the Unique Person ID can be found on the user's profile page. This information can also be obtained using a Report Wizard report by selecting Email.

2.4. Unique Project ID (Project ID)

  • If Project ID is selected for the Work column in the timesheet loader template, the Unique Project ID can be found on the Summary page. This information can also be obtained using a Report Wizard report by selecting the Project ID.

2.5. Unique Project ID (PowerSteering ID)

  • If Powersteering ID is selected for the Work column in the timesheet loader template, the Unique Project ID can be found in the URL from the Summary page. The Unique Project ID is the entire string of numbers and letters that starts AFTER the equals sign and upper case letter U. This information can also be obtained using a Report Wizard report by selecting the PowerSteering ID.

2.6. Unique Project ID (Custom Field)

  • If Custom Field is selected for the Work column in the timesheet loader template, the Unique Project ID can be found on the Edit Details page. This information can also be obtained using a Report Wizard report by selecting the Custom Field.

2.7. Activity

Activity

The acceptable activity codes for this field are visible through the tag values of the tag Activity in the Administrator's area of PowerSteering. If you do not know the valid activity codes please contact your PowerSteering administrator for the information.

3. Save File

  • Save your Excel file using a text (tab delimited) (*.txt) file format.

Note: See the article How do I import timesheet data? to learn the steps to uploading your newly saved file into PowerSteering.

0 Comments

Add your comment

E-Mail me when someone replies to this comment