Generalize an awk program that concatenates csv files and adds a column

I have these two csv files:

$ cat numbers.csv
h1,h2
1, 2
3, 4

$ cat letters.csv
h1,h2
a, b
c, d

I want to concatenate both csv files and I want to add a new column with the value letters or columns if the row comes from letters.csv or numbers.csv so I wrote this awk command (because I'm trying out awk) that does the job:

$ awk 'NR==1 {print new_column","$0} fname != FILENAME { fname = FILENAME; idx++ } FNR > 1 && idx == 1 { print group1","$0 } FNR > 1 && idx == 2 { print group2","$0 }' new_column=type group1=letters group2=numbers letters.csv numbers.csv

new_column is of the column that is going to be added

group1 is the is the value of new_column if the row is added from the letters.csv file

group2 is the is the value of new_column if the row is added from the numbers.csv file

The output I get is:

type,h1,h2
letters,a, b
letters,c, d
numbers,1, 2
numbers,3, 4

The problem is that I don't know if there is a way to make the awk program run with n arguments and n files without having to rewrite it everytime like so:

$ awk '....' new_column=group_name group1=letters group2=numbers ... groupN=whatever  letters.csv numbers.csv .... morefiles_to_N.csv

Maybe like this

% awk 'NR==1{print "type,"$0} 
       FNR==1{nm=FILENAME; sub(/\..*/,"",nm); next} 
             {print nm","$0}' num.csv let.csv
type,h1,h2
num,1, 2
num,3, 4
let,a, b
let,c, d

I suggest taking look at using single variable for ramming all needed names and then deserialize it using split function, consider simple example, let say I have file1.txt with following content

1
2
3

file10.txt with following content

10
20

and file100.txt with following content

100

and want to fuse them, giving names ABLE, BAKER, CHARLIE for data from respective filess, then I could do

awk -v names=ABLE:BAKER:CHARLIE 'BEGIN{split(names,arr,/:/)}FNR==1{i+=1}{print arr[i] "," $0}' file1.txt file10.txt file100.txt

gives output

ABLE,1
ABLE,2
ABLE,3
BAKER,10
BAKER,20
CHARLIE,100

Explanation: I elected : for separating names, when making choice keep in mind separator MUST NOT be present in any name. In BEGIN I split names at : into array arr, so arr[1] becomes ABLE, arr2 becomes BAKER, arr3 becomes CHARLIE. For each first row of file (FNR) I increase i by 1. For each row I print name concatenated with whole line ($0) using , character.

(tested in GNU Awk 5.1.0)

awk -v header=type -v groups=letters,numbers '
    BEGIN{ OFS=","; split(groups, grps,",") } 
    FNR==1{i++} 
    NR==1{ print header,$0; next } 
    FNR!=1{ print grps[i],$0 }
' letters.csv numbers.csv

type,h1,h2
letters,a, b
letters,c, d
numbers,1, 2
numbers,3, 4

I'm adding a no awk answer because I know you are looking for a solution using also other tools (as qsv).

The tool I use for this kind of task is Miller. Running

mlr --csv put '$type=sub(FILENAME,"\..+","")' letters.csv numbers.csv

you get

h1,h2,type
a, b,letters
c, d,letters
1, 2,numbers
3, 4,numbers

put is the verb to build Miller expressions. Here I create the new field type, I put it equal to the FILENAME built-in variable. I use the sub function to remove the file extension.

If you want type ad the first field simply add reorder -f type

mlr --csv put '$type=sub(FILENAME,"\..+","")' then reorder -f type letters.csv numbers.csv