jam1989 jam1989 - 3 months ago 12
PowerShell Question

Select and export minimum value from CSV using Powershell

I have two questions regarding exporting information from a CSV file using powershell. I really don't have much experience coding/using powershell and I've been hacking bits of code together from online tutorials/forums etc.

I have a folder containing CSV files which have numerous columns. The first piece of code I'm writing is to extract every row containing "CSEP DDS Matched" or "CSEP Unmatched" from each file ending "_HIPOTS.csv" and export it to a new file. The code I have is:

$path = "S:\TEST AREA\ac00418\OpsPlan\Test\Output\FY1\*.*"; # Directory containing csv files

$csvFiles = Get-ChildItem -Path $path -Filter *_HIPOTS.csv; # Get the csv files
$destination = "S:\TEST AREA\ac00418\OpsPlan\Test\Output\FY1_CSEP_HighPotentials.csv"

#$content = $null
$content = @()

ForEach($csv in $csvFiles){
#IMPORT
$nodes = import-csv $csv.Fullname
$CSEPs = $nodes|where {$_.NodeSymbolName -match "CSEP DDS Matched" -and $_.NodeSymbolName -match "CSEP Unmatched"}
$allCSEPs += $CSEPs
}

$allCSEPs|Export-csv $destination -NoTypeInformation


The problem is that when I run this, it's not searching for "CSEP Unmatched" and also it seems to run through the for loop two or three times as there seem to be a lot of duplicates in the export. Can anyone help with this?

The second is issue is I have to export the row containg lowest value from another column for each file. I have a similar code to the first except that I have no idea how to pick a minimum value out of a column:

$path = "S:\TEST AREA\ac00418\OpsPlan\Test\Output\FY1\*.*"; # Directory containing csv files
$csvFiles = Get-ChildItem -Path $path -Filter *_HIPOTS.csv; # Get the csv files
$destination = "S:\TEST AREA\ac00418\OpsPlan\Test\Output\LowestNodePressure.csv"

#$content = $null
$content = @()



ForEach($csv in $csvFiles){
#IMPORT
$nodes = import-csv $csv.Fullname
$min = Measure-Object -Property $_.NodePressure -Minimum

$minpressure += $min
}

$minpressure|Export-csv $destination -NoTypeInformation


Here is an example of how the information appears. Bear in mind that there are thousands of these and more columns. These are just the relevant ones for the code I'm trying to write.

NAME NodeSymbolName NodePressure NodeResultFlow
6430050001023 CSEP Unmatched 24 -25
60200100000007 CSEP DDS Matched 49 -106
60200100000072 CSEP DDS Matched 25 -157
60200100000148 CSEP DDS Matched 35 -9


I apologise for the massive post but I figure I may as well kill two birds with one stone! Hopefully I've been clear enough on what I'm trying to achieve. I appreciate any help anyone can give.

Thanks,

J

Answer

Ok, let's wrap this up in an answer and not just comments. There's a few issues here, so let's start at the top:

Not collecting both 'CSEP DDS Matched' and 'CSEP Unmatched'

Sure, no problem. As mentioned in the comments, you want to use the -or operator, not the -and operator. Corrected line:

        $CSEPs = $nodes|where {$_.NodeSymbolName -match "CSEP DDS Matched" -or $_.NodeSymbolName -match "CSEP Unmatched"}

Better yet, the -match operator works off Regular Expressions (commonly referred to as RegEx), so we can shorten that whole thing to:

        $CSEPs = $nodes|where {$_.NodeSymbolName -match "CSEP (DDS |un)Matched"}

RegEx looks at the things inside the parenthesis and uses the pipe as a delimiter matching any of the options in there, so that works for both cases. Neat huh? Moving on!

Duplicates

Yeah, nobody likes those, but I'm willing to bet this is a matter of re-using your session and having dirty variables. The best way to take care of that is to remove the variables at the end of the script. While you're debugging, just comment out the lines if needed, then just un-comment them when you're ready.

Remove-Variable allCSEPs, nodes, CSEPs, LowestNode

Lowest NodePressure for each file

Ok, since you want it for each file you should be collecting these as you go through the files in the first place, so let's just add a line in your initial loop. There's no reason to import these CSVs more than once right? We'll look at your matches from the first loop, sort them, and then just select the first one, and add that one record to a different variable to be output separately.

[array]$LowestNodes += $CSEPs | Sort NodePressure | Select -First 1

See what I did there? I declared $LowestNode as an array, so I can just gather a collection of objects or strings, or whatever, using += and I don't have to declare it as an empty array first.

So with that you have 1 loop (to rule them all), and it could look something like this:

$path = "S:\TEST AREA\ac00418\OpsPlan\Test\Output\FY1\*.*"; # Directory containing csv files 

$csvFiles = Get-ChildItem -Path $path -Filter *_HIPOTS.csv; # Get the csv files
$destination = "S:\TEST AREA\ac00418\OpsPlan\Test\Output\FY1_CSEP_HighPotentials.csv"
$destination2 = "S:\TEST AREA\ac00418\OpsPlan\Test\Output\LowestNodePressure.csv"

ForEach($csv in $csvFiles){
#IMPORT
        $nodes = import-csv $csv.Fullname
        $CSEPs = $nodes|where {$_.NodeSymbolName -match "CSEP (DDS |un)Matched"}
        [array]$LowestNodes += $CSEPs | Sort NodePressure | Select -First 1
        [array]$allCSEPs += $CSEPs
}

$allCSEPs|Export-csv $destination -NoTypeInformation
$LowestNodes|Export-csv $destination2 -NoTypeInformation
Remove-Variable allCSEPs, nodes, CSEPs, LowestNodes