none
Unique Column RRS feed

  • Question

  • Hi,

    I have a csv file which I need to read and export the data to a csv file. The resulting csv file will only include unique values for column A. The input csv file has many duplicates for column A which I need to remove. Is there a Unique option which i can use when running the Import-CSV cmdlet? Thanks for looking into this.

    Input CSV file. 

    • Moved by Bill_Stewart Friday, August 14, 2015 7:36 PM Help vampire question
    Tuesday, July 7, 2015 4:39 PM

All replies

  • This may not be the most efficient way to get the data, but you can do something like this.

    $importedcsv = Import-Csv 'c:\temp\csv.csv'
    $uniquecol1 = $importedcsv | Select-Object -Property col1 -unique
    foreach ($item in $uniquecol1) {
        $importedcsv | Where-Object { $_.col1 -eq $item.col1 } | Select-Object -first 1
        }


    And then export the data returned to a CSV. My testing CSV just looks like this (so you can see the column names, etc.).

    col1,col2
    1,1
    1,2
    1,3
    2,4
    2,5
    3,6
    3,7
    3,8
    3,9

    And my output looks like this:

    col1                                      col2                                    
    ----                                      ----                                    
    1                                         1                                       
    2                                         4                                       
    3                                         6  

    Tuesday, July 7, 2015 5:04 PM
  • Import-Csv 'c:\temp\csv.csv'|group col1 | %{$_.group[0]} | export-Csv newcsv.csv



    \_(ツ)_/

    Tuesday, July 7, 2015 10:05 PM
  • Hi Jrv,

    That did not work. I only see one entry in the output csv and I am expecting to see more.

    Import-Csv C:\PowerShell\PC9999993.csv | Select Node,Flags,Category,Device,Udata,Text | Export-Csv -Path C:\PowerShell\PC9999993_1.csv –NoTypeInformation
    Import-Csv C:\PowerShell\PC9999993_1.csv | Group col4 | %{$_.group[3]} | Export-Csv -Path C:\PowerShell\PC9999993_2.csv –NoTypeInformation
    Column 4 is the column which I need to evaluate and remove duplicate entries and export only the latest entry.
    Thanks
    Friday, July 10, 2015 1:34 PM
  • I am only seeing the first entry of the input csv file on the output csv file. 
    Friday, July 10, 2015 1:43 PM
  • I am only seeing the first entry of the input csv file on the output csv file. 
    Friday, July 10, 2015 1:43 PM
  • You did not follow my example.  TO get unique values you need to reference only $_.group[0] which contains the whole CSV line.

    This is all you need:

    Import-Csv C:\PowerShell\PC9999993_1.csv |
        Group col4 |
        %{$_.group[0]} |
        Export-Csv -Path C:\PowerShell\PC9999993_2.csv –NoType


    \_(ツ)_/

    Friday, July 10, 2015 1:48 PM