Transpose multiple rows (with a mix of space and enter) to a single column

How to change the uploaded weekly file data to the following format?

New Well_Id,Old Well_Id,District,Thana,Date,Data,R.L,WellType,Lati.,Longi.
BAG001,PT006,BARGUNA,AMTALI,1/2/1978,1.81,2.29,Piezometer,220825,901430
BAG001,PT006,BARGUNA,AMTALI,1/9/1978,1.87,2.29,Piezometer,220825,901430
BAG001,PT006,BARGUNA,AMTALI,1/16/1978,1.98,2.29,Piezometer,220825,901430
BAG001,PT006,BARGUNA,AMTALI,1/23/1978,2.03,2.29,Piezometer,220825,901430
BAG001,PT006,BARGUNA,AMTALI,1/30/1978,2.03,2.29,Piezometer,220825,901430

Input file:

                      GROUNDWATER CIRCLE-2,EDPCELL,BWDB
                  Depth to Watertable from Measuring Point
                     Measuring Frequency Monday 0600 hrs
               End Year 88.88 Missing Data 99.99 Unit in metre
New Well_Id :  BAG001       Old Well_Id :  PT006      
District    :  BARGUNA            Thana :  AMTALI             
No. of Change  1
Well Type   Date      Village            Lati.   Longi.   R.L    P.H   Depth 
Piezometer  11.12.77  Amtali             220825  901430    2.29  0.46   8.53
Piezometer  30.12.85  Amtali             220825  901430    2.29  0.46  23.47
------------------------------------------------------------------------------
Yr. 1978  St. Date Jan 02
  1.81  1.87  1.98  2.03  2.03  2.11  2.16  2.14  2.16  2.24  2.29  2.39  2.34
  2.24  2.26  2.14  2.21  2.24  2.14  1.88  1.68  1.47  1.42  1.53  1.50  1.20
  1.20  1.17  1.12  1.14  1.14  1.17  1.12  1.14  0.99  1.17  1.42  1.45  1.40
  1.32  1.42  1.45  1.47  1.53  1.50  1.53  1.47  1.53  1.63  1.60 99.99 99.99
 88.88
Yr. 1979  St. Date Jan 01
  1.70  1.73  1.98  1.96  1.98  2.01  2.01  1.93  1.88  1.85  1.85  1.83  1.91
  1.85  1.83  1.81  1.78  1.73  1.68  1.73  1.73  1.63  1.65  1.60  1.68  1.63
  1.60  1.55  1.50  1.58  1.53  1.32  1.22  1.14  1.14  1.22  1.45  1.42  1.45
  1.40  1.42  1.45  1.53  1.58  1.53  1.73  1.63  1.65  1.70  1.75  1.63  1.75
  1.75 88.88

Here is an awk program that you can start working on.

Note that it will be a bit slow because the code is calling GNU date to perform date arithmetic:

awk '
        BEGIN {
                print "New Well_Id,Old Well_Id,District,Thana,Date,Data,R.L,WellType,Lati.,Longi."
        }
        /Well_Id/ {
                nwid = $4
                owid = $8
        }
        /District/ {
                dist = $3
                than = $6
        }
        /Well Type/ {
                getline
                wtype = $1
                lati = $4
                logi = $5
                rl = $6
        }
        /^Yr/ {
                DT = sprintf ( "%d-%s-%d", $NF, $(NF-1), $2 )
                cmd = "date -d\""DT"\" +%m/%d/%Y"
                cmd | getline DT
                close ( cmd )
        }
        /^[ ]*[0-9]+/ {
                for ( i = 1; i <= NF; i++ )
                {
                        print nwid, owid, dist, than, DT, $i, rl, wtype, lati, logi
                        cmd = "date -d\""DT" +7 days\" +%m/%d/%Y"
                        cmd | getline DT
                        close ( cmd )
                }
        }
' OFS=, BAG001.txt

Speed up date by using an associative array

BEGIN {
.
.
.
        MONTH["Jan"]=1
        MONTH["Feb"]=2
        MONTH["Mar"]=3
        MONTH["Apr"]=4
        MONTH["May"]=5
        MONTH["Jun"]=6
        MONTH["Jul"]=7
        MONTH["Aug"]=8
        MONTH["Sep"]=9
        MONTH["Oct"]=10
        MONTH["Nov"]=11
        MONTH["Dec"]=12
        }

Then when you parse for date just do...

/^Yr./          {
                year=$2
                month=MONTH[$5]
                day=int($6)
                }

I am not sure the sample output file is really consistent...it's not clear how to parse the numeric data. Next time show a complete output file.

@ Yoda, Thanks for your quick reply.

But there are some small errors that still exists. For example,

1. some extra enters (I highlighted them in the uploaded file)
2. It counted 88.88 against some dates which I dont want. (highlighted with color red in the uploaded file)

@ Yoda and others,

I uploaded a sheet of xls, for having the exact format I am looking for.

I have approximately 1265 txt files in a same folder which I need to sort similarly. Can you also suggest me a loop for this?

I really appreciate your help. Looking forward to hear you soon.