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 1.16.3.0, 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 1.16.3.0. 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

Worksheet 2:

The second worksheet will contain company address information.

• Worksheet name: lt_CompanyData

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.

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.

$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.

Summary

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.