PS Script for your office

You're going to want this. It works.

DAF users check it out: https://vision.il4.afwerx.dso.mil/initiatives/3872

Synopsis

Users need a library of scripts to help them manipulate and understand their files. I've made one useful script to create a CSV of a directory's file structure, all of its files and folders, and how they are organized. I've brought these results into an Excel sheet that allows for quick searching without mucking about in shared drive folders. This will especially help in unit inspections.

Problem

Users largely don't use PowerShell, a powerful terminal for even non-admin users to work with their computer and files. Users don't know what they're missing. For example, it can take very long to search inside a large directory. The search results offer limited context. It is easy to accidentally move files around or lose them while doing this, sometimes irretrievably. What's more, units spent countless hours organizing and reorganizing these shared drives so they can comply with unit annual inspection requirements. What they don't know is they can use shell scripting to help their work and keep files safe.

Solution

It would be immensely helpful if a library of shell scripts were available to users to help fix these types of problems. I've created one that recursively reads the file structure of a directory. It counts how deep each file or folder is, and exports the results to a CSV. The top-level of the directory is in the left column, and subsequent files are in columns to the right, by depth. This allows users to really drill down and see the structure or find where files are.

Beyond that, however, I've created pivot tables and slicers from that data. Now, users can quickly filter using search terms or click among the slicer options (ie among all the folders with a depth of 3, if they know it's in that folder). This offers a significantly improved situational awareness of all files in a unit or organization. As an interactive index, it offers a quick-reference to help inform everyday work as it is accomplished. Users can find old templates quickly, and office managers can reorganize shared drives without infuriating their users that everything is in a different place.

Users could really use scripts, code snippets, and walk-throughs like this that can help them be their most efficient selves.

How-to:

The filenames-to-columns.ps1 PowerShell script text is below. It's not all displayed without actually going into the cell.??

1) Select the field, press F2 to enter edit mode, then hit ctrl+a, ctrl+c.?

2) Paste it into Notepad. In the $outputFile line (9), put the location and filename where you want the resultant new CSV to go.

3) Click File > "Save As."?For the filename, name it whatever you want without spaces, as long as it ends in .ps1.

4) You're not out of the woods yet though, you still have to change the "Save as type" field from Text document (*.txt) to "All files (.)"?This will ensure your .ps1 file extension works properly.?

5) Open the File Explorer (yellow folder application) and navigate to the directory where your filenames-to-columns.ps1 file is (the one you just saved).

6) Press ctrl+L or click in the navigation / URL bar at the top of File Explorer (not internet browser), and type: "powershell" without quotes. Press Enter. Powershell will open already pointing to this directory.

7) Now that you are in the correct .ps1 directory and have modified the output file / location inside it (step 2), type ./filenames-to-columns.ps1 - that's dot-forward_slash-[your-script].ps1.?This executes it.

8) It will ask what directory you want it to index.?Using the same ctrl+L in file explorer, or clicking in the navigation bar, you will copy the address of the file you want the script to index.

9) Go back to your PowerShell prompt (make sure it is the active window).?Right click or ctrl+shift+V, either will paste in the location you copied.?Press enter.?

10) Watch and wait. If it throws errors, ask ChatGPT or Claude how to fix your script file. Do not paste controlled information into these internet services.

...................If all goes well, there will be a nice, new CSV file waiting for you when it's done, right where you instructed it to be.?The top-level files in the first directory (the one you pointed it to) will appear in the first column on the left. Then, in turn, their child folders and child items will branch out to the right. The bottom of the barrel, deepest files in the tree are in the column all the way to the right. For each of those, you can trace back their path to the top-level directory. This is how you can make a table, pivot table, and slicers.?

Here is the PowerShell script (.ps1 file extension):

function Escape-Path {

???param (

???????[string]$Path

???)

???# Escape special characters that are interpreted as wildcard characters in paths

???return $Path -replace '\[', '`[' -replace '\]', '`]'

}

$outputFile = "C:\Users\1300332338A\OneDrive - United States Air Force\Documents\large-file.csv"

while ($true) {

???$baseDir = Read-Host "Enter the path to the base directory"

???if (Test-Path -Path $baseDir) {

???????try {

???????????Write-Host "Processing directory: $baseDir"

???????????$escapedBaseDir = Escape-Path -Path $baseDir

???????????$baseDirDepth = ($escapedBaseDir.Split('\').Count - 1)

???????????$rows = Get-ChildItem -Path $escapedBaseDir -Recurse -ErrorAction SilentlyContinue -Force | Where-Object {

???????????????$testPath = Escape-Path -Path $_.FullName

???????????????$testResult = Test-Path -Path $testPath -PathType Leaf -ErrorAction SilentlyContinue

???????????????return $testResult -or $_ -is [System.IO.DirectoryInfo]

???????????} | ForEach-Object {

???????????????Write-Host "Processing item: $($_.FullName)"

???????????????$escapedPath = Escape-Path -Path $_.FullName

???????????????$path = $escapedPath.Replace($escapedBaseDir, "").Trim("\")

???????????????$levels = $path.Split("\")

???????????????$depth = $levels.Count + $baseDirDepth

???????????????$row = [ordered]@{}

???????????????for ($i = 0; $i -lt $depth; $i++) {

???????????????????$level = "Level$($i + 1)"

???????????????????if ($i -lt $levels.Count) {

???????????????????????$row[$level] = $levels[$i]

???????????????????} else {

???????????????????????$row[$level] = ""

???????????????????}

???????????????}

???????????????[PsCustomObject]$row

???????????}

???????????if ($rows) {

???????????????$properties = ($rows | Get-Member -MemberType NoteProperty).Name

???????????????$selectProperties = $properties | Sort-Object

???????????????$rows | Select-Object -Property $selectProperties |

???????????????????ConvertTo-Csv -NoTypeInformation |

???????????????????Select-Object -Skip 1 |

???????????????????Out-File -FilePath $outputFile -Encoding UTF8

???????????????Write-Host "CSV file generated successfully: $outputFile"

???????????} else {

???????????????Write-Host "No accessible files or directories found in the specified base directory."

???????????}

???????????break

???????}

???????catch {

???????????Write-Host "Error: $_"

???????}

???} else {

???????Write-Host "The specified base directory does not exist. Please enter a valid path."

???}

}