CODEWITHSUNDEEP
powershell
MISNole
MISNole

Reputation: 1062

Store Filename as separate variables in PowerShell - based on specific character

I have a vendor file that is stored in a specific format and I would like to use PowerShell to convert the file name into three separate variables which I would then use to pass to a stored procedure that would be executed.

The file format would look like:

AA_BBBBB_YYYYMMDD.xlsx

For instance, the following is an example of a current Excel file:

TX_StampingFee_20210303.xlsx

The 1st characters will generally be a US state abbreviation but not always - so it could be 2 or 3 characters long. The 2nd section is the type of fee the Excel file contains while the last section is the date in a YYYYMMDD format.

What I would like to do is have PowerShell read the file name and separate the name into three separate variables:

$State
$Fee
$Date

What I have so far will list the Excel file in the directory and then I tried to split the name up - but I believe the file extension on the name is causing an error. How can I split this file name into separate variables based off the "_" ( underscore ) character?

Foreach ($file in Get-Childitem "C:\PowerShell\Test\*.xlsx") {
    $file.name
}

$filenames = (Get-ChildItem -Path "C:\PowerShell\Test\*.xlsx" -Directory).Name

Write-Host $filenames

$chararray = $filenames.Split("_")
$chararray

Upvotes: 0

Views: 770

Answers (2)

efrec
efrec

Reputation: 1

Use BaseName rather than the Name property to get the filename without the extension.

Then, if you trust the pattern of the filenames, you can index into your array with a range to join your 1+ fee type substrings into a single string:

$filenames = (Get-ChildItem -Path "C:\PowerShell\Test\*.xlsx" -File).BaseName

if ($filenames) {
    [System.Collections.ArrayList]$fees = @()
    [System.Collections.ArrayList]$states = @()
    [System.Collections.ArrayList]$dates = @()

    foreach ($name in $filenames) {
        $chararray = $name.Split("_")
        $arrlen = $chararray.length

        if ($arrlen -ge 3) {
            $states += $chararray[0]
            $fees += $chararray[1..$arrlen-2] -join '_'
            $dates += $chararray[$arrlen])
        }
    }
}

Upvotes: 0

Doug Maurer
Doug Maurer

Reputation: 8868

You can target the basename which is the filename minus the extension. Then when you split into 3 parts you can directly put those into 3 variables.

Foreach ($file in Get-Childitem "C:\PowerShell\Test\*.xlsx") {
    $State,$Fee,$Date = $file.basename.split('_')
    Write-Host State: $State Fee: $Fee Date: $Date
}

Upvotes: 2

Related Questions