Replace Output of Tree Command with Spaces and Tabs

Question

So there's a problem I am trying to solve and I don't know if this is the way to go about it.

Basically I have a folder structure that looks like:

\---folder
    \---subfolder1
        +---subsub1
        |   +---subsubsub1
        \---subsub2
            \---subsubsub2

And I want it to look in excel like this: link to excel screenshot

I am aware that the tree command in Powershell or command prompt gives the above text output, which is how I got it. I am trying to now format the output of the command so that it contains spaces and tabs instead of the +'s, 's, |'s and -'s that it has. Then I can import this into excel to get the output I'm looking for in that screenshot

In my PS script, I am currently able to replace the -'s with spaces but the +,\ and | symbols turn out to be special characters that aren't replaced as easily.

It's possible that what I'm trying to accomplish can be done through much easier means, and if so I'm open to ideas, but this is the way I've been trying to approach this.

Here's what I have so far for Powershell code:

$filename = "test.txt"
tree /a > $filename
get-content $filename | %{$_ -replace "-"," "} 
get-content $filename | %{$_ -replace [RegEx]::Escape('< SharedPassKey=123456789abcdefghi/JKLM+nopqrst= />'),'< SharedPassKey=123456789abcdefghi/JKLM.nopqrst= />'}

Some things that I run into so far:

• Unable to edit/replace string value with plus symbol "+" in text file via PowerShell
• https://learn.microsoft.com/en-us/dotnet/standard/base-types/regular-expression-language-quick-reference

Answer 1

As in my comment, columns are defined by the objects properties when using Export-Csv or Export-Excel. If you were to replace the leading spaces, slashes, pipes, etc with tabulations the end result after exporting to CSV or Excel would not look like the spreadsheet you've added to your question, the export would be a one column file and each cell would have a different padding (depending on the nesting of the files).

In my opinion, even though more code is required, is to use recursion instead of regex replace for this. I'll use my /etc/ folder for this example.

• So, step by step, define a Recursive function to scan the directories first:

function Get-FolderRecursive {
[cmdletbinding()]
param(
    [string]$Path,
    [int]$Nesting = 0,
    [switch]$Force
)
    
    $outObject = {
        param($Nesting, $folder)
        
        [pscustomobject]@{
            Nesting   = $Nesting
            Hierarchy = $folder.Name
        }
    }
    
    if(-not $Nesting)
    {
        $parent = Get-Item -LiteralPath $Path
        & $outObject -Nesting $Nesting -Folder $parent
    }

    $Nesting++

    $folders = if($Force.IsPresent)
    {
        Get-ChildItem -LiteralPath $Path -Directory -Force
    }
    else
    {
        Get-ChildItem -LiteralPath $Path -Directory
    }

    foreach($folder in $folders)
    {
        & $outObject -Nesting $Nesting -Folder $folder
        
        $PSBoundParameters.Path = $folder.FullName
        $PSBoundParameters.Nesting = $Nesting
        Get-FolderRecursive @PSBoundParameters
    }
}

• Once the function is defined, we can store the result of in a variable:

PS /etc> $result = Get-FolderRecursive . -ErrorAction SilentlyContinue

These are a few lines of how the resulting object[] looks:

PS /etc> $result | Select-Object -First 10

Nesting Hierarchy
------- ---------
      0 etc
      1 acpi
      2 events
      1 alternatives
      1 apache2
      2 conf-available
      2 mods-available
      1 apm
      2 event.d
      2 resume.d

• Now that we have the folder hierarchy stored in a variable we can manipulate this object[] to get the desired output, which is compatible with Export-Csv and Export-Excel:

$maxNesting = ($result.Nesting | Measure-Object -Maximum).Maximum

$output = foreach($item in $result)
{
    $out = [ordered]@{}
    foreach($z in 0..$maxNesting)
    {
        $out["Column $z"] = ''
    }
    $out["Column $($item.Nesting)"] = $item.Hierarchy
    [pscustomobject]$out
}

• If we inspect the first few lines of $output this is how it looks:

PS /etc> $output | Format-Table -AutoSize
                          
Column 0 Column 1                   Column 2               Column 3                      Column 4
-------- --------                   --------               --------                      --------
etc                                                                                      
         acpi                                                                            
                                    events                                               
         alternatives                                                                    
         apache2                                                                         
                                    conf-available                                       
                                    mods-available                                       
         apm                                                                             
                                    event.d                                              
                                    resume.d                                             
                                    scripts.d                                            
                                    suspend.d                                            
         apparmor                                                                        
                                    init                                                 
                                                           network-interface-security    
         apparmor.d                                                                     

---

In case you're interested, I created a module that uses a modified version of this function. It produces the same output as tree in addition to the folder size: PSTree.