Replacing csv header row

Welcome Forums General PowerShell Q&A Replacing csv header row

This topic contains 3 replies, has 4 voices, and was last updated by

4 years, 5 months ago.

  • Author
  • #17216

    Points: 0
    Rank: Member

    I'm trying to automate an ad import using an unedited csv file nightly. The problem is the data base I am exporting from has its own headers. Is it possible to replace the entire header row with another set of values?

    Example of current values Last Name,First Name, and so on.....

    Want to replace it with Ad attribute names.

    Then save the csv file out to a different file name.

    I'm kinda new to scripting so any help will be greatly appreciated!


  • #17217

    Points: 0
    Rank: Member

    Sure. There are a couple of ways to go about that; I prefer to import the old CSV file, then use Select-Object to change the property names of objects in memory, and export back to CSV with the new property names (which become the header row). Something like this:

    $propertyTranslation = @(
        @{ Name = 'givenName'; Expression = { $_.'First Name' } }
        @{ Name = 'surName';   Expression = { $_.'Last Name'  } }
        # And so on
    (Import-Csv -Path 'yourCsvFile.csv') |
    Select-Object -Property $propertyTranslation |
    Export-Csv -Path 'yourCsvFile.csv' -NoTypeInformation

    Note: The parentheses around the Import-Csv command are important, if you want to write back to the same file in a single pipeline. However, this holds the entire file in memory, which may be a problem if your CSV is very large. If you want to stream one line at a time instead, you need to read and write from different files, then move the new file back over top of the original afterward.

  • #17220

    Points: 0
    Rank: Member

    I typically build a custom psobject and fill it with the desired property names and values before exporting to CSV. Don't know if it's applicable in your specific case though.

    $result = foreach($item in $dataSource) {
        Write-Output (New-Object psobject -Property @{
            FirstName = $item.givenName
            LastName = $item.surName
            # And so on..
    Export-Csv -InputObject $result -Path 'C:\CSV\result.csv' -NoTypeInformation
  • #17223

    Points: 329
    Helping Hand
    Rank: Contributor

    I usually use Dave's method using calculated properties which will replace the column name. Just thought I would mention another method is using a Alias for the property. In a situation where you want to bring it into Powershell, do some work and export it back to the other process with the same column names, it's easier to assign an Alias rather than rename the columns coming in and then rename them back when exporting:

    $processes = Get-Process
    $processes | Add-Member -MemberType AliasProperty -Name ProcessID -Value Id
    $processes | Select Id, ProcessID

    In the above example, you can reference the Id as either ID or ProcessID (A.K.A)

The topic ‘Replacing csv header row’ is closed to new replies.