DSXchange

We receive the csv file from an external vendor as below.

Input File:

"DATES ""INSTRUMENT(MNEMONIC)"" ""DATATYPES"" ""12-31-71"" ""06-30-11"" ""M"" 475 ""12-31-71"" ""01-31-72"" ""02-29-72"" ""03-31-72"" ""04-30-72"" ""05-31-72"" ""06-30-72"" ""07-31-72"" ""08-31-72"" ""09-30-72"" ""10-31-72"" ""11-30-72"" ""12-31-72"" ""01-31-73"" ""02-28-73"" ""03-31-73"" ""04-30-73"" ""05-31-73"" ""06-30-73"" ""07-31-73"" ""08-31-73"" ""09-30-73"" ""10-31-73"" ""11-30-73"" ""12-31-73"" ""01-31-74"" ""02-28-74"" ""03-31-74"" ""04-30-74"" ""05-31-74"" ""06-30-74"" ""07-31-74"" ""08-31-74"" ""09-30-74"" ""10-31-74"" ""11-30-74"" ""12-31-74"" ""01-31-75"" ""02-28-75"" ""03-31-75"" ""04-30-75"" ""05-31-75"" ""06-30-75"" ""07-31-75"" ""08-31-75"" ""09-30-75"" ""10-31-75"" ""11-30-75"" ""12-31-75"" ""01-31-76"" ""02-29-76"" ""03-31-76"" ""04-30-76"" ""05-31-76"" ""06-30-76"" ""07-31-76"" ""08-31-76"" ""09-30-76"" ""10-31-76"" ""11-30-76"" ""12-31-76"" ""01-31-77"" ""02-28-77"" ""03-31-77"" ""04-30-77"" ""05-31-77"" ""06-30-77"" ""07-31-77"" ""08-31-77"" ""09-30-77"" ""10-31-77"" ""11-30-77"" ""12-31-77"" ""01-31-78"" ""02-28-78"" ""03-31-78"" ""04-30-78"" ""05-31-78"" ""06-30-78"" ""07-31-78"" ""08-31-78"" ""09-30-78"" ""10-31-78"" ""11-30-78"" ""12-31-78"" ""01-31-79"" ""02-28-79"" ""03-31-79"" ""04-30-79"" ""05-31-79"" ""06-30-79"" ""07-31-79"" ""08-31-79"" ""09-30-79"" ""10-31-79"" ""11-30-79"" ""12-31-79"" ""01-31-80"" ""02-29-80"" ""03-31-80"" ""04-30-80"" ""05-31-80"" ""06-30-80"" ""07-31-80"" ""08-31-80"" ""09-30-80"" ""10-31-80"" ""11-30-80"" ""12-31-80"" ""01-31-81"" ""02-28-81"" ""03-31-81"" ""04-30-81"" ""05-31-81"" ""06-30-81"" ""07-31-81"" ""08-31-81"" ""09-30-81"" ""10-31-81"" ""11-30-81"" ""12-31-81"" ""01-31-82"" ""02-28-82"" ""03-31-82"" ""04-30-82"" ""05-31-82"" ""06-30-82"" ""07-31-82"" ""08-31-82"" ""09-30-82"" ""10-31-82"" ""11-30-82"" ""12-31-82"" ""01-31-83"" ""02-28-83"" ""03-31-83"" ""04-30-83"" ""05-31-83"" ""06-30-83"" ""07-31-83"" ""08-31-83"" ""09-30-83"" ""10-31-83"" ""11-30-83"" ""12-31-83"" ""01-31-84"" ""02-29-84"" ""03-31-84"" ""04-30-84"" ""05-31-84"" ""06-30-84"" ""07-31-84"" ""08-31-84"" ""09-30-84"" ""10-31-84"" ""11-30-84"" ""12-31-84"" ""01-31-85"" ""02-28-85"" ""03-31-85"" ""04-30-85"" ""05-31-85"" ""06-30-85"" ""07-31-85"" ""08-31-85"" ""09-30-85"" ""10-31-85"" ""11-30-85"" ""12-31-85"" ""01-31-86"" ""02-28-86"" ""03-31-86"" ""04-30-86"" ""05-31-86"" ""06-30-86"" ""07-31-86"" ""08-31-86"" ""09-30-86"" ""10-31-86"" ""11-30-86"" ""12-31-86"" ""01-31-87"" ""02-28-87"" ""03-31-87"" ""04-30-87"" ""05-31-87"" ""06-30-87"" ""07-31-87"" ""08-31-87"" ""09-30-87"" ""10-31-87"" ""11-30-87"" ""12-31-87"" ""01-31-88"" ""02-29-88"" ""03-31-88"" ""04-30-88"" ""05-31-88"" ""06-30-88"" ""07-31-88"" ""08-31-88"" ""09-30-88"" ""10-31-88"" ""11-30-88"" ""12-31-88"" ""01-31-89"" ""02-28-89"" ""03-31-89"" ""04-30-89"" ""05-31-89"" ""06-30-89"" ""07-31-89"" ""08-31-89"" ""09-30-89"" ""10-31-89"" ""11-30-89"" ""12-31-89"" ""01-31-90"" ""02-28-90"" ""03-31-90"" ""04-30-90"" ""05-31-90"" ""06-30-90"" ""07-31-90"" ""08-31-90"" ""09-30-90"" ""10-31-90"" ""11-30-90"" ""12-31-90"" ""01-31-91"" ""02-28-91"" ""03-31-91"" ""04-30-91"" ""05-31-91"" ""06-30-91"" ""07-31-91"" ""08-31-91"" ""09-30-91"" ""10-31-91"" ""11-30-91"" ""12-31-91"" ""01-31-92"" ""02-29-92"" ""03-31-92"" ""04-30-92"" ""05-31-92"" ""06-30-92"" ""07-31-92"" ""08-31-92"" ""09-30-92"" ""10-31-92"" ""11-30-92"" ""12-31-92"" ""01-31-93"" ""02-28-93"" ""03-31-93"" ""04-30-93"" ""05-31-93"" ""06-30-93"" ""07-31-93"" ""08-31-93"" ""09-30-93"" ""10-31-93"" ""11-30-93"" ""12-31-93"" ""01-31-94"" ""02-28-94"" ""03-31-94"" ""04-30-94"" ""05-31-94"" ""06-30-94"" ""07-31-94"" ""08-31-94"" ""09-30-94"" ""10-31-94"" ""11-30-94"" ""12-31-94"" ""01-31-95"" ""02-28-95"" ""03-31-95"" ""04-30-95"" ""05-31-95"" ""06-30-95"" ""07-31-95"" ""08-31-95"" ""09-30-95"" ""10-31-95"" ""11-30-95"" ""12-31-95"" ""01-31-96"" ""02-29-96"" ""03-31-96"" ""04-30-96"" ""05-31-96"" ""06-30-96"" ""07-31-96"" ""08-31-96"" ""09-30-96"" ""10-31-96"" ""11-30-96"" ""12-31-96"" ""01-31-97"" ""02-28-97"" ""03-31-97"" ""04-30-97"" ""05-31-97"" ""06-30-97"" ""07-31-97"" ""08-31-97"" ""09-30-97"" ""10-31-97"" ""11-30-97"" ""12-31-97"" ""01-31-98"" ""02-28-98"" ""03-31-98"" ""04-30-98"" ""05-31-98"" ""06-30-98"" ""07-31-98"" ""08-31-98"" ""09-30-98"" ""10-31-98"" ""11-30-98"" ""12-31-98"" ""01-31-99"" ""02-28-99"" ""03-31-99"" ""04-30-99"" ""05-31-99"" ""06-30-99"" ""07-31-99"" ""08-31-99"" ""09-30-99"" ""10-31-99"" ""11-30-99"" ""12-31-99"" ""01-31-00"" ""02-29-00"" ""03-31-00"" ""04-30-00"" ""05-31-00"" ""06-30-00"" ""07-31-00"" ""08-31-00"" ""09-30-00"" ""10-31-00"" ""11-30-00"" ""12-31-00"" ""01-31-01"" ""02-28-01"" ""03-31-01"" ""04-30-01"" ""05-31-01"" ""06-30-01"" ""07-31-01"" ""08-31-01"" ""09-30-01"" ""10-31-01"" ""11-30-01"" ""12-31-01"" ""01-31-02"" ""02-28-02"" ""03-31-02"" ""04-30-02"" ""05-31-02"" ""06-30-02"" ""07-31-02"" ""08-31-02"" ""09-30-02"" ""10-31-02"" ""11-30-02"" ""12-31-02"" ""01-31-03"" ""02-28-03"" ""03-31-03"" ""04-30-03"" ""05-31-03"" ""06-30-03"" ""07-31-03"" ""08-31-03"" ""09-30-03"" ""10-31-03"" ""11-30-03"" ""12-31-03"" ""01-31-04"" ""02-29-04"" ""03-31-04"" ""04-30-04"" ""05-31-04"" ""06-30-04"" ""07-31-04"" ""08-31-04"" ""09-30-04"" ""10-31-04"" ""11-30-04"" ""12-31-04"" ""01-31-05"" ""02-28-05"" ""03-31-05"" ""04-30-05"" ""05-31-05"" ""06-30-05"" ""07-31-05"" ""08-31-05"" ""09-30-05"" ""10-31-05"" ""11-30-05"" ""12-31-05"" ""01-31-06"" ""02-28-06"" ""03-31-06"" ""04-30-06"" ""05-31-06"" ""06-30-06"" ""07-31-06"" ""08-31-06"" ""09-30-06"" ""10-31-06"" ""11-30-06"" ""12-31-06"" ""01-31-07"" ""02-28-07"" ""03-31-07"" ""04-30-07"" ""05-31-07"" ""06-30-07"" ""07-31-07"" ""08-31-07"" ""09-30-07"" ""10-31-07"" ""11-30-07"" ""12-31-07"" ""01-31-08"" ""02-29-08"" ""03-31-08"" ""04-30-08"" ""05-31-08"" ""06-30-08"" ""07-31-08"" ""08-31-08"" ""09-30-08"" ""10-31-08"" ""11-30-08"" ""12-31-08"" ""01-31-09"" ""02-28-09"" ""03-31-09"" ""04-30-09"" ""05-31-09"" ""06-30-09"" ""07-31-09"" ""08-31-09"" ""09-30-09"" ""10-31-09"" ""11-30-09"" ""12-31-09"" ""01-31-10"" ""02-28-10"" ""03-31-10"" ""04-30-10"" ""05-31-10"" ""06-30-10"" ""07-31-10"" ""08-31-10"" ""09-30-10"" ""10-31-10"" ""11-30-10"" ""12-31-10"" ""01-31-11"" ""02-28-11"" ""03-31-11"" ""04-30-11"" ""05-31-11"" ""06-30-11"""
"712232 ""FR"" ""PCH" "1M)*1.000"" ""12-31-71"" ""06-30-11"" ""M"" 475 ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" 10.277 -22.581 -7.87 -19.598 26.875 -15.271 16.279 -14 -4.651 60.976 57.576 -21.635 -2.454 20.755 -21.875 -12.667 -40.458 43.59 -14.286 -43.229 35.78 -31.081 -15.196 131.214 -16.96 -27.746 -6.667 -48.286 3.801 -22.823 -1.724 -17.193 -38.983 -16.667 -8.333 -31.818 -78.667 -6.25 173.333 -75.61 0 70 88.235 56.25 -20 32.5 -18.868 -30.233 16.667 28.571 11.111 -10 -22.222 -14.286 0 -40 11.111 75 0 -28.571 -24 10.526 19.048 -36 0 87.5 -20 -29.167 5.882 -50 11.111 -40 0 0 -41.667 -37.143 -54.545 700 -62.5 -46.667 6.25 5.882 -11.111 -6.25 -13.333 -23.077 40 -7.143 -7.692 0 -45.833 -7.692 66.667 80 11.111 40 185.714 -25 -50 -33.333 1 147.525 140 -41.667 28.571 -22.222 -14.286 8.333 -23.077 -18 -2.439 22.5 -69.388 20 172.222 -65.306 17.647 25 -40 0 -33.333 -40 0 -5 338.596 -50.4 -0.806 46.341 -44.444 0 -10 -10 -55.556 177.778 -59 90.244 156.41 -25 -33.333 -40 50 -55.556 0 -62.5 53.333 4.348"
"12121K ""FR"" ""PCH" "1M)*1.000"" ""12-31-71"" ""06-30-11"" ""M"" 475 ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" 14.667 -22.093 161.194 0 0 -0.571 36.782 -20.168 -7.895 -2.857 -52.941 25 25 4 0 80.769 -14.894 -10 -46.111 -23.711 -48.649 -18.421 -19.355 -36 -6.25 126.667 -23.529 -3.846 -32 -35.294 -9.091 -30 35.714 -21.053 -20 -46.667 -6.25 133.333 -14.286 -23.333 0 -23.913 -8.571 -6.25 -20 0 16.667 -28.571 -30 -14.286 66.667 -40 -41.667 14.286 25 0 -60 -40 0 400 33.333 75 142.857 -61.765 -75.385 290.625 -12 36.364 33.333 -15 -35.294 0 18.182 -23.077 0 -19 -12.346 -1.408 -28.571 28 -21.875 48 -45.946 25 -20 -22.5 22.581 -47.368 0 0 -25"
"WEDDSM ""FR"" ""PCH" "1M)*1.000"" ""12-31-71"" ""06-30-11"" ""M"" 475 ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" 59.459 -8.475 1.852 0.727 -7.942 -21.569 15 8.696 1.2 -1.186 -4 16.25 -70.968 -13.58 -48.571 -66.667 -33.333 -15 -26.471 20 -41.667 100 -42.857 15 8.696 0 -4 -6.25 11.111 0 0 -30 -14.286 0 -33.333 0 0 -60 25 80 -19.444 -10.345 -23.077 0 -10 -13.333 -29.487 -63.636 200 58.333 -5.263 -11.111 -37.5 0 0 260 -72.222 100 -74 92.308 -18 58.537 -23.077 20 58.333 -47.368"
"453454 ""FR"" ""PCH" "1M)*1.000"" ""12-31-71"" ""06-30-11"" ""M"" 475 ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" ""N/A"" 116.667 -23.077 -50 0 0 -20 25 50 33.333 0 -80 100 50 0 -16.667 0 0 0 40 -57.143 33.333 37.5 -22.727 0 -5.882 -6.25 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 -87.2 0 0 66.667 20 -16.667 0 0 -37.5 0 0 0 0 0 0 0 60 5025.001 0 0.61 0 -97.576 0 0 2500 -65.385 -44.444 0 0 0 -0.8 0 0 -35.484 0 -25 0 0 -20 0 0 0 108.334 12 -17.857 0 -47.826 0 -16.667 0 50 233.334 102 -85.149 -60 250 0 -28.571 -33.333 -30 28.571 0 0 511.111 -63.636 0 -20 25 -25 -53.333 0 0 0 14.286 0 0 -25 0 0 50 0 0 -44.444 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 100 450 0 0 0 0 -87.273 0 0 0 -28.571 0 0 0 0 -60 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 -50 0 0 0 0 0 0 0 0 0 0"


