Replace (Or Edit) CSV Header Row

Tagged: 

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

  • Author
    Posts
  • #8917
    Profile photo of David Kluskiewicz
    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
    Profile photo of Poshoholic
    Poshoholic
    Member

    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
    Profile photo of Poshoholic
    Poshoholic
    Member

    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
    Profile photo of Vladimír Meier
    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.