title: finding duplicate rows in excel and export those rows to another sheet using power shell
body: <p>How to find duplicate values in Excel and export rows to another sheet using power shell?
  <a href="https://i.stack.imgur.com/W1EWA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/W1EWA.png" alt="enter image description here"></a>
  I had an Excel sheet with multiple rows and column lets say from "A" to "k". I need to find duplicate rows only if values in all the columns in a row are unique. And the script should ignore D,E,F columns even though those column's values are same.</p>
  
  <p><strong>The script should also copy all those duplicate rows and should paste in a new excel file.it should also copy header row and source of duplicate rows</strong> and  Also attaching a sample image of input file(the output file should also be same as input in this input case because it should also copy source duplicate rows). I had tried a code but it is throwing an error..please look into that and give me a solution for the code.</p>
  
  <pre><code>code:
  # The Text OleDB driver is only available in PowerShell x86. Start x86 
  shell if using x64.
  # This has to be the first check this script performs.
  if ($env:Processor_Architecture -ne "x86")   { 
  Write-Warning "Switching to x86 shell"
  &amp;"$env:windir\syswow64\windowspowershell\v1.0\powershell.exe" 
  "$PSCommandPath $args"; return 
   }
  
  # Change to your CSV file name, must end in .csv or .tsv
  $csvfile = "C:\files\A01modcsv.csv"
  
   # Does the first row contain column names?
   $firstRowColumns = $True
  
   # What's the delimiter? Use `t for tabbed.
   $csvdelimter = "`t"
  
  
   $firstRowColumns = $true
   $checkColumns = "A"
  
  
   $datasource = Split-Path $csvfile
   $tablename = (Split-Path $csvfile -leaf).Replace(".","#")
  
   switch ($firstRowColumns) {
       $true { $firstRowColumns = "Yes" }
       $false { $firstRowColumns = "No" }
    }
  
   $elapsed = [System.Diagnostics.Stopwatch]::StartNew() 
   [void][Reflection.Assembly]::LoadWithPartialName("System.Data")
  
    # Setup OleDB using Microsoft Text Driver.
    $connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$datasource;Extended Properties='text;HDR=$firstRowColumns;FMT=Delimited($csvdelimter)';"
  
  $conn = New-Object System.Data.OleDb.OleDbconnection
  $conn.ConnectionString = $connstring
  $conn.Open()
  $cmd = New-Object System.Data.OleDB.OleDBCommand
  $cmd.Connection = $conn
  
  
   # Perform select on CSV file, then add results to a datatable using ExecuteReader
   $sql = "SELECT $checkColumns, COUNT(*) as DupeCount FROM [$tablename] GROUP BY $checkColumns HAVING COUNT(*) &gt; 1"
   $cmd.CommandText = $sql
   $dt = New-Object System.Data.DataTable
   $dt.BeginLoadData()
  $dt.Load($cmd.ExecuteReader([System.Data.CommandBehaviour]::CloseConnection))
   $dt.EndLoadData()
   $totaltime = [math]::Round($elapsed.Elapsed.TotalSeconds,2)
  
   # Get Total Row Count
  
   $cmd.CommandText = "SELECT COUNT(*) as TotalRows FROM [$tablename]"
   $totalrows = $cmd.ExecuteScalar()
   $conn.Close()[enter image description here][1]
  
  # Output some stats
  $dupecount = $dt.Rows.Count
  Write-Host "Total Elapsed Time: $totaltime seconds. $dupecount duplicates found out of $totalrows total rows. You can access these dupes using `$dt." -ForegroundColor Green
  </code></pre>
  
  <blockquote>
    <p>I am getting an error in the menctioned code at "$dt.Load($cmd.ExecuteReader([System.Data.CommandBehaviour]::CloseConnection))" command....can any help me in solving this error.Thank you.</p>
  </blockquote>