Expected Output:


"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475|1971|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475|1972|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475|1973|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475|1974|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475|1975|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475|1976|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475|1977|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
...
...
...(upto the available last month end date in the input file)
"12121K"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475|1971|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"12121K"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475|1972|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"12121K"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475|1973|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"12121K"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475|1974|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"12121K"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475|1975|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"12121K"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475|1976|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
...
...
...
...(upto the available last month end date in the input file)

is it possible to transpose like above?

I trust you are getting this as a fixed width length / tab delimited file. Yes, you can read it using sequence file staeg and write it using sequencial file stage and there you give | as a delimiter.

DS User

No. This is not the fixed width length file.

the important one is "dynamic column".. When we receive the same file next month we will have one more column in the file. i.e 31/oct/2011.

This looks like a vertical pivot requirement to me.

I have frequently heard this word from you. I am not still clear with the vertical pivot job design. When i search here, i didn't get enough information about this.

Could you please explain it in detail? Because i have lot of tasks like above in my project. Once i understood clearly i can implement it without any doubt.

"DATES ""INSTRUMENT(MNEMONIC)"" ""DATATYPES"" ""12-31-71"" ""06-30-11"" ""M"" 475

""12-31-71"" -- Start date of the first return column on the excel

""06-30-11"" -- End date of the last return column on the excel

