Search CSV using values from CSV

Question

I have a CSV that has two Columns a Computer and an action I also have another CSV that has 3 Columns Computer Identifier Serial Number and Device ID.

I want to take CSV1 and take Computer column and search for those values in CSV2 and see if its the computer identifier or serial and give me the matching device ID so I can run another function against that.

So CSV 1

Computer	Action
CB12345	Lock
574638	Unlock

Will Search through for where the computer value is in CSV 2

DeviceID	CB Number	Serial
57687348	CB12345	674Z2X3
34554345	CB65487	474Z2X3
54868323	CB45789	574Z2X3
15593235	CB65976	974Z2X3
37593236	CB35466	074Z2X3

Once it finds the value whether it be a CB number or serial it will grab its corresponding Device ID so I can use that in another function.

This is what I had so far but I am not sure how to do it so I can use both CSVs

$yesterday = [datetime]::Today.AddDays(-1).tostring("yyyy-MM-dd")
$date = get-date -Format yyyy-MM-dd
$CB = import-csv -Path "\\Scripts\GAM test\CBs" 
$GAM = import-csv -Path "\\Scripts\GAM\CSV\$yesterday-GAMprintCROSbasic.csv"

```  
<# foreach ($record in $CB)
    {
    if ($record.action = "Unlock")
        {
        $DeviceID = $record.deviceID
        $DeviceAsset = $record.asset

        E:\gam\gam.exe update cros $DeviceID action enable
            Write-Output "$deviceID is being disabled. $deviceasset"
        Start-Sleep 10 #>

`if ($record.action = "Unlock")` needs to be `if ($record.action -eq "Unlock")` in PowerShell — TessellatingHeckler, Jan 07 '22 at 21:54
If you are able I would consider using a tool that can do the join between CSV1 and CSV2, and run the shell script on the CSV3 results; otherwise each time you do a run, your script will need to load CSV2 into ram, which if CSV2 is massive could get time consuming and expensive on automating a high turnover of the CSV1 file — Gregor y, Jan 11 '22 at 17:00

score 0 · Answer 1 · answered Jan 08 '22 at 03:16

If you want to use the current computer record to find a matching CB Number or Serial from the second CSV, you can use a where statement for that.

Here is a trimmed down example demonstrating that.

# CSV #1 - Computer / Action
$CB = import-csv -Path "\\Scripts\GAM test\CBs" 
# CSV #2 - DeviceID / CB Number /Serial
$GAM = import-csv -Path "\\Scripts\GAM\CSV\$yesterday-GAMprintCROSbasic.csv"

foreach ($record in $CB) {
    if ($record.action -eq "Unlock") {
        # Select  matching value 
        $GamRecord = $Gam | Where { $_.'CB Number' -eq $Record.Computer -or $_.'Serial' -eq $Record.Computer } | Select -First 1
        if ($null -eq $GamRecord) {
            Write-Warning "No match for: $($record.Computer)"
            Continue
        }

        $Serial = $GamRecord.Serial    


 
        # $GamRecord.'CB NUmber'
        # $GamRecord.DeviceID

    }

}

Once you have the $GamRecord, which is the first record that correlated with $Record.Computer either in its CB Number or Serial, you can access all the properties from both CSVS through their respective variable.

$record.Computer # Is a match to either $GamRecord.'CB Number' or $GamRecord.Serial
$record.Action
$GamRecord.'CB Number'
$GamRecord.DeviceId
$GamRecord.Serial

Note that I left a if ($null -eq $GamRecord) { as you might want to do something specific in the event Computer from the first CSV do not correspond to any CB Number or Serial in CSV2.

Also note the use of -eq when doing a comparison in the if statements. In your initial sample, as pointed out by one of the commenter, you used if ($record.action = "Unlock"), which is incorrect. = is only used to assign a value to a variable, not as a comparison operator. For the latter, -eq is used.

Search CSV using values from CSV

1 Answers1