Remove string after specific character from list using powershell

Question

I am attempting to clean up (reformat) a list of servers that has been provided to me containing the full domain name so that I can use it elsewhere. The list of servers arrive formatted like: servername.domain.com. I read the list into a variable using import-csv and remove the other columns of data not needed using the format-table command. My resulting command looks something like this:

$l=Import-Csv .\filename.csv | Format-Table TARGET

TARGET is the column name containing the server names.

When I use the following to remove the domain name portion of the server name, the resulting value is "Microsoft" repeated for each line containing the server name. I have confirmed the server names are being read into the variable correctly.

$l -replace '(.+?)\..+','$1'

The command does work if there is only one server name in the variable. I know I was able to trim the names of the servers down at one point but I think I had a brain lapse that day and just did not write down the command I used. :(

Any suggestions? My end goal is to incorporate the commands into a script for scheduled processing.

If you plan on doing data manipulation remove the | Format-Table TARGET as it destroys the objects. You want $l=Import-Csv .\filename.csv | Select -Expand TARGET to start. — Matt
– Matt, Commented Feb 5, 2015 at 18:37
Thank you Matt. I did not know this... Learn something new everyday. — gritts
– gritts, Commented Feb 5, 2015 at 19:56

Community · Accepted Answer · 2017-05-23 12:00:12Z

There are a few considerations here. Firstly, as matt has said, objects are destroyed by formatting commands. Only use them if you are trying to achieve a pretty view of the properties of objects, otherwise use select.

I would argue that you would be doing that unnecessarily: why throw away information that you might later want? I've lost count of the number of times that I've realised I can do something new and useful with the script or function I've just written, if I just expand it a little.

When you import a .csv file, you create objects with the property names of the column headings, so you can just refer to that property by name later.

Your problem with the replace command is related to the fact that you need to loop through the objects stored in $l, when there is more than one, Foreach-Object will do that.

So, for the file servers.csv:

TARGET,VALUE2,VALUE3
server1.domain.com,2,3
server2.domain.com,2,3
server3.domain.com,2,3
server4.domain.com,2,3
server5.domain.com,2,3

The import gives us a nice array of objects (nicely formatted in a table):

Import-Csv .\servers.csv | ft -a

TARGET             VALUE2 VALUE3
------             ------ ------
server1.domain.com 2      3
server2.domain.com 2      3
server3.domain.com 2      3
server4.domain.com 2      3
server5.domain.com 2      3

We can use Foreach-Object (% is the alias) to get the 'TARGET' property of each object (equivalent to each line the in file), use the 'Split' method to split on the dots of the FQDN and then take the first token of that split:

Import-Csv .\servers.csv | % {$_.TARGET.Split('.')[0]}
server1
server2
server3
server4
server5

You can import to a variable first and then pipe that to the loop if you want.

Cheers

ConanW, thank you for the detailed description / explanation. Very helpful!

Matt · Accepted Answer · 2015-02-05 18:41:20Z

ALWAYS: If you plan on doing data manipulation remove the | Format-Table TARGET as it destroys the objects.

One approach would be to extract a string array of the column TARGET which you could then process.

$l=Import-Csv .\filename.csv | Select -Expand TARGET

Assuming you have a properly formed CSV your code could be simplified. Since it does not require regex you could also do.

$l=Import-Csv .\filename.csv | Select -Expand TARGET | ForEach-Object{$_ -Split "." | Select -First 1}

$l should contain just the server names at this point. You regex is not wrong however in order to use it you would have to refine it or use it in a loop similar to how I use -split.

$l=Import-Csv .\filename.csv | Select -Expand TARGET
$l | ForEach-Object{$_ -replace '(.+?)\..+','$1'}

Collectives™ on Stack Overflow

Remove string after specific character from list using powershell

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related