PowerShell Tricks: How to use PowerShell for manipulating CSV file? - Part 1

Often all service managers will ask the team for data in CSV format for easy manipulation. The discussion while collating and manipulating CSV file is fun and the discussion continues.

As a PowerShell scriptwriter we just do Export-Csv and stay idle and out of the discussions. We just say sorry 'no good Excel knowledge'.

Just to learn and dig some tricks, we used the below PowerShell codes for manipulating CSV data.

Column1	Column1
Value1	Value1
Value1	Value2
Value4	Value3
Value5	Value4

Consider we have information as CSV file. Nothing big to do with this, but definitely, this trick can be applied to larger data too.

How to get information from Column 1?

Import-Csv C:\Temp\PSExam.CSV | %{$_.Column1}

Value1
Value1
Value4
Value5

The same can be applied for Column 2 by changing {$_.Column2}

How to get UNIQUE values from Column 1?

Import-Csv C:\Temp\PSExam.CSV | %{$_.Column1 } | Group-Object | Where {$_.Count -eq 1} | Select -Property Name

Wow, that's good news and  got a result:
Value4
Value5

How to get a count of DUPLICATES?

Import-Csv C:\Temp\PSExam.csv  | %{$_.Column1 } | Group-Object

Use Group-Object to achieve this:

Count        Name                      Group                                                                                                                                       
-----          ----                              -----                                                                                                                                       
    3          Value1                    {Value1, Value1, Value1}                                                                                                                    
    1          Value4                    {Value4}                                                                                                                                    
    1          Value5                    {Value5}

How to remove duplicates?

$data = Import-Csv C:\Users\904870\Desktop\PSExam.csv  | %{$_.Column1 } | Group-Object
$data.Name

Value1
Value4
Value5

We will see comparing two columns and files in next article.

Enjoy PowerShell!!!