How do I format a cost import file?
PowerSteering’s Cost Import allows you to import cost data from other systems and add the cost data to a project without manually entering each cost line item. The source file for your import generally starts as an Excel spreadsheet which is then saved in text (tab delimited) (*.txt) file format. This article describes how to create the import file for uploading cost data into PowerSteering. See the article How do I import cost data? for more information about the steps for completing the import process.
Note: Before importing cost data, you will need to:
- Coordinate unique IDs between the cost data and the work items in PowerSteering.
- Turn on the Costs module for any work item(s) to which you will load cost data (set Control Costs to "Yes"). If a line item in the cost file has its work item with the Costs module off, the line item will fail (though other, valid line items will load).
- Ensure that one or more Cost Loader Templates have been created.
- Create a properly-formatted cost load file.
See the Excel file attached to this article for an example cost import file. Note: Excel files must be saved in text (tab delimited) (*.txt) format before being imported into PowerSteering.
1. Create Header Information
- Open an Excel Spreadsheet
- Enter header information.
Header Column Names
- Unique Project ID
- User cost tag
2. Enter Cost Data
- Enter import information as needed into the Excel spreadsheet.
Below is a list of the data elements that are required to create a cost 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.
Unique Project ID (Required): The ID of the project for which the cost was incurred. The Unique Project ID value must map to the field configured in the Cost Loader Template. See the article How do I add a new cost loader template? for more information.
Date (Required): This identifies the date the cost was incurred. The proper format for this field is MM/DD/YYYY. If you cannot format this field using the date options in Excel you will want to enter this information as text.
Description: The information you enter in this field will display on the cost page of PowerSteering.
Cost: Either "actual" (or the letter "a") or "estimated" (or the letter "e"). If there is no value, the cost type (actual or estimated) will be determined by the Default Cost Type setting at the time the cost file is uploaded
Amount (Required): The amount of the incurred cost.
User cost tag: If used, enter the user cost tag.
Activity: This identifies the project activity on which the cost was incurred; this value must match the Activity values that are already established in PowerSteering.
2.1. Unique Project ID (Project ID)
- If Project ID is selected for the Work column in the cost 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.2. Unique Project ID (PowerSteering ID)
- If Powersteering ID is selected for the Work column in the cost 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.3. Unique Project ID (Custom Field)
- If Custom Field is selected for the Work column in the cost 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.
- The acceptable activity codes for this field are visible through the tag values of the Activity tag 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 in text (tab delimited) (*.txt) file format.
Note: See the article How do I import cost data? to learn the steps to uploading your newly saved file into PowerSteering.