Finding matching in a Cell

PowerShell Help
1

I am comparing two csvfile and want to find a match in a cell.

Example

Machine Name Location Status
JOEDOE1
 
Computerrname Location Status
JOEDOE2, JOEDOE3, Jimbob, Jerrry NY Retired
JImbob2, jeery2, JOEDOE1 NY Active
My Script 
$computername = Import-csv $path\computername.csv

$ref = Import-csv $path\ref.csv


Foreach ($line in $computername){


Foreach ($r in $ref){


if($line.computername -in $r.computername){


$line.Status = $r.status

}

}

}

$computername | Out-Gridview

I tried different variable like -match, -contain, but it cant read inside that cell to find a match. Any suggestion?

2

If this your entire script, there are several issues.

  1. You do not set variable r or computername, but try to use them.
  2. You are missing closing curly braces.
  3. The reference CSV is not a good example of CSV. See below
  4. You are over complicating it.

Reference CSV should look like this

Computerrname Status
JOEDOE2 Retired
JOEDOE3 Retired
Jimbob Retired
Jerrry Retired
JImbob2 Active
jeery2 Active
JOEDOE1 Active

However, working with the data you have, if the goal is to output a new CSV with the computername (from computername CSV) and the status (from matched line in reference CSV) then this simplified version will do the work.

# setting up test files, use your actual CSV files instead
$computernameCSV = New-TemporaryFile
$referenceCSV    = New-TemporaryFile

@'
Computername,Status
JOEDOE1
'@ | Set-Content $computernameCSV -Encoding utf8

@'
Computername,Status
"JOEDOE2,JOEDOE3,Jimbob,Jerrry",Retired
"JImbob2,jeery2,JOEDOE1",Active
'@ | Set-Content $referenceCSV -Encoding utf8

$referenceCSVdata = Import-Csv $referenceCSV

Import-Csv $computernameCSV | Foreach {
    if($match = $referenceCSVdata | where computername -match $_.computername)
    {
        [PSCustomObject]@{
            ComputerName = $_.computername
            Status       = $match.status
        }
    }
} -OutVariable newCSVdata

Which outputs (and also captures to $newCSVdata variable)

ComputerName Status
------------ ------
JOEDOE1      Active

You can add $newCSVdata | Out-Gridview or $newCSVdata | Export-CSV -Path \some\path\file.csv -NoTypeInformation to the end.

3

Sorry for the typo and syntax error above. I have corrected it and add additional info. My issue is i am working with over 5000 rows with 10 column and the header names do not match but are similar.

4

You probably are overthinking this. The easiest fix would be to pick one of the CSV files and edit its header names to match those of the other CSV so that you can run the comparison (you need to normalize the data before you can perform useful tasks with it).

If the headers are always consistent for that file, you could script the header renaming task so that you don’t have to do it manually.