PowerShell:- Get distinct value of a column from all the files in a folder.

# the column name and position should be same in all the files

# below script will give the distinct values for the column “RACCT” across all the files in the folder

$folderPath = “Files\FAGLFLEXT_CSV\” # this is the folder where all the source csv files are present

# Destination folder for the new files. Be sure to include a backslash after the folder.
$folderPathDest = “C:\Users\Desktop\test.csv”  # extract account from all the files and save them to test.csv

$folderPathDest_Cln =”C:\Users\Desktop\test_Cln.csv” # save only distinct accounts to test_Cln.csv

# Desired columns – enclose in single quotes.
$desiredColumns = ‘RACCT’

# Generate a list of all files in the folder and pipe it to ForEach-Object
Get-ChildItem $folderPath -Name |

# Loop through each file
ForEach-Object {

# Combines source folder path and file name
$filePath = $folderPath + $_

# Combines destination folder and file name
$filePathdest = $folderPathDest + $_

# Imports CSV file, selects desired columns, and then exports as CSV to the desired destination
Import-Csv $filePath -Delimiter ‘,’ -Header “RYEAR”,”DRCRK”,”LC”,”RLDNR”,”RACCT”,”COST_ELEM”,”RBUKRS”,”RCNTR”,”PRCTR”,”RFAREA”,”KOKRS “,”SEGMENT”,”ZZLOCA”,”RASSC”,”HSLVT”,”HSL01″,”HSL02″,”HSL03″,”HSL04″,”HSL05″,”HSL06″,”HSL07″,”HSL08″,”HSL09″,”HSL10″,”HSL11″,”HSL12″,”HSL13″,”HSL14″,”HSL15″,”HSL16″ | Select $desiredColumns | Export-Csv -Append $folderPathDest -NoTypeInformation

# $NAME | Select-Object $desiredColumns -Unique | Export-Csv -Append $folderPathDest;

}

#Import-Csv $folderPathDest | sort RACCT –Unique | Export-Csv $folderPathDest_Cln -NoTypeInformation

$hs = new-object System.Collections.Generic.HashSet[string]
$sw = [System.Diagnostics.Stopwatch]::StartNew();
$reader = [System.IO.File]::OpenText($folderPathDest)
try {
while (($line = $reader.ReadLine()) -ne $null)
{
$t = $hs.Add($line)
}
}
finally {
$reader.Close()
}
$sw.Stop();
Write-Output (“read-uniq took {0}” -f $sw.Elapsed);

$sw = [System.Diagnostics.Stopwatch]::StartNew();
$ls = new-object system.collections.generic.List[string] $hs;
$ls.Sort();
$sw.Stop();
Write-Output (“sorting took {0}” -f $sw.Elapsed);

$sw = [System.Diagnostics.Stopwatch]::StartNew();
try
{
$f = New-Object System.IO.StreamWriter “$folderPathDest_Cln”;
foreach ($s in $ls)
{
$f.WriteLine($s);
}
}
finally
{
$f.Close();
}
$sw.Stop();
Write-Output (“saving took {0}” -f $sw.Elapsed);

 

Method one liner simply replace everything in the for each loop and use the below.
Import-Csv $filePath | Select Account | sort Account -Descending -Unique

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s