Author Posts

October 11, 2017 at 12:50 pm

Hello,

I am feeding and imported csv to a ForEach-Object loop and am confused about the result.

Each object in the collection has a property called 'LAN Hostnames'. I need to remove everything after the first comma in this properties value on each object.

Example Objects:

Device Type: Windows 10
OS: Windows
LAN Hostname: name1, name1, name1
OS Detail: Windows NT; Windows NT; Windows NT

Device Type: Windows 10
OS: Windows
LAN Hostname: name2, name2, name2, name2, name2
OS Detail: Windows NT; Windows NT; Windows NT

I am trying to end up with:

Device Type: Windows 10
OS: Windows
LAN Hostname: name1
OS Detail: Windows NT

Here is something I have tried...

$import = Import-CSV -path E:\folder\file.csv

$split = ForEach ($i in $import) {

$_.'LAN Hostnames'.Split(',')[0]

}

I thought this would iterate through the collection of objects and split the 'LAN Hostnames' value at the comma. Which it does, but $split contains only these values. $split is...

name1
name2

...etc. I am excited to learn about working with strings in this way. Any advice appreciated.

October 11, 2017 at 1:27 pm

so you want to modify the value of the 'LAN Hostname' property but otherwise leave the rest of the output intact?

There are a couple of ways to do that, probably the most readable of which is to craft a new object within your loop. You're on the right track with your string manipulation code, but since that's all you're outputting from your loop, that's all you're going to get in your variable. You need to output the other properties of $i along with your modified 'LAN Hostnames' value. Try this:

$import = Import-CSV -path E:\folder\file.csv

ForEach ($i in $import) {

    new-object PSObject -Property @{
        'Device Type' = $i.'Device Type'
        'OS' = $i.OS
        'LAN Hostname' = $i.'LAN Hostnames'.Split(',')[0]
        'OS Detail' = $i.'OS Detail'
    }
}

You could also do it with calculated properties all on one line, but the above should be alot more readable.

October 11, 2017 at 1:35 pm

Hey

Your first problem here, based on your example data, is that your inputfile is not really a .csv file. It's a text file where one of the lines happens to be comma delimited. If you output $import to the prompt, you will see why this is a problem.

$import = Import-CSV -path "D:\input.csv"
$import

Results

Device Type: Windows 10                      
-----------------------                      
OS: Windows                                  
LAN Hostname: name1                          
OS Detail: Windows NT; Windows NT; Windows NT

As you can see from the above results, it is using the first line as a column header, which it is not, and then making subsequent lines values of that header.

So first you need to get the content of the file in with the correct method. In this case it's just a text file.

$import = Get-Content -path "D:\input.csv"
$import

Results

Device Type: Windows 10
OS: Windows
LAN Hostname: name1, name1, name1
OS Detail: Windows NT; Windows NT; Windows NT

Now you can go through each line and do whatever manipulation you want. Your second problem is that you are using $_ to reference the current object in the foreach loop. This would be OK if you were Piping the output of Get-Content to ForEach-Object, but you are not. You are using ForEach and defining $i as the current object, so you need to use $i as your reference variable.

$import = Get-Content -path "D:\input.csv"
$split = ForEach ($i in $import) {
    $i.Split(',|;')[0]
}
$split

Results

Device Type: Windows 10
OS: Windows
LAN Hostname: name1
OS Detail: Windows NT

For an example using Pipe and $_, you could do this.

$split = Get-Content -path "D:\input.csv" |
ForEach-Object {
    $_.Split(',|;')[0]
}
$split

Same Results

Device Type: Windows 10
OS: Windows
LAN Hostname: name1
OS Detail: Windows NT

October 11, 2017 at 1:58 pm

Hi Curtis!

I am not sure how I ended up typing $_. in my loop above. I am using $i.'LAN Hostnames' in the script. I just mis-typed that, but good eye.

Also, when I import my csv, the results are not as you have shown above. The result of Import-CSV is exactly how I posted above...

Device Type: Windows 10
OS: Windows
LAN Hostname: name1, name1, name1
OS Detail: Windows NT; Windows NT; Windows NT

Device Type: Windows 10
OS: Windows
LAN Hostname: name2, name2, name2, name2, name2
OS Detail: Windows NT; Windows NT; Windows NT

The Property side on the left shows the column header names in the csv (Device Type, OS, etc...) and the values shown are separate rows in the .csv file.

I will try using Get-Content instead and see how it goes.

Thank you!

October 11, 2017 at 3:12 pm

Jeremy,

This solution of creating custom objects worked beautifully. It is exactly what I needed, and makes sense! I will definitely carry this with me going forward.

Thank you for your time!

October 11, 2017 at 8:40 pm

Ok, this is likely me missunderstanding what the data you provided represented.

I understood:
Device Type: Windows 10
OS: Windows
LAN Hostname: name1, name1, name1
OS Detail: Windows NT; Windows NT; Windows NT

as being the content of your input file; however, based on your last statement, the content of your input file is actually something like.

Device Type,OS,LAN Hostname,OS Detail
Windows 10,Windows,"name1, name1, name1",Windows NT; Windows NT; Windows NT

If that's the case you do in fact have a CSV and Jeremy's code is a good example, but you don't really need to create a new custom object. You can just use the current object like this.

Import-Csv -Path "E:\folder\file.csv" |
ForEach-Object {
    $_.'LAN Hostname' = $_.'LAN Hostname'.split(",")[0]
    $_.'OS Detail' = $_.'OS Detail'.split(";")[0]
    $_
} |
Format-List

Results

Device Type  : Windows 10
OS           : Windows
LAN Hostname : name1
OS Detail    : Windows NT

October 11, 2017 at 10:03 pm

This my lazy way 😛 if all has the same name

'LAN Hostname' = $i.'LAN Hostnames' | Select -Unique
'OS Detail' = $i.'OS Detail' | Select -Unique

October 11, 2017 at 10:08 pm

Hey Naw,
The problem there is that the value is a string not an array where Select-Object -Unique can compare multiple string and only return the unique elements.

October 11, 2017 at 10:42 pm

Oh, Thanks for pointing that out. I thought it was a returned object from somewhere. Yes, if it's the string I would go with .split(",") or if all those string has the same length .SubString(0,5) for namex etc.,

October 12, 2017 at 12:07 am

Hi Curtis,

Correct, that is what the .csv looks like. And I will take note and write out your example as well.

Thank you!