HowTo: Use New-PWFoldersFromSpreadsheet

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.

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.

The following shows the contents of the spreadsheet.

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.

ProjectWise Explorer

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

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

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.