Import and Export Data using Microsoft Excel

Timephased attribute data is displayed from the Financials tab in the Projects module. A two-way data transfer capability is provided that can help you view and edit this data using Microsoft Excel. If multiple currencies are enabled, your personal currency will be used to render the data in the Microsoft Excel worksheet.

The individual line items in the Financials tab show data that contains timephased attributes (including mapped and calculated timephased attributes) in the upper pane; associated details for each attribute is shown in the lower pane. All three types of timephased data can be exported; only timephased attributes can be imported.

Tabs in the Financials tab can be exported individually or as a group. Data is always exported into a single Microsoft Excel worksheet. A separate row is created for each value in the Financials tab, up to 255 total columns. You can modify the timephased attribute data in Microsoft Excel; once finished, you can import changes to timephased attributes (but not mapped or calculated timephased attributes) back in.

The first screen shot shows rolled-up timephased data (such as that which appears in the upper-pane of the Financials tab) that has been exported to Microsoft Excel:

The second screen shot shows detailed timephased data (such as that which appears in the lower-pane of the Financials tab) that has been exported to Microsoft Excel:

After the data has been exported to Microsoft Excel, you can add new values (rows) to the timephased attributes. When you import data from Microsoft Excel, you can replace the existing data or you can append the updated data. The following columns appear in every datasheet that is exported to an Microsoft Excel worksheet:

TPA-XML The unique system name of the timephased attribute.
TPA-Display The display name of the timephased attribute, for example, TPA Labor Costs.
Units The units for the attribute, for example, Currency.
Dimension-XML The unique system name of the dimension for the timephased attribute.
Dimension-Display The display name of the dimension for the timephased attribute, for example, Plan.
Start Date The start date for the timephased attribute.
End Date The end date for the timephased attribute.

If you change the names of columns or data types in the spreadsheet, you will be unable to import the data because columns and data types are mapped to specific values in the Financials tab. For example, if you delete or rename a of the columns in the exported spreadsheet and then attempt to import it, you will see this error:

If a timephased attribute value is added that is not found, you will see this error:

Exporting financials data using Microsoft Excel

You can view and modify timephased attribute data in Microsoft Excel.

To export financials data using Microsoft Excel

Open the Projects module.
Select a work item.
Select the Financials tab.
Right-click the work item in the work item tree and select Export to Excel.
In the Export to Excel dialog box, specify the export parameters.
Select Include Details to include details for each timephased attribute in the Financials view.
Select Export All Tabs to include data for each tab in the Financials tab.
Select Export To Existing Workbook to export to a Microsoft Excel file that already exists. Specify the file name and the name of the worksheet in the Microsoft Excel file into which you want to export Mariner 2008 data.
Click Save.

Importing financials data using Microsoft Excel

You can import changes made to timephased attribute data in Microsoft Excel back into the Financials tab.

To import financials data using Microsoft Excel

Open the Projects module.
Select a work item.
Select the Financials tab.
Right-click the work item in the work item tree and select Import from Excel.
In the Import from Excel dialog box, select a Microsoft Excel file.
In Worksheet, type the name of tab in the Microsoft Excel file that you want to import to Mariner 2008 as a datasheet.
From the Period drop-down, select the time period into which data in the spreadsheet is divided.
Under Add or Replace, choose whether you want to add the data from the Microsoft Excel file to existing Mariner 2008 data or if you want to replace existing Mariner 2008 data with the data from the Microsoft Excel file.
Click Save.