475 -- number of columns in between start and end date

Since the column names are dynamic, i think i won't use 'horizontal pivot' method too. am i correct?

What is

Excel wrote:"DATES ""INSTRUMENT(MNEMONIC)"" ""DATATYPES""

doing? Is this the entire first line or is it the first three columns in a longer first line?
Is

Excel wrote:"DATES ""INSTRUMENT(MNEMONIC)"" ""DATATYPES"" ""12-31-71"" ""06-30-11"" ""M"" 475

perhaps the entire first line, or only a part of it?

Please describe the metadata that you are using - perhaps post the record schema from the imported table definition.

Pivot is where you translate from data in columns to data in rows (horizontal pivot) or from data in rows to data in columns (vertical pivot). We'll get back to that when we know a bit more about your job design.

Incidentally, why are the character string fields delimited by doubled double quotes? This is not a conventional delimited file arrangement.

Hi Karthik,

(just realized that Ray was quicker to respond than I was...)

It is very difficult to reconstruct your original requirement and the business-rules you used from your original data and the result provided.

If I interpret your input correctly, you have got a first data-row containing 475 date-values (1) and then four more data rows (2-5) containing 475 numeric values each, all corresponding to the date-values from the first row.

Now you want to concatenate the values from the first 70 bytes from rows 2-4 with the year from row one and the twelve corresponding values for that year from rows 2-4.

