HowTo: Export / Import ProjectWise Environments

Be sure to check out my Scripting4Crypto initiative. It’s a fun way to get into using cryptocurrencies all while getting your PowerShell needs met.

In this post, I want to step through the process of exporting and importing a ProjectWise Environment. These two new cmdlets offer a way for ProjectWise Administrators to:

• copy an environment from one datasource to another
• update or add attributes to an existing environment
• add or modify an interface

We will be using the following three cmdlets, two of which were added to the PWPS_DAB module in version 1.12.4.0. At the time of this post, I am using version 1.12.8.0. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

• Export-PWEnvironments
• Import-PWEnvironments
• New-XLSXWorkbook

Export-PWEnvironments

The following was taken from the help for this cmdlet.

This is intended to be a smarter and easier to edit format than the AAM file currently used for environment definitions.

Column types supported are Text, Int, Numeric and DateTime. All column types will be lumped into one of those categories.

Document Code Definitions and Interfaces are also exported.

There are only two parameters available with this cmdlet.

1. OutputFileName
   • This is an optional parameter. When an OutputFileName (and path) are provided, an Excel file will be generated containing the Environments, Attribute Definitions, and Interfaces for one or more Environments within the current ProjectWise datasource.
   • If this parameter is excluded, the information will be output to an array of Datatables (Environments, EnvironmentAttributes, Interfaces).
2. Environments
   • This is also an optional parameter.  You can specify one or more Environment names to be reported on.
   • When excluded, all Environments will be included with the export.

The following will export the Simple environment out to the Environments.xlsx file.

$OutputFileName = 'd:\temp\export\Environments.xlsx'
$PWEnv = 'Simple'

$Splat_ExportPWEnv = @{
    Environments = $PWEnv
    OutputFileName = $OutputFileName
}
$exportPWEnv = Export-PWEnvironments @Splat_ExportPWEnv -Verbose

The following show the three worksheets exported to the Excel file.

If there were multiple Environment names specified, or if all Environments were exported, there would be additional entries in the Environments worksheet.

The EnvironmentAttributes worksheet contains all of the Environment column definitions for each environment reported on. Notice the dateapproved attribute is selected, we will be updating this attribute on import.

The Interfaces worksheet contains all Interface definitions configured for each Environment.

In the following, we will export the information to an array of datatables. Then take a look at the names of the three datatables which correspond with each of the worksheets within the Excel workbook.

# Export to an array of datatables.
$exportPWEnv = Export-PWEnvironments -Environments $PWEnv -Verbose

# List the datatable names.
$exportPWEnv.TableName

The DateApproved Attribute Settings Prior to Import

You see we have the Simple environment selected in the left pane and the DateApproved in the right pane of the ProjectWise Administrator client.

Within each of the tabs in the attribute properties dialog, you see there haven’t been any settings applied.

Update the Attribute Values

How you add / modify the attribute property values, will depend on how you exported the Environment data. If you exported the data to an Excel workbook, you can simply open the workbook and make edits within each of the worksheets.

If you exported the data to datatables, you can modify the values in each datatable. However, you will still need to export the datatables to an Excel workbook to use within the Import-PWEnvironments cmdlet.

The following will export the datatables to an Excel workbook.

# Export datatables to an Excel workbook.
New-XLSXWorkbook -InputTables $exportPWEnv -OutputFileName $OutputFileName -Verbose

Keep in mind, not all properties can be updated using this method.

• Triggers, defaults and lookups can be added, modified or removed.
• Triggering columns can be easily changed.
• Edit control types, format strings, etc. can all be changed.
• You cannot change the size.

DateApproved Attribute Values to be Updated

The following shows the updated attribute values (highlighted) which will be applied during the import process.

Import-PWEnvironments

The following was taken from the help for this cmdlet.

This is intended to be a smarter and more flexible alternative to using AAM files to maintain environments.

Column type keywords supported are text, int, numeric and datetime.

You can update existing environments such as adding attributes by adding lines to the spreadsheet. You can change properties for existing attributes by changing the appropriate columns in the spreadsheet.

Triggers, defaults and lookups can be added, modified or removed.
Triggering columns can be easily changed. Edit control types, format strings, etc. can all be changed.

Interfaces can be added or replaced by changing the Interfaces tab.
Interface elements can be added or moved or changed.

Document code definitions can be modified or removed.
Use the -ApplyDocumentCodeDefintion switch.

Debugging Import:
Adding columns works well with SQL Server and Oracle. If table creation on new environment fails in either SQL Server or Oracle, this probably has to do with a reserved word being used, an invalid column name or an invalid width for a column. Debug by taking the displayed DDL for the new table and executing it in SQL Plus (change the table name). Oracle should tell you the issue in an interactive session as will SQL Server Management Studio. Then, just update the spreadsheet and try again.

Now that we have updated the attribute properties, we can import them in by using the Import-PWEnvironments cmdlet which will update the DateApproved attribute.

# Import the Environment information.
$Splat_ImportEnv = @{
    InputFileName = $OutputFileName
    Environments = $PWEnv
}
Import-PWEnvironments @Splat_ImportEnv -Verbose

The following shows that the DateApproved attribute properties have been updated.

---

This is a basic example of what can be accomplished using the Export-PWEnvironments and Import-PWEnvironments cmdlets.  I would suggest delving into these cmdlets and learning all of what they can do. It will make administering your Environments much easier.

---

Experiment with it and have fun.

Hopefully, you find this useful. Please let me know if you have any questions or comments.  If you like this post, please click the Like button at the bottom of the page.