Editing a Column Within a CSV

Question

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?

You've asked this same question three times today. – Maximilian Burszley Dec 18 '18 at 21:38 — Maximilian Burszley, Dec 18 '18 at 21:38

mklement0 · Answer 1 · 2022-03-11T00:19:59.140

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.

A couple thousand rows with 3 fields each is nowhere near what I would consider a large data set. ;) — Ansgar Wiechers, Dec 18 '18 at 21:00
@AnsgarWiechers: Well, a couple _ten_-thousands, but point taken. I hope it's still helpful to contrast the two approaches. — mklement0, Dec 18 '18 at 21:04

score 1 · Accepted Answer · answered Dec 18 '18 at 20:56

1

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.

answered Dec 18 '18 at 20:56

Ansgar Wiechers

193,178
25
254
328

Thank you, this is what I was looking for. I'm not sure why when I tried $row.Name = $row.Name... previously, it did not work. Could it be that I was using double quotes instead of single? – JK72 Dec 18 '18 at 21:11
No, double quotes shouldn't have made a difference in this case. – Ansgar Wiechers Dec 18 '18 at 21:19
@JK72: `$row.Name = $row.Name... ` only works if `$row` is a _single_ row, as in this answer. By contrast, if `$file` contains _all_ rows, as your question suggests, `$file.Name = $file.Name...` does _not_ work – mklement0 Dec 18 '18 at 23:07

Editing a Column Within a CSV

2 Answers2