#PowerShell, #PowerWiseScripting, #ProjectWise, Lookup Tables, PWPS_DAB

HowTo: Create / Manage Lookup Tables

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.

With the release of the PWPS_DAB module version, we now have the ability to create and manage lookup tables within ProjectWise using PowerShell.  The purpose of this functionality is to mitigate the need to create individual ProjectWise Environments to create and manage lookup tables. In this post, we will be going through the process of creating and managing lookup tables within ProjectWise. We will be creating an Excel spreadsheet containing our new lookup table definitions and values.  We will not cover using lookup tables within ProjectWise. That is a topic for another post. We will be using the following cmdlets to accomplish this task. All of the ProjectWise related cmdlets are available using the PWPS_DAB module. At the time of this post, I am using version Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.
  • Import-PWLookupTablesFromXLSX
  • Export-PWLookupTablesToXLSX

Excel Spreadsheet

The first thing we need to do is create an Excel spreadsheet containing the lookup table(s) definition information.  We will be creating two worksheets within the Excel file. Each worksheet will correspond to a table within the database. If the table exists, its content can be appended to, or replaced. If it does not exist, a new table will be created. The Excel worksheet name will be used as the table name to be created or updated. The first row within each worksheet will correspond to the columns within the table and all additional rows will contain the values.

Worksheet 1:

The first worksheet will contain a list of state and city information.
  • Worksheet name: lt_CityStateData
Excel spreadsheet containing city and state data.
Contains City and State information.

Worksheet 2:

The second worksheet will contain company address information.
  • Worksheet name: lt_CompanyData
Excel spreadsheet containing company address data.
Contains company address information.

Import From Excel

Now that we have our spreadsheet created, we can import the data. The first time the Import-PWLookupTablesFromXLSX cmdlet is run, table ‘T_LookupTableListing‘ will be created within the database. This table will track the custom lookup table(s) within the current ProjectWise datasource. The following shows the new table within the ProjectWise datasource.
Shows T_LookupTableListing table within SQL.
Lets run the Import-PWLookupTablesFromXLSX cmdlet and create the new tables. We should end up with two entries in the T_LookupTableListing table. Each entry will correspond to a new datatable within the database.
# Excel file containing datatable information.
$InputFile = 'D:\temp\MyLookups.xlsx'

# Use contents of Excel file to create new datatables.
Import-PWLookupTablesFromXLSX -InputFile $InputFile -Verbose
The following will show that the new tables were created and the contents of each. Table lt_CityStateData: Table lt_CompanyData:

Import From Excel with Append or DropFirst

The benefit of taking this approach is that you can use the Excel spreadsheet to manage the lookup table data. You can add, modify or remove entries from the spreadsheet. Then run the Import-PWLookupTablesFromXLSX again using the modified Excel spreadsheet. Include either the -Append or -DropFirst switch parameters and the corresponding datatable(s) will be updated accordingly. NOTE: The default behavior is to clear all data from the datatable and add import the data from the spreadsheet. Be sure to use the -Append switch parameter if you wish to maintain your current entries.  One caveat, is that you have the potential of adding duplicate entries. 

Export To Excel

Using the Export-PWLookupTablesToXLSX allows you to export all of your custom lookup tables to one consolidated Excel spreadsheet. You can use this as a starting point to generate new lookup tables, or to modify and update your existing lookup datatables. By default, all lookup tables listed in the T_LookupTableListing table will be exported. You can specify individual lookup tables to be exported.
# Excel file to be generated.
$OutputFile = 'D:\temp\All_LookupTables.xlsx'

# The following will export ALL lookup tables.
Export-PWLookupTablesToXLSX -OutputFile $OutputFile -Verbose
The following shows the Excel file generated. Notice that there is a worksheet corresponding to each of our custom lookup tables. excel4
$Splat_Export = @{
    OutputFile = 'D:\temp\Individual_LookupTable.xlsx'
    TableNames = 'lt_CityStateData'
<# The following will export ONLY the specified lookup table. 
You can pass multiple table names. #>
Export-PWLookupTablesToXLSX @Splat_Export -Verbose
The following shows the Excel file generated. Notice that ONLY the specified lookup table data was exported. excel3


We took a quick look at using spreadsheets to manage lookup tables within a ProjectWise datasource. There is a lot of potential and flexibility with these new cmdlets. This should make the job of creating and managing your lookup tables 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.

2 thoughts on “HowTo: Create / Manage Lookup Tables”

  1. Hi Brian,
    The above information was great but I am having a bit of trouble locating the dbase name to input into my SQL statement for a Bentley cloud hosted datasource.


    1. I am not sure what the question is. Did you import data from an Excel file? The tablename you specify will be created if it does not exist. Just be sure not to use a ProjectWise specific table name. It does not matter where your ProjectWise datasource resides, either local or hosted. Hope this helps.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.