CODEWITHSUNDEEP
powershellcsvreplaceformatting
JK72
JK72

Reputation: 149

Editing a Column Within a CSV

I have a CSV that contain 24,000+ server names, as well as other information. The columns of the CSV are Name, Group, and Target. An example of the raw data going in for the "Name" column is:

WindowsAuthServer @ wddg9028WIN

I'm trying to find a way to edit the Name column within the CSV. I need to remove WindowsAuthServer @, and WIN from the server names. The only thing I want to be left over is the server name, or wddg9028 for this example. A potential issue is that not every server name has this format, and some only contain the server name. Obviously the ones that are already correct don't need to be changed, but I'm not sure if this will throw things off.

I tried doing:

$test = $file.Name -replace "WindowsAuthServer @ ",""

This returns the names exactly how I want them (minus removing WIN). However whenever I do this, I lose all other columns from the CSV.

Is there a way to edit one column within a CSV? Should it be done as the data is being read in for this case?

Upvotes: 0

Views: 118

Answers (2)

mklement0
mklement0

Reputation: 437803

With large data sets, a pipeline-based command is generally preferable to keep memory use constant:

Import-Csv in.csv |
  Select-Object @{ 
      n='Name'
      e={ $_.Name -creplace 'WindowsAuthServer @ |WIN' }
    }, Group, Target |
      Export-Csv -NoTypeInformation -Encoding Utf8 out.csv

That way, the data is processed row by row and, as a bonus, you can use Select-Object with a calculated property to perform your name transformation.

That said, if performance matters and you know that you can fit all data in memory at once, you can use the following (PSv4+):

# Read the file in full into memory (transformed into objects).
$file = Import-Csv in.csv 

# Modify the `.Name` property values of the in-memory object collection.
$file.ForEach({ $_.Name = $_.Name -creplace 'WindowsAuthServer @ |WIN' })

# Export the in-memory object collection back to a CSV file.
$file | Export-Csv -NoTypeInformation -Encoding Utf8 out.csv

As for what you tried:

$test = $file.Name -replace "WindowsAuthServer @ ",""

This statement performs the string replacement on all .Name property values and returns the transformed strings as new strings (which you then assign to $test).

Therefore, your $file.Name values remain unchanged.

Instead, you must explicitly update the .Name properties.
While it might be tempting to do the following, it does not work:

# !! Does NOT work if $file is an *array* of objects.
$file.Name = $file.Name -replace "WindowsAuthServer @ ",""

That is, while you can get the property values of an array of objects with simply property access (.Name) - a feature called member-access enumeration - setting properties is by design not supported - see GitHub issue #5271.

Instead, apart from using the pipeline as shown above, you have two choices:

  • in PSv4+, you can use the .Where() collection method, as also shown above.

  • alternatively (also works in lower PS versions), use a foreach loop, as shown in Ansgar Wiechers' answer.

Upvotes: 2

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200293

Import the CSV. Process the rows in a loop, replacing the current value of the field in question with the modified value. Export the data back to a CSV.

$csv = Import-Csv 'C:\path\to\input.csv'

foreach ($row in $csv) {
    $row.Name = $row.Name -replace 'WindowsAuthServer @ '
}

$csv | Export-Csv 'C:\path\to\output.csv' -NoType

This is at least your third question on the subject. Please go find a PowerShell tutorial. This is really basic stuff.

Upvotes: 1

Related Questions