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	[email protected]
456	[email protected]

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

If nothing else your Add-Member should probably come after the Select, and in the Select expressions it should be $_.Email not just $.Email. Same with EmployeeID. — TheMadTechnician
– TheMadTechnician, Commented Feb 14 at 20:19
Typo: $. should be $_., using the automatic $_ variable. I've corrected the typo to focus on the larger issues, as addressed in Santiago's answer. — mklement0
– mklement0, Commented Feb 14 at 20:22

Santiago Squarzon · Accepted Answer · 2025-02-14 22:25:26Z

2

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

edited Feb 14 at 22:25

answered Feb 14 at 20:22

Santiago Squarzon

65.5k5 gold badges26 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related