Rename files based on lookup hashtable

Question

I have a list of .txt files inside a folder with the following naming: firstname[dot]lastname[underscore]nationality[underscore]date.txt

Examples:

charles.pearson_FR_08-11-2021.txt
lena.smith_GB_11-12-2019.txt
paul.miller_XX_08-03-2017.txt

I would like to replace the filenames of the files that have a 'XX' as nationality by the nationality coming from a lookup table.

What I have tried so far:

$sqlcn = New-Object System.Data.SqlClient.SqlConnection
$sqlcn.ConnectionString = "MyData; MyCatalog;"
$sqlcn.Open()

$sqlcmd = $sqlcn.CreateCommand()
$query = "select username, nationality from mytable"
$sqlcmd.CommandText = $query

$adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd
$data = New-Object System.Data.DataSet
$adp.Fill($data) | Out-Null

# create a lookup Hashtable
$lookup = @{}
# build up the $lookup hash
foreach ($item in $data.Tables) {
    $lookup[$item.username] = $item.nationality
}

[string]$rootPathForFiles = Join-Path -Path $env:USERPROFILE -ChildPath 'Desktop\testFolder'
[string[]]$listOfFilesToRename = Get-ChildItem -Path $rootPathForFiles -Filter '*_XX_*.txt' | Select-Object -ExpandProperty FullName
$listOfFilesToRename | ForEach-Object {
    #get the filename without the directory
    [string]$newName = Split-Path -Path $_ -Leaf 
    #NEEDED: Lookup on the hashtable and rename the file
}

It correctly lists all .txt files that have _XX_ in the name but I miss the lookup on the hashtable and the renaming.

For information, $data looks like this:

username                                    nationality
--------                                    -----------                                                
lena.jeankins                                           
paul.mirabel                                GB         
mickael.mudik                               GB         
paul.miller                                 FR    
...     

Answer 1

What you can do here is to not just select the files FullName, but instead keep the complete FileInfo object and use the .BaseName and .Extensions properties like this:

$rootPathForFiles = Join-Path -Path $env:USERPROFILE -ChildPath 'Desktop\testFolder'
(Get-ChildItem -Path $rootPathForFiles -Filter '*.*_XX_*.txt' -File) | ForEach-Object {
    $userName,$nationality,$date = $_.BaseName -split '_'
    if ($lookup.ContainsKey($userName)) {
        $newName = '{0}_{1}_{2}{3}' -f  $userName, $lookup[$username], $date, $_.Extension
        $_ | Rename-Item -NewName $newName
    }
    else {
        Write-Warning "Could not retrieve nationality for user $userName"
    }
}