I have several large CSV files that I need to split based on a match in one column.

The column is called "Grading Period Title" and there are up to 10 different values. I want to separate all values of "Overall" into overall.CSV file, and all other values to term.CSV and preserve all the other columns of data.

I found this code to group and split by all the values, but I can't get it to split overall and all other values into 2 files

#Splitting a CSV file into multiple files based on column value

$groups = Import-Csv -Path "csvfile.csv" | Group-Object 'Grading Period Title' -eq '*Overall*'

$groups | ForEach-Object {$_.Group | Export-Csv "$($_.Name).csv" -NoTypeInformation}

Count Name Group

---

278 22-23 MC T2
71657 Overall
71275 22-23 T2
104 22-23 8th Blk Q2

So they are grouped, but I don't know how to select the Overall group as one file, and the rest as the 2nd file and name them.

thanks!

CodePudding user response：

To just split it, you can filter with Where-Object, for example:

# overall group
Import-Csv -Path "csvfile.csv" | 
  Where-Object { $_.'Grading Period Title' -Like '*Overall*' } |
  Export-CSV -Path "overall.csv" -NoTypeInformation

# looks like
Grading Period Title Score
-------------------- -----
Overall              5
Overall              7


# term group
Import-Csv -Path "csvfile.csv" | 
  Where-Object { $_.'Grading Period Title' -NotLike '*Overall*' } |
  Export-CSV -Path "term.csv" -NoTypeInformation

# looks like
Grading Period Title Score
-------------------- -----
22-23 MC T2          6
22-23 T2             1

CodePudding user response：

To complement the clear answer from @Cpt.Whale and do this is one iteration using the Steppable Pipeline:

Import-Csv .\csvfile.csv |
ForEach-Object -Begin {
    $Overall = { Export-CSV -notype -Path .\Overall.csv }.GetSteppablePipeline()
    $Term    = { Export-CSV -notype -Path .\Term.csv }.GetSteppablePipeline()
    $Overall.Begin($True)
    $Term.Begin($True)
} -Process {
    if ( $_.'Grading Period Title' -Like '*Overall*' ) {
        $Overall.Process($_)
    }
    else {
        $Term.Process($_)
    }

} -End {
    $Overall.End()
    $Term.End()
}

For details, see: Mastering the (steppable) pipeline.