Note also that gsub(ERE, replacement, string) returns the number of substitutions performed, not the modified string. And, since there are multiple commas on the Score line, it might make more sense to get rid of all of the commas on the input line in one shot before splitting out the fields than to perform gsub() calls on the split fields. In fact, if we set the field separator differently, we can avoid the need to perform any substitutions. The following creates a comma separated values file that can easily be loaded into Excel:

Code:

awk '
BEGIN { FS = "[ ,]*"
        OFS = ","
        print "Serial", "Query start", "Query End", "target start", "target end", "Score", "E", "P", "GC"
}
/^>/ {  S = $1 }
/Query/ {
        QS = $4
        QE = $6
        TS = $9
        TE = $11
}
/Score/ {
        print S, QS, QE, TS, TE, $4, $7, $10, $13
}' "Input file.txt"

which, with your sample input produces:

Code:

Serial,Query start,Query End,target start,target end,Score,E,P,GC
>gi|1234|ref|,1,65,1677,1733,8.38,0.6529,0.0001513,46
>gi|54367|ref|,1,65,6780,6812,8.13,0.7692,0.0001782,36

If you save this output in a file named input.csv and open it with Excel, you'll get your desired spreadsheet. Then, if .csv format isn't suitable, you can have Excel save it to a file in .xlsx format.

Note that you can give this script multiple input files which will still produce a single .csv output file. You might, however, run into ARG_MAX limitations on your system if you replace the last line of the script with:

Code:

}' *.txt

with over a thousand .txt files. I'm sure you can figure out how to group sets of .txt files into suitable chunks and combine the resulting output files (stripping off the header in all output files except the first one) to get a single, large .csv file to feed to Excel.