Replacing csv header row

Welcome Forums General PowerShell Q&A Replacing csv header row

Viewing 3 reply threads
  • Author
    Posts
    • #17216
      Participant
      Topics: 1
      Replies: 0
      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!

      Thanks

    • #17217
      Member
      Topics: 9
      Replies: 2322
      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
      Participant
      Topics: 3
      Replies: 79
      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
      Participant
      Topics: 12
      Replies: 1642
      Points: 2,660
      Helping Hand
      Rank: Community Hero

      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)

Viewing 3 reply threads
  • The topic ‘Replacing csv header row’ is closed to new replies.