HowTo: Create File Type Association Report

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.

I recently had a request to compile a report listing all of the File Type Associations associated with each of the three document processors (Full Text Indexing, Thumbnail Extraction, and File Property Extraction) for multiple ProjectWise (PW) datasources. This report will help to standardize the File Type Associations across the multiple PW datasources. In this post, I will be demonstrating how to generate a report listing all of the Document Processors File Type Associations for an individual datasource. 

All of the ProjectWise related cmdlets are available using the PWPS_DAB module. At the time of this post, I am using version 1.29.3.0. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

• Select-PWSQL
• New-XLSXWorkbook

Get Application and File Type Association Data

Within the report we want to include the application name for any entry which has an application id specified. There is not a cmdlet available to get this information so we will need to use a SQL query.

# Get a list of all applicatons. Eliminates the need to query for each later on.
$sqlResults_Applications = Select-PWSQL -SQLSelectStatement "select * from dms_appl"

Next, we will use a SQL query to get the File Type Association data as there is not a cmdlet to do this at the time of writing this post.

# Get all File Type Associations. 
$sqlResults_FileTypeAssociations = Select-PWSQL -SQLSelectStatement "select * from dms_ftypeassoc"

The next step is to add a tablename to the datatable returned from the second SQL query. This will be used later when we export the data to an Excel file. The worksheet will match the tablename. We will also be adding two new columns to the datatable. One is for the name of the File Type Association type and the other is for the application name. 

# Adding a table name so when exported to Excel, the worksheet will have a meaningful name.
$sqlResults_FileTypeAssociations.TableName = "FileTypeAssocations"
# Adding columns for File Type Association (Type) and Application Name (Application). 
$sqlResults_FileTypeAssociations.Columns.AddRange(@("Type", "Application"))

You can use the following to verify the updates to the datatable took effect.

# Use the following to view the columns within the datatable.
$sqlResults_FileTypeAssociations.Columns.columnname
# Use the following to see the unique types. Should be a maximum of 3.
$sqlResults_FileTypeAssociations | Select-Object -Unique o_compguid

Update the Datatable

Now that we have the datatable setup, we can add the additional information (type, application name). We will loop through each of the rows within the datatable and add the appropriate data.

<# Loop through each of the file type associations to update the datarow to add
the appropriate type and application name. #>
foreach($row in $sqlResults_FileTypeAssociations.Rows){
    # Determine the file type association type and update the datarow.
    $type = [string]::Empty
    switch ($row.o_compguid){
        '3e8884cb-cd0a-4f9a-8e40-96f261cb4284'{ 
            $type = 'File Property Extraction'
            break
        }
        '4deaeff5-999f-44c8-a26e-b2e621304cb2'{
            $type = 'Full Text Indexing'
            break 
        }
        '681014dc-859b-4c25-ac5b-a813548a5ee9'{
            $type = 'Thumbnail Extraction'
            break
        }
    } # end switch ($row.o_compguid){...
    $row.Type = $type

    # Update the datarow with application name when the application number is greater than zero. 
    if($row.o_applno -gt 0){
        $row.Application = $sqlResults_Applications.Select("o_applno = $($row.o_applno)") | 
        Select-Object -ExpandProperty o_applname
    }
} # end foreach($row in $sqlResults_FileTypeAssociations.Rows){...

Example of the datatable after the update. Notice the Type and Application Name are filled in when appropriate.

$sqlResults_FileTypeAssociations | Out-GridView

Export to Excel

Lastly, we will export the data to an Excel spreadsheet.

$OutputFileName = "c:\temp\Export\FileTypeAssociations_$(Get-Date -Format yyyyMMddhhmmss).xlsx"
New-XLSXWorkbook -InputTables $sqlResults_FileTypeAssociations -OutputFileName $OutputFileName -Open

---

Summary

This is a pretty straight forward process. We used SQL queries to get the information we needed and generated a nice report listing the information pertaining to the File Type Associations. We did this for one datasource, however, it would not be difficult to accomplish for many.  Keep in mind that sometimes you have to use the Select-PWSQL cmdlet to get the data needed when another cmdlet is not available.

---

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.