title: Powershell - Extract data from a file depending on a 2nd file data
body: <p>I'm trying to extract some data from one file compared to what is found in  another file. I need this to happen in Powershell. I'm really trying to coax some SQL like workings, and as much as I've scratched my head, read on the subject, I've not been able to make anything happen. </p>
  
  <p>Essentially, I need to match 4 criteria from file 1 to 4 Criteria on file 2. If those match, I need to ensure 2 other criteria on file 2 meet certain values. </p>
  
  <p>If the criteria match and the values are met, I want those transactions in one file, otherwise in another file. </p>
  
  <p>Criteria to match from file 1 to file 2:</p>
  
  <ul>
  <li><p>Company number: <code>$_.File1Field1</code> to <code>$_.File2Field4</code></p></li>
  <li><p>Transaction number: Last 4 digits of <code>$_.File1Field8</code> to <code>$_.File2Field1</code></p></li>
  <li><p>Transaction value: <code>$_.File1Field11</code> to <code>$_.File2Field10</code></p></li>
  <li><p>Transaction Date: <code>$_.File1Field7</code> to <code>$_.File2Field7</code></p></li>
  </ul>
  
  <p>If those 4 criteria are a match, I need 2 extra fields on the file 2 to NOT be, say,  "ABC" or "DEF" for <code>$_.File2Field2</code> &amp; "1234" for <code>$_.File2Field3</code>. </p>
  
  <p>Then I need all transaction of one type in one file, and the others in another file. 
  Am I asking too much of powershell (I certainly am asking too much of myself with that one!!!!  lol )?!?</p>
  
  <p>I've done a lot of reading on Compare-Object, Where-Object, but couldn't find anything that I could put to use. :(</p>
  
  <p>Those are my original files at the moment.</p>
  
  <pre><code>#File 1:
  $LoanTransactions = import-Csv \\774512-LRBSPT01\********$\uardata\rt1\BankRec\Test\step1\Test_Store_Loans_Module.txt
  #File 2:
  $LoanExtract = import-Csv \\774512-LRBSPT01\********$\uardata\rt1\BankRec\Test\loans_extract.csv
  </code></pre>
  
  <p>I've done where-object in the past, where I would pre-define some conditions and then apply that the a file to extract the result, but I haven't been able to make the conditions depends on something contained in my 2nd file. </p>
  
  <p>Something like that:</p>
  
  <pre><code>$ConditionsToMeet = { $_.File1Field1 -eq $_.File2Field4 -and $_.File1Field8 -eq $_.File2Field1.....}
  $ConditionsNotMet = { $_.File1Field1 -ne $_.File2Field4 -or $_.File1Field8 -ne $_.File2Field1.....}
  
  $LoanTransactions | where-object $ConditionsToMeet | Export-Csv \\774512-LRBSPT01\********$\uardata\rt1\BankRec\Test\step1\Extract1.txt
  
  $LoanTransactions | where-object $ConditionsNotMet | Export-Csv \\774512-LRBSPT01\********$\uardata\rt1\BankRec\Test\step1\Extract2.txt
  </code></pre>
  
  <p>Obviously, my conditions aren't constructed properly, unfortunately, despite extensive reading, I couldn't find how to. 
  It may be super simple, or perhaps Powershell just cannot quite do that, but i thought this should be achievable?</p>