Replacing csv header row

Tagged: , ,

This topic contains 3 replies, has 4 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 2 years, 4 months ago.

  • Author
    Posts
  • #17216
    Profile photo of Shawn K
    Shawn K
    Participant

    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!

    Thanks

  • #17217
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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
    Profile photo of Martin Nielsen
    Martin Nielsen
    Participant

    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
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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)

You must be logged in to reply to this topic.