editing tab delimited csv

PowerShell Help
1

I’ve got a large csv file where I would need to replace some text. Given a list of host I would need to find certain property of my hosts and if they match a condition apply modification.
I’m starting with

$hosts = Get-Content hosts.txt
$props = Import-CSV props.csv -delimiter “`t” | select hostname,status,CPU,FreeDisk | Where-Object {$.status -like ‘down’} | Where-Object {$.CPU -like “0%”} | Where-Object {$_.FreeDisk -lt “20”}

foreach($object in hosts){
$props | Where {$.hostname match $object} # this is the first condition and it seems to work when I run this in debugging mode but then I’m not sure ho to pipe the results to my if statement…
if ($.status -match “down”){
$.status = $.status -replace “down”,“up” # this is the bit that should do the replaccement
}
$
} | Export-CSV props.csv -delimiter “`t” -NoTypeInformation

Can someone please spot where I’m going wrong?

2

Hey Jarek
From having a quick check,

$props | Where {$_.hostname match $object}

does not have a ‘-’ for match, to indicate it as a parameter

3

Oh yes sorry. That’s a typo. The thing is not the condition isn’t working but rather getting the second condition to apply so I added
$props | Where {$.hostname match $object} | Foreach-Object { and now I can see my $ getting values where before the passing of $_ was stopping at that line.

4

I’m now getting an error of an empty pipe when the script gets to Export-CSV…
so I replaced the last line with

$props | Export-CSV props.csv -delimiter “`t” -NoTypeInformation

but but that’s no good either as this is not only replacing the fields I intended to replace but creates a new csv which only contains what matched
$InUse | where {$_.Hostname -match $blackedOUT}

5

This isn’t going to be what’s causing your current problem, but jumps out at me anyway. $props contains an already-filtered list from your props.csv file. When you export that back to the CSV later, you’re going to lose any lines in the file that didn’t match your original filter. If that’s what you intended, great, but otherwise, be careful. :slight_smile:

The main problem in the code of your original post is that you’re trying to use $_ in a place where it isn’t appropriate. $_ is set in the ForEach-Object cmdlet, but not the (foreach ($thing in $things)) loop construct; for the latter, you’d refer to $thing rather than $_. Also, you can’t pipe the results of the (foreach ($thing in $things)) loop construct to another command directly.

With these things in mind, you could do something like this:

$hosts = Get-Content hosts.txt
$props = Import-CSV props.csv -Delimiter "`t" # Making sure $props contains the entire file for later export.

# Here, we'll just modify the $props array in memory.

foreach ($host in $hosts)
{
    foreach ($object in $props)
    {
        if ($object.HostName -match $host)
        {
            # No need to check whether Status contains 'down' first; -replace will just do nothing if that string
            # isn't found.

            $object.Status = $object.Status -replace 'down', 'up'
        }
    }
}

# Now write the modified array back out to the CSV file.
$props | Export-CSV props.csv -Delimiter "`t" -NoTypeInformation
6

Sorry it took a while. I was stuck with an emergency situation. As to the script I like that double foreach loop. Makes sense once I look at it and very elegant too :slight_smile: I wouldn’t get to that with my ifs and buts :wink: Trouble is the resulting file isn’t the same as the original csv. Somehow it gets a " at the beginning and end of each row which messes it up to the point where it doesn’t display correctly when imported to excel as tab delimited file. I cannot see there it’s taking these double quotes from.

7

Took a while but it turns up if we add

(Get-Content props.csv) | ForEach-Object {$_ -replace ‘"’,“”} | Out-File props.csv -Force -Encoding Ascii

All the unwanted quotes go away :slight_smile: