Split dynamic TSV/CSV file into 3 separate files using Bash script with Awk

Question

I have a dynamic csv/tsv file (tab delimiter) where a new row is added underneath Debit Count and Score Count every hour. No new rows will be added under Receipt Count, only the value changes each hour. Please see two examples below for reference.

Example of FileA.csv at the 3rd hour

Debit Count     VALUE
hour 1          5
hour 2          81
hour 3          15
Score Count
hour 1          31
hour 2          66
hour 3          9
Receipt Count
age logs        23
bus logs        21
pig logs        7
dog logs        40

Example of FileA.csv at the 7th hour

Debit Count     VALUE
hour 1          5
hour 2          81
hour 3          15
hour 4          20
hour 5          52
hour 6          33
hour 7          35
Score Count    
hour 1          31
hour 2          66
hour 3          9
hour 4          112
hour 5          15
hour 6          38
hour 7          21
Receipt Count  
age logs        13
bus logs        28
pig logs        85
dog logs        55

So what i'm trying to achieve is separate FileA.csv into ABC.csv , DEF.csv and GHI.csv keeping in mind that the rows underneath Debit Count and Score Count increases every hour. The new files ABC.csv,DEF.csv,GHI.csv will be REPLACED every hour

Using the 3rd hour example for reference to what i'm trying to achieve

ABC.csv

Debit Count     VALUE
hour 1          5
hour 2          81
hour 3          15

DEF.csv

Score Count  
hour 1          31
hour 2          66
hour 3          9

GHI.csv

Receipt Count
age logs        23
bus logs        21
pig logs        7
dog logs        40

What I tried doing (Edited)

awk f="ABC.csv DEF.csv GHI.csv" '
  BEGIN {split(f,files)} /^Debit/ /^Score/ /^Receipt/ {n++} {print>files[n]}' FileA.csv

---

This question was closed for lack of focus in a previous post and i was given the option to either edit or re-post the question. I decided to re-post the question with better clarity so others that may have seen it before could see it again. Thanks

Answer 1

Use awk to change the output file when you get to each header line.

awk '/Debit Count/ { of="ABC.csv" }
     /Score Count/ { of="DEF.csv" }
     /Receipt Count/ { of="GHI.csv" }
     {print >of}' FileA.csv

Answer 2

This might work for you (GNU csplit):

csplit -szfX -n1 file '/^[DSR]/' '{*}' && mv X0 ABC.csv && mv X1 DEF.csv && mv X2 GHI.csv

Split file based on the first character of the headers. The -z option elides the empty first file and the -s keeps the output silent. The remaining options are unnecessary but make for a neat one-liner.

Answer 3

Your code under "What I tried doing (Edited)":

awk f="ABC.csv DEF.csv GHI.csv" '
  BEGIN {split(f,files)} /^Debit/ /^Score/ /^Receipt/ {n++} {print>files[n]}' FileA.csv

was close, it should have been:

awk -v f="ABC.csv DEF.csv GHI.csv" '
  BEGIN {split(f,files)} /^Debit/ || /^Score/ || /^Receipt/ {n++} {print>files[n]}' FileA.csv

or:

awk -v f="ABC.csv DEF.csv GHI.csv" '
  BEGIN {split(f,files)} /^(Debit|Score|Receipt)/ {n++} {print>files[n]}' FileA.csv

By the way, your input file is neither CSV, nor TSV. It's not CSV as it's not comma-separated, it's tab-separated, and it's neither because both formats only have 1 header row as the first line, they don't have multiple header rows within the file. So it's just a plain old text file, with some headers sprinkled throughout and tab-separated name-value pairs under the headers and so your input file should be named FileA.txt, not FileA.csv nor FileA.tsv.

Even your 2nd 2 output files aren't TSV since the header line is just a string about the file, not the names of the 2 columns in the file - if you changed Receipt<blank>Count to Receipt<tab>Count or Receipt Count<tab>Value or similar to have column names THEN it'd be valid TSV and you could name your output files GHI.tsv, etc. The file suffixes matter for other people or subsequent tools understanding their contents.

You also don't actually need to test for the strings Debit, etc. you just need to change output file every time $2 is empty and I don't see any benefit to passing the output file names in with a variable rather than just making them a string in the script.

So your code should really be:

$ awk '
    BEGIN {
        split("ABC.tsv DEF.tsv GHI.tsv",files)
        FS = OFS = "\t"
    }
    NR == 1  { hd=$2; n++ }
    $2 == "" { $2=hd; n++ }
    { print > files[n] }
' FileA.txt

$ head *.tsv
==> ABC.tsv <==
Debit Count     VALUE
hour 1  5
hour 2  81
hour 3  15
hour 4  20
hour 5  52
hour 6  33
hour 7  35

==> DEF.tsv <==
Score Count     VALUE
hour 1  31
hour 2  66
hour 3  9
hour 4  112
hour 5  15
hour 6  38
hour 7  21

==> GHI.tsv <==
Receipt Count   VALUE
age logs        13
bus logs        28
pig logs        85
dog logs        55