Your result is misleading because you have got 12 concatenated values for 1971, but only one date in row 1.
It is simply impossible to interpret how your input- and result-files are structured in terms of field-delimiters and quote-delimiters.

Try to identify the data-structure first, then reduce complexity and think again. I am quite sure you will find a suitable solution yourself. If not, come back to forum with a technical question we can handle.

The table structure will be like below

Sec_No int,
Year int
Jan decimal(16,8),
Feb decimal(16,8),
Mar decimal(16,8),
Apr decimal(16,8),
May decimal(16,8),
Jun decimal(16,8),
Jul decimal(16,8),
Aug decimal(16,8),
Sep decimal(16,8),
Oct decimal(16,8),
Nov decimal(16,8),
Dec decimal(16,8),

If you see for 1971 the file has the data for 31/dec/71. Thats why you see some value in the last column. The remaining should be null or N/A.

karthi_gana wrote:If you see for 1971 the file has the data for 31/dec/71. Thats why you see some value in the last column. The remaining should be null or N/A.

The first value in the row starting with "712232 " is "N/A" and this should correspond to "12-31-71". Maybe I got something wrong, but as I said: your requirements are unclear.

I'm sorry..I have just edited the expected output. you can see the correct values now.

The file format is not in the correct way. But unfortunately this file is being sent by enternal vendor since 2008 every month. Currently the subroutine is used to transpose this one. But it is taking long time to complete its execution. Thats why we have decided to redesign the same in 8.1 (it is in 7.5 version now).

We will forgot about the first column and even we can replace the double quotes completely from the file. It doesn't matter for them. They just need the final output as expected.

any help?