How to dynamically list out a list of files in a directory and number them in Powershell-CodePudding

I'm writing a Powershell script that will present the user with options of SQL queries to run and grab a report on. I have a entire directory full of these queries, but not sure how to dynamically list out the names, and number them in the terminal. I know I can hard-code them, but I'd like to account for scripts being added or removed later on.

The folder structure of the project is below, and I have my methodology for calling and executing queries done and tested.

- Powershell_Script.ps1
    > Individual queries
        - Query 1.sql
        - Query 2.sql
        - Query 3.sql

Essentially, I'd like to be able to present a menu like below for users to simply read through, and select a number to run that specific script.

Individual Scripts
******************

1. 500MB Files.sql
2. 4GB Files.sql
3. Duplicate Users.sql
4. Flat space Files.sql
etc...

CodePudding user response：

You could use a Get-ChildItem on your script directory and push it to a ForEachObject Loop to sequentially number each script and display filename in the console. You can store the FullName (path) of the script in a variable at the same time that would be referenced after user input.

Write-Host "Individual Scripts"
Write-Host "******************`r`n"
$i = 0
$SQL_Scripts = Get-ChildItem -Path "[PATH TO SQL SCRIPTS]" -Filter "*.sql" |
    ForEach-Object {
        Write-Host "$($i  ). $($_.Name)" # Numbers and Write Filename to Console
        $_.FullName #Passes back full script path to Array
    }
Write-Host ""
$Number = Read-Host -Prompt "Which script do you want to run?"

$Script = $SQL_Scripts[$Number - 1]  # User Selected No. - 1  is array element w/ script path

CodePudding user response：

I do this by adding an expression field as the first in my list of Format-Table properties:

@{Label = "Index"; Expression = { 1 [array]::indexof($myArray, $_) } }

After using Read-Host to get a number, I subtract 1 to get back to the actual offset in the array.