Create a folder structure from an Excel-file or similar

PowerShell Help
1

Hi,

I’m working on copying data from one AD to another and among the tasks is to organize and prepopulate empty folder structures preconfigured with correct permissions as well.

I know that I can use the New-Item -ItemType Directory -Force -Path 'Directory\SubDirectory\SubSubDirectory' to recursively create an entire straight path, but if I need to create more than one Subdirectory or SubSubDirectory in each TopDirectory I’m feeling a bit lost.

In Linux the mkdir command supports brace expansion so you could for instance do this:
mkdir -p Directory\SubDirectory-{HR,Sales,Purchasing}\SubSubDirectory{1..3} allowing me to simultaneously create three Subdirectories for different departments with each containing three numbered SubSubDirectories.

I’m getting information about the folder structure in Excel-files.

FolderStructureExcel
FolderStructureExcel897×463 40.7 KB

Example of structure:

Department
|_HR
	|_Managers
	|_Staffing
|_Sales
	|_Managers
	|_SalesStaff
	|_Economy
|_Purchasing
	|_Managers
	|_Vendors
		|_Microsoft
		|_Apple
		|_Samsung
	|_Economy

As you can see each level contains unique values, so I can’t rely on simple numbering or similar.
I’ve previously built a script for creating a template for POSH modules using a relatively simple foreach, but that reuses the same structure everytime except for the TopDirectory Name, so it feels like it doesn’t match up to this.

2

foreach is probably the way to go but can we have some sample data to understand the structure of your Excel (CSV?) document? All I see is a blurry screenshot.

3

Sorry, I can’t show the exact structure, so I hoped the example list below the blurred out screenshot would be clear enough.
I added in some dummy data in a copy instead.
The TopDirectories are the Light blue ones below the Title “Company (300)”, SubDirectories are the ones in a darker blue below the title “Department (400)”. Unit (500) is skipped entirely so finally the SubSubDirectories are the ones below “Location (600)”

FolderStructureExample
FolderStructureExample826×435 10.9 KB

I’ll also need to set permissions on two levels “Company (300)” and “Location (600)” once the folders are created.

4

OK, that’s a pain to process in PowerShell. If this was my task, I’d be dragging those cells in Excel to populate the empty ones, saving it as a CSV and keeping the PowerShell code simple:

$data = @'
Company,Department,Unit,Location
Corp1,Sales,Unit Sales DK,HR
Corp1,Sales,Unit Sales DK,Economy
Corp1,Sales,Unit Sales DK,Management
Corp1,Sales,Unit Sales DK,Staff
Corp1,Care,Accomodations,Acc High Street
Corp1,Care,Accomodations,Acc Downtown
Corp1,Care,Accomodations,Acc London
'@

$csv = $data | ConvertFrom-Csv

foreach ($row in $csv) {

    $path = "$($row.Company)/$($row.Department)/$($row.Location)"
    New-Item -Path "E:\$path" -ItemType Directory

}

For the permissions, the scripting should also be fairly simple but this is another task where having the planned paths and permissions in a CSV file will really help you. It also has the advantage of being your documentation.

This is not tested:

$data = @'
Path,Group,Permission
E:\Corp1,CORP1\Admins,FullControl
E:\Corp1,CORP1\Users,ReadOnly
E:\Corp1\Sales\Economy\,CORP1\SaleEconomyUsers,Modify
E:\Corp1\Sales\Staff,CORP1\SaleEconomyUsers,Modify
E:\Corp1\Care\Acc High Street,CORP1\AccHighStreetUsers,Modify
'@

$csv = $data | ConvertFrom-Csv

foreach ($folder in $csv) {

    $acl = Get-ACl $folder.Path
    $identity = $folder.Group
    $fileSystemRights = $folder.Permission
    $type = "Allow"
    $fileSystemAccessRuleArgumentList = $identity, $fileSystemRights, $type
    $fileSystemAccessRule = New-Object -TypeName System.Security.AccessControl.FileSystemAccessRule -ArgumentList $fileSystemAccessRuleArgumentList
    $acl.SetAccessRule($fileSystemAccessRule)
    Set-ACL -Path $folder.Path

}
1 Like
5

Thank you, it was basically what I expected.
With some hands-on work I massaged the Excel-file into something I could export as a CSV and used your ForEach as a template.
Will continue on Monday, but I feel I have a good handle on it for now.