Rename files based on lookup hashtable-CodePudding

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    
...

CodePudding user response：

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"
    }
}