Rename CSV Column Heading in Powershell Using Select-Object Not Working

Question

I am working to process a raw CSV file given to my team via SFTP and update the format to the correct format for our system needs. I've been requested to script this via PowerShell so the script can be scheduled for daily runs on a non-technical team member's local machine.

To rename the column headings, I've routinely used the "Select-Object" expression in other projects. However, for some reason it is not working in this use case. The script runs to completion and saves the final file, but is maintaining the original headings.

Using the Select-Object approach to rename two columns from "Email" to "EMAIL_ADDRESS" and "EmployeeID" to "SrchKeyEMPLID" is not working. The input csv file is formatted as:

EmployeeID	Email
123	123@comp.com
456	456@comp.com

The code is:

$csvPath = 'file_path_raw_file.csv'
$tempPath = 'file_dropping_null_rows.csv' ##needed for validations
$pitstopPath = 'file_dropping_unneeded_cols.csv'
$finalPath = 'final_file_sys_load.csv'

Import-Csv $csvPath | Where-Object {
    $_.PSObject.Properties.Value -notcontains $null -and $_.PSObject.Properties.Value -notcontains ""
} | Export-Csv $tempPath -NoTypeInformation

Import-Csv $tempPath | Select-Object Email,EmployeeID | Export-Csv $pitstopPath -NoTypeInformation 

$formatFile =  Import-Csv $pitstopPath
$formatFile | Add-Member -MemberType NoteProperty -Name 'EMAIL1_NOTIFY' -Value 'Y'
$formatFile | Select-Object -Property @{Name = 'EMAIL_ADDRESS'; Expression ={$_.Email}},@{Name='SrchKeyEMPLID';Expression={$_.EmployeeID}}
$formatFile | Export-Csv -path $finalPath -NoTypeInformation

$checksBals = Import-Csv $finalPath
$header_values = $checksBals[0].PSObject.Properties.name
$header_values

Answer 1

You're creating new objects using the desired property names in:

$formatFile | Select-Object -Property @{Name = 'EMAIL_ADDRESS'; Expression =....

But you're never storing those changes in a CSV, it's just getting outputted. So $pitstopPath and $finalPath are exactly the same, no changes made. What you should've done is pipe the output from the mentioned Select-Object line to Export-Csv $finalPath.

Leaving that aside, you're doing so many unneeded operations for no specific reason, your code could be as simple as:

$csvPath = 'file_path_raw_file.csv'
$finalPath = 'final_file_sys_load.csv'

Import-Csv $csvPath | ForEach-Object {
    if ($_.Email -and $_.EmployeeID) {
        [pscustomobject]@{
            EMAIL_ADDRESS = $_.Email
            SrchKeyEMPLID = $_.EmployeeID
            EMAIL1_NOTIFY = 'Y'
        }
    }
} | Export-Csv $finalPath -NoTypeInformation