Combining multiple data files with header while also adding a data column

Question

I have multiple tab delimited data files that are separated by month in the format jan06.txt, feb06.txt, ..., dec07.txt.

Within each file, it looks something like:

Header1 Header2 Header3 ...
Data1   Data2   Data3   ...
Data4   Data5   Data6   ...
...     ...     ...

What I want to do is to combine all all the data files into a single data file, with just a single header at the top, but also include a new data column that contains the month and year so I don't lose that information from the file name. So my new, single data file would contain:

Date   Header1 Header2 Header3 ...
200601 Data1   Data2   Data3   ...
200602 Data4   Data5   Data6   ...
...    ...     ...     ...

Where 200601 would refer to Jan 06, 200602 refer to Feb 06, etc.

I know if I do something like cat *.txt > data.txt, I could combine all my files. However, the two issues remain:

1. I have a header in every file that would get concatenated, which I don't want.
2. I would lose the month information that is stored in the file name.

I think I can do this with some combination of cat and sed, but I am not sure how to start.

Answer 1

Here's an awk program that should do what you need:

awk '
    BEGIN {
        # create an array so we can map the month number to month name
        split("jan feb mar apr may jun jul aug sep oct nov dec", months)
    }
    function filename2date(filename,         month, year, i) {
        month = substr(filename, 1, 3)
        year = substr(filename, 4, 2)
        for (i=1; i<=12; i++) 
            if (months[i] == month) 
                return sprintf("20%s%02d", year, i)
        return filename
    }
    NR == 1  { 
        # this is the first line of the first file
        print "Date", $0 
    }
    FNR == 1 { 
        # this is the first line of each file
        date = filename2date(FILENAME)
        next
    }
    { print date, $0 }
' ???[0-9][0-9].txt > data.txt