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
Powershell

Avatar of undefined
Last Comment
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
Christophe
Flag of France image

ASKER

thank you for your answer Qlemo.

unfortunately, I have to keep directories in the list. It is an important information for end users.
Avatar of Qlemo
Qlemo
Flag of Germany image

Then use this:
$Data = Get-ChildItem -Recurse $path | Sort-Object PsParentPath, {!$_.PsISContainer}, Name | Foreach {  

Open in new window

Avatar of Christophe
Christophe
Flag of France image

ASKER

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
Avatar of Qlemo
Qlemo
Flag of Germany 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.
Avatar of Christophe
Christophe
Flag of France image

ASKER

Thanks!

The result I have:
User generated image
the result I would like:
User generated image
Thank you again for your help
Christophe
Avatar of Qlemo
Qlemo
Flag of Germany image

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?
Avatar of Christophe
Christophe
Flag of France image

ASKER

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
Avatar of Qlemo
Qlemo
Flag of Germany image

Assume there is another Asafo folder (Asafo\Asafo) - where to place that, and where its content?
Avatar of Christophe
Christophe
Flag of France image

ASKER

I think it should be here:

User generated image
Avatar of Christophe
Christophe
Flag of France image

ASKER

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

Avatar of Christophe
Christophe
Flag of France image

ASKER

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

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Christophe
Christophe
Flag of France image

ASKER

It works perfectly!

Thank you for your help. I never would'nt find the solution myself.
Avatar of Qlemo
Qlemo
Flag of Germany image

You can simplify that sort expression:
Sort-Object {If($_.PsIsContainer) {$_.FullName} Else {$_.DirectoryName}}, {!$_.PsIsContainer}, FullName

Open in new window


Avatar of Christophe
Christophe
Flag of France image

ASKER

thank you guys for your help!
Powershell
Powershell

Windows PowerShell is a task automation and configuration management framework from Microsoft, consisting of a command-line shell and associated scripting language built on the .NET Framework. PowerShell provides full access to the Component Object Model (COM) and Windows Management Instrumentation (WMI), enabling administrators to perform administrative tasks on both local and remote Windows systems as well as WS-Management and Common Information Model (CIM) enabling management of remote Linux systems and network devices.

27K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo