Replace (Or Edit) CSV Header Row

This topic contains 3 replies, has 3 voices, and was last updated by  Vladimír Meier 3 years, 7 months ago.

  • Author
    Posts
  • #8917

    David Kluskiewicz
    Participant

    Is there a way to replace or edit the header row of a CSV file with PowerShell? I have been able to edit individual cells in Excel using the script below, but I would rather not convert the file from CSV and start launching an instance of Excel just to edit one row of a CSV. Any suggestions?


    $Excel = New-Object -Com Excel.Application
    $WorkBook = $Excel.Workbooks.Open('C:\scripts\merger\merged?.xlsx')
    $WorkSheet = $WorkBook.Worksheets.Item(1)

    $AddCustomField1 = $WorkSheet.Cells.Item(1,9)
    $AddCustomField1.Value2 = "Custom Field 1"

    $AddCustomField3 = $WorkSheet.Cells.Item(1,4)
    $AddCustomField3.Value2 = "Custom Field 3"

    $AddCustomField5 = $WorkSheet.Cells.Item(1,8)
    $AddCustomField5.Value2 = "Custom Field 3"

    $AddCustomField1 = $Null
    $AddCustomField3 = $Null
    $AddCustomField5 = $Null

    $WorkBook.Save
    $Excel.Quit()

  • #8922

    Poshoholic
    Participant

    Hi David,

    Funny, I did this just the other day.

    Instead of using Import-Csv, you can use ConvertFrom-Csv. Here's an example that shows how you can skip the header line in your csv file when you call Get-Content (gc) so that you can then specify a different list of headers in ConvertFrom-Csv:

    PS C:\> gsv b* | select Name,DisplayName,Status | export-csv C:\my.csv -NoTypeInformation
    PS C:\> gc C:\my.csv | Select -skip 1 | ConvertFrom-Csv -Header 'ServiceName','LocalizedName','Status'
    
    ServiceName                             LocalizedName                           Status
    -----------                             -------------                           ------
    BcmBtRSupport                           Bluetooth Radio Control Service         Running
    BDESVC                                  BitLocker Drive Encryption Service      Stopped
    BFE                                     Base Filtering Engine                   Running
    BITS                                    Background Intelligent Transfer Service Running
    Bonjour Service                         Bonjour Service                         Running
    BrokerInfrastructure                    Background Tasks Infrastructure Service Running
    Browser                                 Computer Browser                        Running
    bthserv                                 Bluetooth Support Service               Running
    
  • #8923

    Poshoholic
    Participant

    Oh, and of course, once you have the data converted, you could just re-export it again.

    Also, if you really just want to edit the csv row in the file, you could do that too using Get-Content/Set-Content.

  • #14255

    Vladimír Meier
    Participant

    Hi David,

    you can using Import-Csv as well.
    Here is example

    => gsv b* | select Name,DisplayName,Status | export-csv C:\my.csv -NoTypeInformation
    => Import-Csv C:\my.csv | select @{Name = "ServiceName"; Expression = {$_."Name"}}, @{Name = "LocalizedName"; Expression = {$_."DisplayName"}}, status
    ServiceName LocalizedName Status
    ----------- ------------- ------
    BDESVC BitLocker Drive Encryption Service Stopped
    BFE Base Filtering Engine Running
    BITS Background Intelligent Transfer Service Running
    Browser Computer Browser Running
    bthserv Bluetooth Support Service Stopped

You must be logged in to reply to this topic.