Complete your Vault data

2017-07-14_09-40-17This topic came up quite frequently in the last months. You have your data in Vault, but for which ever reason, there is still other data outside of Vault that shall be added to existing Vault files, folders, items, or other objects. You have the data as CSV (or Excel), but don’t know how to import this into Vault. Well, for the files you can use our dataLoader. It’s a simple and powerful tool that allows you to pick a CSV file, it does the mapping of properties and files, and then updates the file properties. It also keeps track of issues during the update process, so that you can repeat the operation for the problematic files to a later point in time.

While the dataLoader gives you comfort, performance, and other benefits, there might be situations where you need to update just a small amount of files or other objects like folders or items.

As you know we love scripting, so updating properties based on a CSV file is something that can be done via a PowerShell script. As an example, if you want to update file properties, you can take advantage of powerVault (free) and have a script like this:

Open-VaultConnection -Server "localhost" -Vault "Vault" -User "Administrator" -Password ""

$srcConds = New-Object Autodesk.Connectivity.WebServices.SrchCond[] 1
$srcConds[0] = New-Object Autodesk.Connectivity.WebServices.SrchCond
$srcConds[0].PropDefId = 0
$srcConds[0].PropTyp = "AllProperties"
$srcConds[0].SrchOper = 1
$srcConds[0].SrchTxt = ''
$srcConds[0].SrchRule = "Must"
$bookmark = ""
$searchStatus = New-Object Autodesk.Connectivity.WebServices.SrchStatus
$files = @()
do {
	$files += $vault.DocumentService.FindFileFoldersBySearchConditions($srcConds, $null, $null, $true, $true, [ref]$bookmark, [ref]$searchStatus)
} while ($files.Count -lt $searchStatus.TotalHits)

$csv = Import-Csv "c:\temp\fileProperties.csv" -Delimiter ';'
$i = 0
foreach($row in $csv)
{
	Write-Progress -Activity "Updating files..." -CurrentOperation $row.FileName -PercentComplete ($i++ / $csv.Count * 100)
	$data = @{}
	foreach($col in $row.PSObject.Properties)
	{
		if($col.Name -ne "FileName")
		{
			$data[$col.Name] = $col.Value
		}
	}
	$file = $files | Where-Object { $_.File.Name -eq $row.FileName }
	$fullPath = $file.Folder.FullName + '/' +$file.File.Name
	$file = Update-VaultFile -File $fullPath -Properties $data
}

This is a sample CSV file for this script:

FileName;Title
100083.ipt;updated via script
100084.ipt;updated via script

This script searches for all the files in Vault, reads the CSV file, and for each line in the CSV, it updates the according properties. This script is kept pretty simple, so for instance loading all the files might not be a good idea. If you know that you just need Inventor files, then it would be good to enhance the filter criteria in the search, or if you just look for files in a given folder, then you can specify this in the FindFileFoldersBySearchConditions function. If you like to know which search criteria to use, then you can easily perform a search in Vault and use vapiTrace to figure out the parameters. We need to search for the files, as we need the full path in order to work with the Update-VaultFile command-let. However, if you have the full path in your CSV, than searching the files can be omitted.

Also, this script does not have any sort of error handling, so in case the file could not be found, or the file is checked-out or permissions are missing, etc., the update will just fail. Obviously the script can be enhanced in order to take care also of these situations.

The same applies to items. Here is the script:

Open-VaultConnection -Server "localhost" -Vault "Vault" -User "Administrator" -Password ""

$csv = Import-Csv "c:\temp\itemProperties.csv" -Delimiter ';'
$i = 0
foreach($row in $csv)
{
	Write-Progress -Activity "Updating items..." -CurrentOperation $row.ItemNumber -PercentComplete ($i++ / $csv.Count * 100)
	$data = @{}
	foreach($col in $row.PSObject.Properties)
	{
		if($col.Name -ne "ItemNumber")
		{
			$data[$col.Name] = $col.Value
		}
	}
	$item = Update-VaultItem -Number $row.ItemNumber -Properties $data
}

In this case, it’s even simpler. We don’t need to search for the items. If you know the number, then you can update them right away. There are some things to take into account. You can just update user defined properties via the argument –Properties. The title for instance, is a system property, which can be updated via the special argument -Title.

Another example, is updating the properties of folders. Here the script:

Open-VaultConnection -Server "localhost" -Vault "Vault" -User "Administrator" -Password ""

$srcConds = New-Object Autodesk.Connectivity.WebServices.SrchCond[] 1
$srcConds[0] = New-Object Autodesk.Connectivity.WebServices.SrchCond
$srcConds[0].PropDefId = 0
$srcConds[0].PropTyp = "AllProperties"
$srcConds[0].SrchOper = 1
$srcConds[0].SrchTxt = ''
$srcConds[0].SrchRule = "Must"
$bookmark = ""
$searchStatus = New-Object Autodesk.Connectivity.WebServices.SrchStatus
$folders = @()
do {
	$folders += $vault.DocumentService.FindFoldersBySearchConditions($srcConds, $null, $null, $true, [ref]$bookmark, [ref]$searchStatus)
} while ($folders.Count -lt $searchStatus.TotalHits)

$propDefs = $vault.PropertyService.GetPropertyDefinitionsByEntityClassId("FLDR")

$csv = Import-Csv "c:\temp\folderProperties.csv" -Delimiter ';'
foreach($row in $csv)
{
	$data = @{}
	foreach($col in $row.PSObject.Properties)
	{
		if($col.Name -ne "FolderName")
		{
			$propDef = $propDefs | Where-Object { $_.DispName -eq $col.Name }
			$data[$propDef.Id] = $col.Value
		}
	}
	$propValues = New-Object Autodesk.Connectivity.WebServices.PropInstParamArray
	$propValues.Items = New-Object Autodesk.Connectivity.WebServices.PropInstParam[] $data.Count
	$i = 0
	foreach($d in $data.GetEnumerator()) {
		$propValues.Items[$i] = New-Object Autodesk.Connectivity.WebServices.PropInstParam -Property @{PropDefId = $d.Key;Val = $d.Value}
		$i++
	}
	$folder = $folders | Where-Object { $_.Name -eq $row.FolderName }
	$vault.DocumentServiceExtensions.UpdateFolderProperties(@($folder.Id),@($propValues))
}

Here, like for the files, we search for all folders (you can enhance the search), load all the rows from the CSV and then perform the property update. Unfortunately, powerVault does not offer a Update-VaultFolder yet, so we have to do it via regular Vault API, which makes the script more cumbersome and longer.
In this case, we have to get all the properties first, as we cannot work with the property name. We need the Vault internal property ID. Then, we have to build the PropInstParamArray argument for the UpdateFolderProperties API function. So, we cycle through all the CSV columns, except for the FolderName, and build our list of property IDs and values.

The sample for the folder property update can be also applied to custom objects and change order. Obviously the function names and arguments will change, but the logic remains the same. Via vapiTrace, you can work in Vault and see which arguments and functions are needed for performing the search and the property update.

Bottom line, you have plenty of options for doing a mass update of your data. The coolOrange dataLoader is definitively the first choice when it comes to update file properties in a secure and reliable way. However, if the amount of data is small or you want to update other objects, then some line of scripting will do too.

We hope you enjoy!!

This entry was posted in Migration, PowerShell, powerVault, Vault API. Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s