How can I update an Awk command to work with a newer version of MacOS?

Question

I am using the following Awk command which is based on this Stack Exchange post:

tail -n +2 *.csv | sort -t',' -k2 | awk -F',' '$2~/^[[:space:]]*$/{next} {sub(/\r$/,"")} $2!=prev{close(out); out=$2".txt"; prev=$2} {print $1 > out}'

The command works perfectly under MacOS 10.14. However, I recently upgraded to MacOS 12.6 and it no longer works. (MacOS 12.6 uses awk version 20200816).

It produces the following error:

awk: newline in regular expression ... at source line 1
 context is
    $2~/^[[:space:]]*$/{next} {sub(/ >>> 
 <<< 
awk: syntax error at source line 1
awk: illegal statement at source line 1

How can I get it working again and ideally (if possible) make it more future proof, without having to install any extra software. I looked at the changes made to awk, but can't find anything that would cause it to stop working.

---

Background

The command takes all CSV files in a directory. It splits the file into text files according to the values of the second column of the CSV file while only keeping the values stored in the first column.

Example CSV file:

COLUMN 1,COLUMN 2
innovation "is essential",3-Entrepreneurship
countless,
innocent,2-Police
toilet handle,2-Bathroom
née dresses,3-Companies
odorless,2-Sense of Smell
old ideas "new takes",3-Entrepreneurship
new income streams,3-Entrepreneurship
Zoë’s food store,3-Companies
many,
crime "doesn't sleep",2-Police
bath room,2-Bathroom
ring,
móvíl résumés,3-Companies
musty smell's come here,2-Sense of Smell
good publicity guru,3-Entrepreneurship
Señor,3-Companies

E.g. after split

In file 3-Entrepreneurship.txt

innovation "is essential"
old ideas "new takes"
new income streams
good publicity guru

In file 2-Bathroom.txt

toilet handle
bath room

In file 2-Police.txt

innocent
crime "doesn't sleep"

In file 2-Sense of Smell.txt

odorless
musty smell's come here

In file 3-Companies.txt

née dresses
Zoë’s food store
móvíl résumés
Señor

Answer 1

The solution I posted nearly 3 years ago still works:

# the files produced must not exist prior to the run
awk -F, 'FNR>1 && $2 {print $1 >> ($2 ".txt"); close($2 ".txt")}' file.csv

Produces:

$ head *.txt
==> 2-Bathroom.txt <==
toilet handle
bath room

==> 2-Police.txt <==
innocent
crime "doesnt sleep"

==> 2-Sense of Smell.txt <==
odorless
musty smells come here

==> 3-Companies.txt <==
née dresses
Zoë’s food store
móvíl résumés
Señor

==> 3-Entrepreneurship.txt <==
innovation "is essential"
old ideas "new takes"
new income streams
good publicity guru

Or, here is a Ruby:

ruby -r csv -e '
CSV.parse($<.read, **{:headers=>true, :liberal_parsing=>true}).
    select{|r| r["COLUMN 2"]}.
    group_by{|r| r["COLUMN 2"]}.
    each{|k,v| File.write("#{k}.txt", v.map(&:first).map(&:last).join("\n")) 
}
' file.csv
# same output

Answer 2

Looks like it's treating the \r as a literal linefeed (possible issue with using smart quotes?).

You might try, say, replacing \r with \x0d to see if that make a difference.