How do I format a metric import file?

The file format for a metric upload is very easy to create but very specific in how it must be ordered and displayed. Once created the headers for your import file can be reused for future imports. The source file for your import generally starts as an Excel spreadsheet which is then saved using either a .xls or text (tab delimited) (*.txt) file format. This article describes how to create the import file for uploading metric data into PowerSteering. See the article How do I import metric data? for more information about the steps for completing the import process.

See the Excel file attached to this article for an example of how column headings need to be formatted for a metric import file.

1. Select File Preference

The first row contains the header information, subsequent rows contain the metric data to be imported.

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

There are two options for matching the data in the import file against the PowerSteering database:

Header Option 1: Match on PowerSteering ID or Project Sequence

Header Option 2: Match on Project ID

The header option you select will impact how you format your import file which is discussed in the next steps.

1.1. Header Option 1: Match on PowerSteering ID or Project Sequence

Header Option 1 matches using the PowerSteering ID which is found in the URL of the web page or the Sequence Number which is found at the top of the Summary page. The import does not use the name of the project or object type to match the data to PowerSteering.

Note: The Sequence Number is a configuration option that must be enabled for viewing by the PowerSteering team.

1.2. Header Option 2: Match on Project ID

Header Option 2 matches using the Project ID which can be found on the Edit Details page.

Note: The Project ID is a configuration option that must be enabled for editing and viewing by the PowerSteering team.

2. Create Header Information

  1. Open an Excel spreadsheet.
  2. Enter header information (must use option 1 or option 2 layout).

Header Option 1 Column Names

  1. Project Name
  2. Project's PowerSteering ID:
  3. Project's Sequence
  4. Metric Template
  5. View
  6. Beneficiary
  7. Item Name
  8. Date

Header Option 2 Column Names

  1. Project Name
  2. Project's ID
  3. Type Name
  4. Metric Template
  5. View
  6. Beneficiary
  7. Item Name
  8. Date

Note: Regardless of which header option you choose, column header information must be entered exactly as shown in the order provided. This includes matching the punctuation, spacing, and lower and / or upper case of the wording shown in these examples and lists.

2.1. Proper Date Format

The column heading for the date information must be formatted in one of the following styles. You can use a dash or slash for each of the date formats shown below:

  • Mon-YY  (Jan-14)
  • Mon-YYYY  (Jan-2014)
  • MM-YYYY  (01-2014)
  • YYYY-MM  (2014-01)
  • YYYY-Mon  (2014-Jan)
  • DD-MMM-YY (31-Jan-14)
  • DD-MMM-YYYY (31-Jan-2014)
  • DD-MM-YYYY (31-01-2014)
  • YYYY-MM-DD (2014-01-31)
  • YYYY-MMM-DD (2014-Jan-31)

Note: The date columns indicate the month and year where the metric data should be imported. For quarterly metrics you will enter the date that represents the first month / year of the quarter. For example if your fiscal year starts in April, and you wanted to enter a date to represent the first quarter of that fiscal year, you would use Apr-14 to represent the month and year of FYQ1. For yearly metrics you can use the calendar's first month.

3. Create Import Information

  • Enter import information as needed into Excel spreadsheet. A description of each type of data entry is provided in the following steps.

3.1. Project Name

Project Name

Although the project name is not used to match the PowerSteering database, it is recommended that you include this information as future reference in your import file. The project name can be found at the top of the Summary page or on the Edit Details page.

Note: A blank row in this column will cause an error during uploading, so ensure that a value is populated for every row in the column. Values do not have to be unique (i.e., 'x' could be used for all null rows).

3.2. Project's PowerSteering ID

This information can be found in the URL from the Summary page. The PowerSteering ID is the entire string of numbers and letters that starts AFTER the equals sign and upper case letter U. This is used to match data if you are using Header Option 1. This information can also be obtained using a Report Wizard report by selecting the PowerSteering ID.

3.3. Sequence Number

Sequence Number

This information can be found at the top of the Summary page. The Sequence Number is a configuration option that must be enabled for viewing by the PowerSteering team. This is used to match data if you are using Header Option 1. This information can also be obtained using a Report Wizard report.

3.4. Project's ID

Project's ID

The project ID can be found on the Edit Details page. The Project ID is a configuration option that must be enabled for editing and viewing by the PowerSteering team. This is used to match data if you are using Header Option 2. This information can also be obtained using a Report Wizard report.

3.5. Type Name

The import process will take the take the following values for the Type Name column - Work, Tollgate, Organization and / or Deliverable. The selection of the type name does not seem to impact the import but it must be one of the four values and be capitalized.

3.6. Metric Template

Metric Template

The name of the metric template appears in the Header of the Metric page and next to the word "Metric:" on the Metric page. You must match the name of the metric template exactly including case and spacing. This is used to match data if you are using Header Option 2.

3.7. View

View

The View is represented by the tabs at the top of your metric. You can only load one view per line of your spreadsheet and you must match the name of the view exactly including case and spacing. If your metric template does not contain any views you can leave this field blank but you must include the column with the header.

3.8. Beneficiary

Beneficiary

If you are using an advanced metric that has used a tag to further define metrics you will need to enter the tag values identified as the beneficiaries. You must match the name of the tag value exactly including case and spacing.

3.9. Item Name

Item Name

The Item Name represents the line item against which you are importing metric data. If there are line items that share the same name you will need to further define the line item by providing an additional identifier for the data.

3.10. Item Name (Importing Duplicates)

If a metric template has more than one item with the same name, the names of those items must be fully qualified using additional line items for description separated by the pipe symbol. Using the screen capture above as an example you can differentiate the two line items named Equipment by entering them as follows:

CAPITAL | Equipment

BENEFIT | Equipment

The pipe symbol can be padded with any number of spaces, or none.

Note: Any item that appears above the line item can be used as its descriptor. So in the above example you could also use COST | Equipment to populate the first line item and Other Expense | Equipment to populate the second line item.

3.11. Date / Number

The numeric data you enter will populate the period that corresponds to its column heading. Numbers should be entered without commas, currency symbols and / or monetary symbols such as K or M. A period is acceptable as a decimal point. If your metric is utilizing a monetary symbol such as K or M, enter data as its full value such as 30000 to represent $30k or 1000000 to represent $1M.

If you wish to load data for a metric that has no frequency or date/time relevance, you can use the column header Amount in place of the month/year.

Note: The date columns indicate the month and year where the metric data should be imported. For quarterly metrics you will enter the date that represents the first month / year of the quarter. For example if your fiscal year starts in April, and you wanted to enter a date to represent the first quarter of that fiscal year, you would use Apr-14 to represent the month and year of FYQ1. For yearly metrics you can use the calendar's first month.

4. Save File

  • Save your Excel file using either a .xls or text (tab delimited) (*.txt) file format. .xlsx and .csv file formats are not supported.

Note: See the article How do I import metric 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