Merge multiple CSV files from subfolders into a unique file and add file name in a new column

Question

I have a bunch of CSV files in many subfolders (only 1 level of subfolders from the parent directory, nothing is nested further).

Each CSV file has three columns; i, k, v with a mix of string and number values.
Delimiter is ;.
Sometimes, ';' is part of a string. If so, it's escaped with a backslash \;.

Content sample for a single file subfoler1/file001.csv;

index,key,value
0,Contact,mailto:[email protected]\;
1,IDG,"44.1\,80.1"
2,Information,https://www.sys.admin.org/\;

I'd like to merge them into a unique CSV file, while appending the filename to a new column, let's say 'filename';

index,key,value,filename
0,Contact,mailto:[email protected]\;subfolder1/file001.csv
1,IDG,"44.1\,80.1"subfolder1/file001.csv
2,Information,https://www.sys.admin.org/\;subfolder1/file001.csv
0,Contacts,mailto:[email protected]\;subfolder2/file002.csv
1,IDG,"5.2\,7.4";subfolder2/file002.csv

If this makes things much easier, it's ok to skip the folder name in the last column, taking only the filename.

I've done that once in the past;

awk '{print $0";"FILENAME}' .*.csv > merged.csv

But files have to be a single folder, no spread out in multiple subfolders.

How could I modify this command to get my desired result?

Answer 1

Untested:

awk -v OFS=';' '
    NR == 1 { print $0, "filename" }
    FNR > 1 { print $0, FILENAME }
' */*.csv

This prints the header row only from the first file.