Replace (Or Edit) CSV Header Row

PowerShell Help
1

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()

2

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
3

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.

4

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