Wednesday, February 9, 2011

Powershell to help you to sort, manipulate or consolidate CSV file

To help you to sort, manipulate or consolidate your information, Powershell is a very interesting tool.

First, you need a .csv file with this format (use excel file save as)


col1,col2,col3
c,cc,ccc
a,aa,aaa
b,bb,bbb
c,cc,ccc
b,bb,bbb


For example, to select only one column, just use colum name

Import-Csv .\test.csv | select col2



col2
----
cc
aa
bb
cc
bb


If your .CSV has ";" and not "," as delimiter you can change this by adding -Delimiter switch like that


Import-Csv .\test.csv -Delimiter ";" | select col2

(Warning: This switch does not work on Powershell 1.0)

Moreover, be sure to have a header else you will have a error
Import-Csv : The member "xxxx" is already present
And do not forget # is considered as a remark

Now, if you only need distinct col2 use -unique switch and Sort-object if you need to have only distinct field

Import-Csv .\test.csv | select -unique col2 | Sort-Object col2



col2
----
aa
bb
cc


To display each line with column 3 then column 1 then column 2 you can do like this

$content = import-csv ".\test.csv"
foreach ($line in $content) {
write-host $line.col3 $line.col2 $line.col1
}


result:

ccc c cc
aaa a aa
bbb b bb
ccc c cc
bbb b bb


or like this:

Import-Csv .\test.csv | select col3, col1, col2


result:

col3 col1 col2
---- ---- ----
ccc c cc
aaa a aa
bbb b bb
ccc c cc
bbb b bb


Or like this:

$Content = Get-Content ".\test.csv"
$arrLine = @( )
Foreach ($line in $Content) {
$line =$line.Trim()
If ($line.length -gt 0) {
$arrLine=$line.split(",")
write-host $arrLine[2] $arrLine[0] $arrLine[1]
}
}


result:

col3 col1 col2
ccc c cc
aaa a aa
bbb b bb
ccc c cc
bbb b bb

1 comment:

Denis Couto said...

The explanation about the # symbol made my day! Tks for sharing!