Splitting csv into multiple files with header using awk

Question

I am attempting to split a csv file based on unique column values into multiple files using awk. I am able to split the csv successfully with awk -F\, '{print > $2".csv"}' example.csv however it is committing the header column from the new files.

For example:

example.csv

Color,Car,Make
Red,Kia,Spectra
Orange,Kia,Sportage
Green,Ford,Explorer
Black,Ford,F-150

Result:

Kia.csv

Red,Kia,Spectra
Orange,Kia,Sportage
___________________
Ford.csv

Green,Ford,Explorer
Black,Ford,F-150

My desired output:

Kia.csv

Color,Car,Make
Red,Kia,Spectra
Orange,Kia,Sportage
___________________
Ford.csv

Color,Car,Make
Green,Ford,Explorer
Black,Ford,F-150

To attempt getting the header column passed to the new files, I attempted something like this awk -F'|' 'FNR==1{hdr=$0;next} {if (!seen[$1]++) print hdr>$2; print>$2}' example.csv but unfortunately this did not have the intended result.

tshiono · Accepted Answer · 2022-03-17 08:32:13Z

2

You are almost there. Would you please try:

awk -F, '
    FNR==1 {header = $0; next}
    !seen[$2]++ {print header > $2".csv"}
    {print > $2".csv"}
' example.csv

If you have many varieties of car makes, "too many open files error" may occur. In such cases, please close files referring @RavinderSingh13's answer.

edited Mar 17, 2022 at 8:32

answered Mar 17, 2022 at 3:07

tshiono

22.3k2 gold badges18 silver badges26 bronze badges

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

Comments

RavinderSingh13 · Accepted Answer · 2022-03-17 04:31:31Z

2

1st solution: With your shown samples, please try following awk code.

awk -F, '
FNR==NR{
  header=$0
  next
}
{
  outputFile=$2".csv"
}
prev!=$2".csv" || !prev{
  close(prev)
  print header > (outputFile)
}
{
  print $0 > (outputFile)
  prev=outputFile
}
' <(head -1 Input_file) <(tail -n +2 Input_file | sort -t, -k2)

2nd solution: Adding solution with only 1 pass of reading Input_file.

awk -F, -v header=$(head -1 Input_file) '
{
  outputFile=$2".csv"
}
prev!=$2".csv" || !prev{
  close(prev)
  print header > (outputFile)
}
{
  print $0 > (outputFile)
  prev=outputFile
}
' <(tail -n +2 Input_file | sort -t, -k2)

answered Mar 17, 2022 at 4:31

RavinderSingh13

135k14 gold badges61 silver badges100 bronze badges

1 Comment

tshiono Over a year ago

It is a good idea (and is mandatory in most cases) to close files to avoid too many open files error.

Collectives™ on Stack Overflow

Splitting csv into multiple files with header using awk

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related