HowTo: Export And Update Document Attributes On Import

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 realize I have another post stepping through the process of updating document attributes (How To: Update Document Attributes Using PowerShell). However, I had a request for a method to export documents from ProjectWise and include a generated metadata report. Then import those documents into another folder and update the document attributes from the generated metadata file. I am importing the documents into another folder within the same ProjectWise datasource. However, it could easily be a folder within a different datasource. 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.18.2.0. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

• Export-PWDocuments
• Import-PWDocuments
• Get-TablesFromXLSXWorkbook
• Get-PWEnvironmentColumns
• Get-PWDocumentsBySearch
• Update-PWDocumentAttributes

Export Documents

The following will quickly step through the process of exporting documents to a local machine.  We will be generating a metadata report along with exporting the documents. The folder containing the documents has the ‘Simple’ ProjectWise Environment associated with it. So, the metadata report will contain a worksheet entitled ‘Simple’ which will contain the document attribute metadata for each document.

# ProjectWise Folder to Export documents from.
$FolderPath_Export = 'PowerShell\FilesToExport'
# Local folder to export to.
$LocalFolder = 'D:\TEMP\Export\PowerShell'
# Spreadsheet to export document metadata to.
$ExportMetadataFile = "$LocalFolder\PowerShell_Metadata_$(Get-Date -Format yyyyMMddHHmmss).xlsx"

# Using splat for readability.
$Splat_Export = @{
    ProjectWiseFolder = $FolderPath_Export
    OutputFolder = $LocalFolder
    ExportMetadata = $true
    ExportMetadataFile = $ExportMetadataFile
}
Export-PWDocuments @Splat_Export

The following shows the ProjectWise Folder and Documents to be exported. The following shows the local folder post export.

Import Documents

Now that we have the documents exported to the local machine. We will import them into a specified ProjectWise folder.

# ProjectWise Folder to Import documents into.
$FolderPath_Import = 'PowerShell\Import'

# Using splat for readability.
$Splat_Import = @{
    InputFolder = $LocalFolder
    ProjectWiseFolder = $FolderPath_Import
    ExcludeSourceDirectoryFromTargetPath = $true
    ExtensionsOfInterest = '.dgn;.pdf'
}
Import-PWDocuments @Splat_Import

The following shows the ProjectWise Folder the documents were imported in to.

Update Document Attributes

Now, we will go through the process of updating the document attributes. The metadata will be imported from the metadata report which was generated during the document export process. The following shows the content of the generated metadata report. General Worksheet: Simple Worksheet: First, we will import the contents of the metadata report into the $dts variable. The results should be a dataset containing three datatables. Each datatable corresponds to a worksheet within the Excel file.

# Import contents of the generated spreadsheet.
$dts = Get-TablesFromXLSXWorkbook -InputFileName $ExportMetadataFile

Next, we will isolate the datatable containing the document attributes. We know the we are using the ‘Simple’ environment. However, I will show you how you can grab the environment name from the ProjectWise folder. We will then get a list of all columns within the ProjectWise environment. This will be used in the document attributes update process.

# You'll want to use the table which matches the Environment name for the folder.
$Environment = (Get-PWFolders -FolderPath $ProjectWiseFolderInput -JustOne |
Select-Object Environment).Environment

# Isolate only the datatable which corresponds to the environment.
$dtAttributes = $dts | 
Where-Object TableName -eq $Environment

# Get a list of column names from the environment associated with the target folders environment.
$Columns = (Get-PWEnvironmentColumns -EnvironmentName $Environment).GetEnumerator() | 
Select-Object Name

Finally, we will loop through all of the rows in the $dtAttributes datatable and update the document attributes.

# Loop through each entry in the datatable.
foreach ($row in $dtAttributes) {
    # Get file name to be updated.
    $FileName = Split-Path $row.ProjectWisePath -Leaf

    # Get the ProjectWise document object for the current document.
    $pwDocObject = Get-PWDocumentsBySearch -FolderPath $ProjectWiseFolderInput -FileName $FileName -JustThisFolder
    if( [string]::IsNullOrEmpty($pwDocObject)) {
        Write-Warning -Message "Failed to select document '$FileName'."
        Continue
    }

    # Hashtable to populate with Attribute name / value pairs.
    $AttributesToUpdate = @{} 
    # Populate hashtable.
    foreach ($Column in $Columns.Name) {
        # Skip this column.
        if($Column -eq 'a_attrno') {
            Continue
        }

        # Populate hash table with attribute name and values ONLY when a value is included.
        if( -not ([string]::IsNullOrEmpty($row.$Column))) {
            $AttributesToUpdate.Add($Column, $row.$Column)
        }
    } # end foreach ($Column in $Columns...

    # Update attribute values for the current document.
    if( -not (Update-PWDocumentAttributes -InputDocuments $pwDocObject -Attributes $AttributesToUpdate -ReturnBoolean)) {
        Write-Warning -Message "Failed to update document attributes for $($pwDocObject.Name)."
    } 
} # end foreach ($row in $dtAttributes...

The following shows the contents of the $AttributesToUpdate variable which contains the metadata from the ‘Simple’ datatable corresponding to the ‘MyDGN_v7.dgn’ document. The following shows the updated document properties for the corresponding ProjectWise document.

Summary

To summarize what we have done, we exported ProjectWise documents from a specified ProjectWise folder to the local drive. At which time, we also generated a report (an Excel file) which contains document attribute metadata for each document exported.  We then imported the documents into another ProjectWise folder (could easily have been in another datasource) and updated the document attributes for those imported documents using the content of the Excel spreadsheet.

---

The following is a link to the full PowerShell script. HowToUpdateDocumentAttributeValues 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.