#PowerShell, #PowerWiseScripting, #ProjectWise, PWPS_DAB

HowTo: Use New-PWFoldersFromSpreadsheet

In this post, we will be using information imported from an Excel Workbook to create folders within a ProjectWise datasource. There are six required columns to be included in the Excel Workbook: FullPath, NewParentPath, Description, Storage, Environment, Workflow. 

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.23.5.0. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

  • New-PWFoldersFromSpreadsheet

Excel Workbook (Spreadsheet)

The provided Excel Workbook must contain the following columns for the cmdlet to work correctly.

FullPath – The path of the folder to be created. The FullPath value will be appended to the value in the NewParentPath column.  This is not required. If it is empty, it will be ignored.
NewParentPath – Use to specify the parent folder to create the folder contained in the fullpath column in. If this folder does not exist, it will be created. If one is not specified, the fullpath value will be used and created from the ROOT directory.
Description – New folder description to be used when included. Not required.
Storage – Storage area to be used. If one is not included, the default storage area will be used.
Environment – Environment to be used. This is not required. However, if a default environment is provided, it will be used.
Workflow – Workflow to be used. This is not required. However, if a default workflow is provided, it will be used.

The following shows the contents of the spreadsheet.

excel

Create Folders

The following explains what will occur as each line of the provided spreadsheet is processed. When a default value is used, it will be applied to all folders being created based on the particular line in the spreadsheet. 

  • Line One (1) contains the column header definitions.
  • Line Two (2), the PSNewProject folder will be created at the ROOT level.
    • The description and the Environment will be applied as listed.
  • Line Three (3), the PowerShell folder already exists.
    • The NewProject folder will be created within the PowerShell folder. 
    • The description and the Storage will be applied as listed.
  • Line Four (4), the NewParentPath column is empty.
    • The PowerShell\NewProject02 folder will be created within the ROOT folder. This will function the same as if the PowerShell folder was placed within the NewParentPath column.
    • The description and the Environment will be applied as listed.
  • Line Five (5), the PowerShell\NewProject02 already exists.
    • It was created on the previous line.
    • The Sub1\Sub1a folder(s) will both be created within the NewProject02 folder.
    • The description and the Environment will be applied as listed.
  • Line Six (6), the PowerShell\NewProject03\Sub1 folder will be created.
    • The Sub1a folder will be created in the NewProject03\Sub1 folder.
    • The description and the WorkFlow will be applied as listed.

In the following code we will import the contents of the spreadsheet and create the folders accordingly. I have included a default value for the DefaultStorageArea parameter as this is a required field.  I have also included a default value for the DefaultEnvironment parameter. I wanted all folders to have an associated environment. If one is not included within the spreadsheet, the one specified in the DefaultEnvironment parameter will be used.

<# Required fields for the Excel spreadsheet: 
FullPath, NewParentPath, Description, Storage, Environment, Workflow #>

$InputFilePathName = 'D:\powershell\NewPWFolders.xlsx'

$Splat_NewFolders = @{
InputFileName = $InputFilePathName
DefaultStorageArea = 'Storage01'
# Optional Parameters
# DefaultNewParentPath = 'Projects'
DefaultEnvironment = 'psEnvironment1'
# DefaultWorkflow = ''
}
New-PWFoldersFromSpreadsheet @Splat_NewFolders -Verbose

The following shows the verbose output to the console.

verboseoutput

ProjectWise Explorer

The following shows the various folders which were created and where. The lines correspond to the lines within the Excel Workbook.

pw

The following show a couple of the folders to demonstrate the Environment and Storage areas were associated correctly. 

example2

example1

 


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.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

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