Link to home
Create AccountLog in
Avatar of Christophe
ChristopheFlag for France

asked on

Powershell try to group objects

Dear Experts,

I am working on a script that gets all content of a folder and create a word document with the result as a table format. Everything works except the sorting. I am trying to use group-object in a table without success.

The result I would like is a table with files grouped by their directories.
I tried to modify sorting or add group but I can'tdo it.

The result I have now:
User generated imageAs you can see, the list of files in test\bureau\test is stopped by the folder "Carte" because of sorting.
I would like to have the files grouped by their parent folders with keeping alphabetical sorting. Do you think it is possible?

please see below my script:

# add a background color to directory rows
Function Add-DirBackground
{
   [CmdletBinding()]
   Param (
      [Parameter(Mandatory, ValueFromPipeline)]
      [string]$Line
   )
   Process
   {
      If ($Line.Contains("*dir*"))
      {
         $Line = $Line.Replace("<tr>", "<tr class=""dir"">")
         $Line = $Line.Replace("*dir*", "")
         
         
      }
      Return $Line
   }
}

$head = @"
 <Title>$Foldername - Folder structure</Title>
<style>
body { font-family:Calibri;
       font-size:11pt; }
td, th { border:1px solid #ddd;
         border-collapse:collapse;
  width: 160px;
 }

th { color:white;
     background-color:#344D59;
     padding-top: 12px;
     padding-bottom: 12px;
     font-size:11pt; }

table { width:1050px; margin-bottom:4px;

border-collapse: collapse
}
h2 {
 font-family:Arial;
 color:#212E53;
 font-size:14pt;
}

h4 {
 font-family:Arial;
 color:#212E53;
 font-size:12pt;
}

.dir {
  background-color: #B8CBD0;
  font-weight: bold;
  font-style: italic;
  font-size:11pt;


}

</style>
"@

# Folder select

Add-Type -AssemblyName System.Windows.Forms
$FolderBrowser = New-Object System.Windows.Forms.FolderBrowserDialog
$FolderBrowser.Description = 'Select the folder containing the data'
$result = $FolderBrowser.ShowDialog((New-Object System.Windows.Forms.Form -Property @{ TopMost = $true }))
if ($result -eq [Windows.Forms.DialogResult]::OK)
{
   $FolderBrowser.SelectedPath
}
else
{
   exit
}

# Variables

$path = $FolderBrowser.SelectedPath
$Foldername = Split-Path -Path $FolderBrowser.SelectedPath -Leaf
$outputfile = "$path\$Foldername.html"
$docfile = "$path\$Foldername" + "-Folder index.docx"
$Folders = get-childitem -Path $path -recurse | where-object { $_.PSIsContainer }
$Files = get-childitem -Path $path -recurse | where-object { -not $_.PSIsContainer }

# Request

$Data = Get-ChildItem -Recurse $path | Sort-Object fullname | Foreach {
   $name = $_.fullName
   #$name = $_.fullName.replace($path,$_.parent)
   $parent = Split-Path $name -parent
   $node = Split-Path $name -leaf
   $dir = $_.PSISContainer
   if ($dir) { $node += '*dir*' }
   $level = ($name.ToCharArray() -eq "\").Count
   [PSCustomObject]([ordered]@{
         level  = $level
         dir    = $dir
         parent = $parent
         node   = $node
      })

}

# Output to HTML

$Data |
Select-Object -Property @{ Name = 'Path'; Expression = { $_.Parent } },
           @{ Name = 'File name'; Expression = { $_.Node } },
           @{ Name = 'Comments'; Expression = { $_.Null } },
           @{ Name = 'Doc'; Expression = { $_.Null } } |
ConvertTo-Html -head $head -Body "<h2>Folder name: $($Foldername)</h2> <td><h4>Folders: $($folders.Count) / Files: $($Files.Count)</h4></td>" | Add-DirBackground | out-file $outputfile

# Convert html to docx

[ref]$SaveFormat = "microsoft.office.interop.word.WdSaveFormat" -as [type]
$word = New-Object -ComObject word.application
$word.visible = $false
$Selection = $Word.Selection
$doc = $word.documents.open($outputfile)


# change page orientation to landscape
$doc.PageSetup.Orientation = 1

# Set margins size
$doc.PageSetup.LeftMargin = 25
$doc.PageSetup.RightMargin = 25
$doc.PageSetup.TopMargin = 25
$doc.PageSetup.BottomMargin = 50

# set top row to repeat across pages
$doc.Tables[1].Rows[1].HeadingFormat = -1


$doc.saveas([ref]$docfile, [ref]$SaveFormat::wdFormatDocumentDefault)
$doc.close()
$word.Quit()
$word = $null
[gc]::collect()
[gc]::WaitForPendingFinalizers()

# Remove HTML file

Remove-Item $outputfile -Force

# open Doc file

ii $docfile

Open in new window


Thank you in advance for your help
Christophe
Avatar of Qlemo
Qlemo
Flag of Germany image

I would exclude directories from the listing - unless you really want to have those. Without, sorting is easy:
$Data = Get-ChildItem -Recurse $path -Files | Sort-Object DirectoryName, Name | Foreach { 

Open in new window

Avatar of Christophe

ASKER

thank you for your answer Qlemo.

unfortunately, I have to keep directories in the list. It is an important information for end users.
Then use this:
$Data = Get-ChildItem -Recurse $path | Sort-Object PsParentPath, {!$_.PsISContainer}, Name | Foreach {  

Open in new window

Thank you for your help. I appreciate it but unfortunately, it does not work.
In the list I have folders first and then the files.
User generated image
Yes, of course. If you want the directories listed, you'll need to decide whether they appear at the start or end of the parent folder list. You didn't want them to be in the correct alphabetic order, because than folders and subfolders get mixed into files.

Maybe you should an example of desired output to clarify what you are after.
Thanks!

The result I have:
User generated image
the result I would like:
User generated image
Thank you again for your help
Christophe
I cannot see a consistent sort order in your example - TEST*, then Test*, then B*, ... all in the same folder.
And how should it look like if there are two folders?
Sorry, please see below the correct pictures.

The result I have:
User generated image
The result I would like to have:
User generated image
"Carte" is a subfolder of "Asafo" folder

Thanks!
Christophe
Assume there is another Asafo folder (Asafo\Asafo) - where to place that, and where its content?
I think it should be here:

User generated image
And if the Subfolder Asafo contains a subfolder "test", it shoud be like that:

User generated image
I think you might be looking for
Sort-Object -Property @{Expression = ({If($_.PSISContainer) {$_.FullName} Else {Split-Path $_.FullName -parent}})}, {!$_.PSISContainer}, {$_.FullName}| 

Open in new window

Thank you Dustin, I just tried your solution and it works perfectly for folders and subfolders. Do you think it is possible to have the files alphabetically sorted?
ASKER CERTIFIED SOLUTION
Avatar of Dustin Saunders
Dustin Saunders
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
It works perfectly!

Thank you for your help. I never would'nt find the solution myself.
You can simplify that sort expression:
Sort-Object {If($_.PsIsContainer) {$_.FullName} Else {$_.DirectoryName}}, {!$_.PsIsContainer}, FullName

Open in new window


thank you guys for your help!