title: Compare a Column from CSV with a Column in EXL and add the result to my CSV File
body: <p>I want to compare my CSV File with EXL file.</p>
  
  <p>The EXL file has many columns such as USERID and WAVE. My CSV file has just USERID Column.</p>
  
  <p>it should compare my CSV Column USerID with USERID Column in Exl and checks in which wave the user A in CSV has in EXL file and add a wave column in CSV File called "WAVE” and write the wave Number related to USER A in CSV file.</p>
  
  <p>If no name from CSV file is found in EXL file, write in the Column Wave “NOT Found".</p>
  
  <p>I am not sure what I wrote "Read and Get Values from Excel" helps or not but I have no clue how/what to do further.</p>
  
  <pre><code>$TXTFile = "C:\A.txt" 
  $CSVFile = "C:\B.csv" 
  $EXLFIle = "C:\C.xlsx"
  
  #find all CNs in TXT file and list them in CSV File
  Select-String -Path $TXTFile  -Pattern 'CN=(.*?),' -AllMatches |
    Select-Object -Expand Matches |
    ForEach-Object { $_.Groups[1].Value } | 
    select @{L="UserID"; E={$_}} |
    Export-CSV $CSVFile -noTypeInformation
  
  # Read and Get Values from Excel 
  #Create an instance of Excel.Application and Open Excel file
  $ObjExcel = New-Object -ComObject Excel.Application
  $Workbook = $ObjExcel.Workbooks.Open($EXLFIle)
  $Sheet = $workbook.Worksheets.Item($SheetName)
  $ObjExcel.Visible = $false
  
  #Count max Rows
  $RowMax = ($sheet.UsedRange.rows).count
  
  #Declare the starting positions
  $rowUserID,$colUserID = 1,2
  $rowWave,$colWave = 1,9
  
  for ($i=1; $i -le $RowMax-1; $i++)
  {
  $UserID = $Sheet.Cells.Item($rowUserID+$i,$colUserID).text
  $Wave = $Sheet.Cells.Item($rowWave+$i,$colWave).text
  
  Write-Host ("USERID: "+$UserID)
  Write-Host ("Wave: "+$Wave)
  }
  
  $objExcel.quit() 
  </code></pre>
  
  <h3>UPDATE</h3>
  
  <p>I changed the script in this way, but it does not give me any error or any result. EXL file has 26000 raws. I do not know if it takes too much time or sth is wrong in my script.</p>
  
  <pre><code>$TXTFile = "C:\A.txt" 
  $CSVFile1 = "C:\A.csv" 
  $EXLFile = "C:\B.xlsx"
  $Result = "C:\Result.csv" 
  $CSVFile2 = "C:\B.csv"
  
  #find all CNs in TXT file and list them in CSV File
  Select-String -Path $TXTFile  -Pattern 'CN=(.*?),' -AllMatches |
    Select-Object -Expand Matches |
    ForEach-Object { $_.Groups[1].Value } | 
    select @{L="UserID"; E={$_}} |
    Export-CSV $CSVFile1 -noTypeInformation
  
  ########################################################################
  # Convert EXL file  to CSV File
  $excelwb = New-Object -ComObject excel.application
  $workbook = $excelwb.Workbooks.Open($EXLFile)
  $workbook.SaveAs($CSVFile2,6)
  $workbook.Close($false)
  $ExcelWB.quit()
  $csv = Import-Csv $CSVFile2 -Delimiter ";"
  ########################################################################
  # We Compare File2 with File1
  $file2 = import-csv $CSVFile2         
  $file1 = import-csv $CSVFile1         
  
  $Output = @()
  
      ForEach ($a in $file2)
      {
          $Match = $file1 | where {$_."User ID" -eq $a.UserID}
          If($Match)
          {
             $Output += New-Object PsObject -Property @{"User ID" =$a.UserID;column9 =$Match.column9}
          }
          else
          {
          $Output += New-Object PsObject -Property @{"User ID" =$a.UserID;column9 ="NA"}
          }
      }
  $Output | Export-Csv $Result
  </code></pre>