Replace CSV line using the -replace operator - retain format (PowerShell)

Question

I am trying to replace a line in a CSV and retain the array structure, see CSV and code example:

CSV example:

Computer,Domain
DC001,contoso.local
DB001,contoso.local
AB001,contoso.local

Code:

[array]$csv= Import-Csv c:\docs\MyCsv.csv
$newCSv = $csv | ForEach { $_.Domain -replace "contoso", "MyDomainName" }

Output of $newCsv:

MyDomainName.local
MyDomainName.local
MyDomainName.local

Desired output $newCsv:

 Computer Domain       
-------- ------       
DC001    MyDomainName.local
DB001    MyDomainName.local
AB001    MyDomainName.local

I have tried piping to| Select Computer, Domain but this does not work. Is there a way to fix this?

the output you show is the DISPLAY of the variable content. if you want it converted to a CSV format you need to use Export-CSV. — Lee_Dailey
– Lee_Dailey, Commented Oct 14, 2020 at 14:13

Doug Maurer · Accepted Answer · 2020-10-14 14:23:45Z

You are not modifying your csv at all, check the output of your command before assigning it to a variable.

$csv = @'
Computer,Domain
DC001,contoso.local
DB001,contoso.local
AB001,contoso.local
'@ | ConvertFrom-Csv

$csv | foreach {$_.domain -replace 'contoso','MyDomainName'}

MyDomainName.local
MyDomainName.local
MyDomainName.local

You are assigning that output to variable $newcsv which has no bearing on the contents of $csv. If you want to replace text in one of your properties, you need to do just that.

$csv | foreach {$_.domain = $_.domain -replace 'contoso','MyDomainName'} # No output

Having assigned the replaced text from property .domain back into .domain now take a look at the contents of $csv

$csv

Computer Domain            
-------- ------            
DC001    MyDomainName.local
DB001    MyDomainName.local
AB001    MyDomainName.local

Now you can export it

$csv | Export-Csv $somefile -NoType

Theo · Accepted Answer · 2020-10-14 14:21:02Z

2

To output the new CSV, you need to specify its properties. To change the values in just one column, you can use Select-Object with a calculated property:

$newCsv = Import-Csv -Path 'D:\Test\MyCsv.csv' | 
          Select-Object Computer, 
                        @{Name = 'Domain';Expression = {$_.Domain -replace "contoso", "MyDomainName" }}

#output on screen
$newCsv

# write to new csv file
$newCsv | Export-Csv -Path 'D:\Test\MyNewCsv.csv' -NoTypeInformation

Output

Computer Domain            
-------- ------            
DC001    MyDomainName.local
DB001    MyDomainName.local
AB001    MyDomainName.local

answered Oct 14, 2020 at 14:21

Theo

61.9k8 gold badges28 silver badges48 bronze badges

Collectives™ on Stack Overflow

Replace CSV line using the -replace operator - retain format (PowerShell)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related