CODEWITHSUNDEEP
powershell
shadow2020
shadow2020

Reputation: 1351

Powershell Compare-object IF different then ONLY list items from one file, not both

I have deleted my original question because I believe I have a more efficient way to run my script, thus I'm changing my question.

$scrubFileOneDelim = "|"
$scrubFileTwoDelim = "|"

$scrubFileOneBal = 2
$scrubFileTwoBal = 56
$scrubFileOneAcctNum = 0
$scrubFileTwoAcctNum = 0


    $ColumnsF1 = Get-Content $scrubFileOne | ForEach-Object{($_.split($scrubFileOneDelim)).Count} | Measure-Object -Maximum | Select-Object -ExpandProperty Maximum
    $ColumnsF2 = Get-Content $scrubFileTwo | ForEach-Object{($_.split($scrubFileTwoDelim)).Count} | Measure-Object -Maximum | Select-Object -ExpandProperty Maximum
    $useColumnsF1 = $ColumnsF1-1;
    $useColumnsF2 = $ColumnsF2-1;


$fileOne = import-csv "$scrubFileOne" -Delimiter "$scrubFileOneDelim" -Header (0..$useColumnsF1) | select -Property @{label="BALANCE";expression={$($_.$scrubFileOneBal)}},@{label="ACCTNUM";expression={$($_.$scrubFileOneAcctNum)}} 
$fileTwo = import-csv "$scrubFileTwo" -Delimiter "$scrubFileTwoDelim" -Header (0..$useColumnsF2) | select -Property @{label="BALANCE";expression={$($_.$scrubFileTwoBal)}},@{label="ACCTNUM";expression={$($_.$scrubFileTwoAcctNum)}} 

$hash = @{}
$hashTwo = @{}
$fileOne | foreach { $hash.add($_.ACCTNUM, $_.BALANCE) }
$fileTwo | foreach { $hashTwo.add($_.ACCTNUM, $_.BALANCE) }

In this script I'm doing the following, counting header's to return the count and use it in a range operator in order to dynamically insert headers for later manipulation. Then I'm importing 2 CSV files. I'm taking those CSV files and pushing them into their own hashtable.

Just for an idea of what I'm trying to do from here...

CSV1 (as a hashtable) looks like this: 

Name                           Value                                                                                                                                                    
----                           -----                                                                                                                                                    
000000000001                   000000285+                                                                                                                                               
000000000002                   000031000+                                                                                                                                               
000000000003                   000004685+                                                                                                                                               
000000000004                   000025877+                                                                                                                                                                                                                                                                                          
000000000005                   000000001+                                                                                                                                               
000000000006                   000031000+                                                                                                                                               
000000000007                   000018137+                                                                                                                                               
000000000008                   000000000+

CSV2 (as a hashtable) looks like this: 

Name                           Value                                                                                                                                                    
----                           -----                                                                                                                                                    
000000000001                   000008411+                                                                                                                                               
000000000003                   000018137+                                                                                                                                               
000000000007                   000042865+                                                                                                                                               
000000000008                   000009761+

I would like to create a third hash table. It will have all the "NAME" items from CSV2, but I don't want the "VALUE" from CSV2, I want it to have the "VALUE"s that CSV1 has. So in the end result would look like this. 

Name                           Value                                                                                                                                                    
----                           -----                                                                                                                                                    
000000000001                   000000285+                                                                                                                                               
000000000003                   000004685+                                                                                                                                               
000000000007                   000018137+                                                                                                                                               
000000000008                   000000000+

Ultimately I want this to be exported as a csv.

I have tried this with just doing a compare-object, not doing the hashtables with the following code, but I abandoned trying to do it this way because file 1 may have 100,000 "accounts" where file 2 only has 200, and the result I was getting listed close to the 100,000 accounts that I didn't want to be in the result. They had the right balances but I want a file that only has those balances for the accounts listed in file 2. This code below isn't really a part of my question, just showing something I've tried. I just think this is much easier and faster with a hash table now so I would like to go that route.

#Find and Rename the BALANCE and ACCOUNT NUMBER columns in both files. 
$fileOne = import-csv "$scrubFileOne" -Delimiter "$scrubFileOneDelim" -Header (0..$useColumnsF1) | select -Property @{label="BALANCE";expression={$($_.$scrubFileOneBal)}},@{label="ACCT-NUM";expression={$($_.$scrubFileOneAcctNum)}}
$fileTwo = import-csv "$scrubFileTwo" -Delimiter "$scrubFileTwoDelim" -Header (0..$useColumnsF2) | select -Property @{label="BALANCE";expression={$($_.$scrubFileTwoBal)}},@{label="ACCT-NUM";expression={$($_.$scrubFileTwoAcctNum)}}

Compare-Object $fileOne $fileTwo -Property 'BALANCE','ACCTNUM' -IncludeEqual -PassThru | Where-Object{$_.sideIndicator -eq "<="} | select * -Exclude SideIndicator | export-csv -notype "C:\test\f1.txt"

Upvotes: 0

Views: 1488

Answers (2)

Dave F
Dave F

Reputation: 1985

Assuming that you have the following hash tables:

 $hash = @{
    '000000000001' = '000000285+';
    '000000000002' = '000031000+';
    '000000000003' = '000004685+';
    '000000000004' = '000025877+';
    '000000000005' = '000000001+';
    '000000000006' = '000031000+';
    '000000000007' = '000018137+';
    '000000000008' = '000000000+';
}

$hashTwo = @{
    '000000000001' = '000008411+';
    '000000000003' = '000018137+';
    '000000000007' = '000042865+';
    '000000000008' = '000009761+';
}

you can create the third hash table by iterating over the keys from the second hash table and then assigning those keys to the value from the first hash table. 

$hashThree = @{}
ForEach ($key In $hashTwo.Keys) {
    $hashThree["$key"] = $hash["$key"]
}
$hashThree

The output of $hashThree is:

Name                           Value
----                           -----
000000000007                   000018137+
000000000001                   000000285+
000000000008                   000000000+
000000000003                   000004685+

If you want the order of the data maintained (and you are using PowerShell 6 Core), you can use [ordered]@{} when creating the hash tables.

Upvotes: 1

Drew
Drew

Reputation: 4020

What you are after is filtering the Compare-Object function. This will show only one side of the result. YOu will need to place this before you exclude that property for it to work.

| Where-Object{$_.sideIndicator -eq "<="} |

Upvotes: 1

Related Questions