Powershell--split CSV into 2 files based on column value

Question

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.

Grading Period Title	Score
Overall	5
22-23 MC T2	6
Overall	7
22-23 T2	1

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!

Cpt.Whale · Accepted Answer · 2023-02-02 19:31:20Z

2

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

answered Feb 2, 2023 at 19:31

Cpt.Whale

5,3911 gold badge15 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

iRon · Accepted Answer · 2023-02-03 09:28:37Z

1

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.

answered Feb 3, 2023 at 9:28

iRon

24.4k10 gold badges60 silver badges107 bronze badges

Collectives™ on Stack Overflow

Powershell--split CSV into 2 files based on column value

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related