# 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
