1 Reply Latest reply: May 15, 2014 9:56 AM by Grant Perkins RSS

    Newbie needs help with a comma delimited file

    thorny00 _

      I have a comma delimited file that I need to strip off only certain data.  Below is a portion of the file. I need to do the following:

      1. replace the 'Pass-Through' with the above 'Product Name'

      2. strip off the 10, 30, 45, 60 Day line

      3. grab the next 20 lines only.

      4. This will be repeated 20 times.

       

      "Lender ID:","19869-000-7"

      "Lender Name:","Testing"

       

       

      "Product Name:","30-Year Fixed Rate (01 - 30-Year Fixed Rate)"

      "Remittance Type:","Actual/Actual"

      "","10 Day","30 Day","45 Day","60 Day"

      "Par Yield:","5.32","5.36","5.39","5.42"

      ""

      "Pass-Through","10 Day","30 Day","45 Day","60 Day"

      "7.250","N/A","N/A","N/A","N/A"

      "7.125","N/A","N/A","N/A","N/A"

      "7.000","N/A","N/A","N/A","N/A"

      "6.875","N/A","N/A","N/A","N/A"

      "6.750","N/A","N/A","N/A","N/A"

      "6.625","N/A","N/A","N/A","N/A"

      "6.500","N/A","N/A","N/A","N/A"

      "6.375","N/A","N/A","N/A","N/A"

      "6.250","N/A","N/A","103.2036","103.1063"

      "6.125","103.0887","102.9342","102.8124","102.7152"

      "6.000","102.6631","102.4925","102.3708","102.2736"

      "5.875","102.2276","102.0571","101.9275","101.8304"

      "5.750","101.7985","101.6281","101.4905","101.3854"

      "5.625","101.3351","101.1569","101.0194","100.9064"

      "5.500","100.7892","100.6113","100.4742","100.3694"

      "5.375","100.2419","100.0644","99.9356","99.8231"

      "5.250","99.6933","99.5243","99.3878","99.2835"

      "5.125","99.1435","98.9749","98.8387","98.7347"

      "5.000","98.5845","98.4083","98.2805","98.1688"

      "4.875","98.0163","97.8486","97.7131","97.6097"

      "4.750","97.4552","97.2879","97.1528","97.0417"

      "4.625","96.7500","96.5835","96.4490","96.3384"

      "4.500","95.7217","95.5488","95.4155","95.3058"

      "4.375","94.6900","94.5187","94.3866","94.2780"

      "4.250","93.6554","93.4857","93.3547","93.2471"

       

       

      "Product Name:","20-Year Fixed Rate (02 - 20-Year Fixed Rate)"

      "Remittance Type:","Actual/Actual"

      "","10 Day","30 Day","45 Day","60 Day"

      "Par Yield:","5.18","5.23","5.26","5.29"

      ""

      "Pass-Through","10 Day","30 Day","45 Day","60 Day"

      "7.250","N/A","N/A","N/A","N/A"

      "7.125","N/A","N/A","N/A","N/A"

      "7.000","N/A","N/A","N/A","N/A"

      "6.875","N/A","N/A","N/A","N/A"

      "6.750","N/A","N/A","N/A","N/A"

      "6.625","N/A","N/A","N/A","N/A"

      "6.500","N/A","N/A","N/A","N/A"

      "6.375","N/A","N/A","N/A","N/A"

      "6.250","N/A","N/A","N/A","N/A"

      "6.125","N/A","N/A","102.9667","102.8733"

      "6.000","102.8501","102.6898","102.5631","102.4632"

      "5.875","102.4585","102.2918","102.1653","102.0589"

      "5.750","102.0661","101.8930","101.7600","101.6538"

      "5.625","101.6663","101.4868","101.3474","101.2348"

      "5.500","101.1926","101.0202","100.8812","100.7754"

      "5.375","100.7251","100.5531","100.4145","100.3090"

      "5.250","100.2572","100.0856","99.9539","99.8487"

      "5.125","99.7889","99.6177","99.4864","99.3814"

      "5.000","99.3201","99.1494","99.0184","98.9137"

      "4.875","98.8510","98.6808","98.5501","98.4392"

      "4.750","98.3816","98.2118","98.0814","97.9708"

      "4.625","97.8141","97.6450","97.5086","97.3985"

      "4.500","96.7814","96.6140","96.4790","96.3700"

      "4.375","95.7493","95.5772","95.4500","95.3357"

      "4.250","94.7180","94.5477","94.4155","94.3087"

       

       

      "Product Name:","15-Year Fixed Rate (03 - 15-Year Fixed Rate)"

      "Remittance Type:","Actual/Actual"

      "","10 Day","30 Day","45 Day","60 Day"

      "Par Yield:","4.78","4.82","4.85","4.87"

      ""

      "Pass-Through","10 Day","30 Day","45 Day","60 Day"

      "6.750","N/A","N/A","N/A","N/A"

      "6.625","N/A","N/A","N/A","N/A"

      "6.500","N/A","N/A","N/A","N/A"

      "6.375","N/A","N/A","N/A","N/A"

      "6.250","N/A","N/A","N/A","N/A"

      "6.125","N/A","N/A","N/A","N/A"

      "6.000","N/A","N/A","N/A","N/A"

      "5.875","N/A","N/A","N/A","N/A"

      "5.750","N/A","N/A","103.1637","103.0708"

      "5.625","103.0023","102.8711","102.7673","102.6746"

      "5.500","102.5725","102.4416","102.3381","102.2456"

      "5.375","102.1427","102.0067","101.9035","101.8113"

      "5.250","101.7129","101.5773","101.4690","101.3771"

      "5.125","101.2778","101.1372","101.0292","100.9375"

      "5.000","100.8050","100.6703","100.5627","100.4767"

      "4.875","100.3380","100.2037","100.1018","100.0107"

      "4.750","99.8713","99.7374","99.6359","99.5504"

      "4.625","99.3943","99.2609","99.1597","99.0798"

      "4.500","98.8858","98.7529","98.6574","98.5726"

      "4.375","98.3779","98.2509","98.1505","98.0660"

      "4.250","97.8708","97.7443","97.6495","97.5601"

      "4.125","97.2489","97.1231","97.0288","96.9399"

      "4.000","96.1709","96.0413","95.9481","95.8603"

      "3.875","95.0923","94.9642","94.8721","94.7801"

      "3.750","94.0133","93.8867","93.7957","93.7048"

       

       

      "Product Name:","10-Year Fixed Rate (04 - 10-Year Fixed Rate)"

      "Remittance Type:","Actual/Actual"

      "","10 Day","30 Day","45 Day","60 Day"

      "Par Yield:","4.56","4.60","4.64","4.68"

      ""

      "Pass-Through","10 Day","30 Day","45 Day","60 Day"

      "6.250","N/A","N/A","N/A","N/A"

      "6.125","N/A","N/A","N/A","N/A"

      "6.000","N/A","N/A","N/A","N/A"

      "5.875","N/A","N/A","N/A","N/A"

      "5.750","N/A","N/A","N/A","N/A"

      "5.625","N/A","N/A","102.9357","102.8399"

      "5.500","102.7456","102.6238","102.5282","102.4327"

      "5.375","102.3379","102.2164","102.1168","102.0260"

      "5.250","101.9306","101.8053","101.7060","101.6155"

      "5.125","101.5368","101.4076","101.3086","101.2141"

      "5.000","101.1994","101.0704","100.9674","100.8774"

      "4.875","100.8620","100.7334","100.6306","100.5365"

      "4.750","100.5291","100.3965","100.2897","100.1958"

      "4.625","100.1920","100.0597","99.9489","99.8553"

      "4.500","99.8465","99.7188","99.6083","99.5191"

      "4.375","99.5054","99.3738","99.2678","99.1831"

      "4.250","99.1603","99.0332","98.9275","98.8473"

      "4.125","98.7393","98.6127","98.5116","98.4316"

      "4.000","97.8491","97.7238","97.6237","97.5403"

      "3.875","96.9577","96.8336","96.7386","96.6520"

      "3.750","96.0693","95.9465","95.8525","95.7626"

      "3.625","95.1557","95.0382","94.9451","94.8522"

      "3.500","94.1256","94.0055","93.9136","93.8218"

      "3.375","93.0941","92.9756","92.8809","92.7902"

      "3.250","92.0618","91.9447","91.8512","91.7617"

        • Newbie needs help with a comma delimited file
          Grant Perkins

          So do you want to keep the result as a csv file in the much the same overall format but with the changes applied?

           

          Which version of Monarch and Standard or Pro?

           

          My initial thought would be to eliminate the csv format by, for eample,  reading it into Monarch and then exporting as a fixed format report. Then model the report layout to give what you want with the possibility of exporting once again as a csv file.

           

          I'm keen to understand the purpose so we don't miss any especially good solutions.

           

           

          Grant

           

           

          Originally posted by thorny00:

          I have a comma delimited file that I need to strip off only certain data.  Below is a portion of the file. I need to do the following:

          1. replace the 'Pass-Through' with the above 'Product Name'

          2. strip off the 10, 30, 45, 60 Day line

          3. grab the next 20 lines only.

          4. This will be repeated 20 times.

           

          "Lender ID:","19869-000-7"

          "Lender Name:","Testing"

           

           

          "Product Name:","30-Year Fixed Rate (01 - 30-Year Fixed Rate)"

          "Remittance Type:","Actual/Actual"

          "","10 Day","30 Day","45 Day","60 Day"

          "Par Yield:","5.32","5.36","5.39","5.42"

          ""

          "Pass-Through","10 Day","30 Day","45 Day","60 Day"

          "7.250","N/A","N/A","N/A","N/A"

          "7.125","N/A","N/A","N/A","N/A"

          "7.000","N/A","N/A","N/A","N/A"

          "6.875","N/A","N/A","N/A","N/A"

          "6.750","N/A","N/A","N/A","N/A"

          "6.625","N/A","N/A","N/A","N/A"

          "6.500","N/A","N/A","N/A","N/A"

          "6.375","N/A","N/A","N/A","N/A"

          "6.250","N/A","N/A","103.2036","103.1063"

          "6.125","103.0887","102.9342","102.8124","102.7152"

          "6.000","102.6631","102.4925","102.3708","102.2736"

          "5.875","102.2276","102.0571","101.9275","101.8304"

          "5.750","101.7985","101.6281","101.4905","101.3854"

          "5.625","101.3351","101.1569","101.0194","100.9064"

          "5.500","100.7892","100.6113","100.4742","100.3694"

          "5.375","100.2419","100.0644","99.9356","99.8231"

          "5.250","99.6933","99.5243","99.3878","99.2835"

          "5.125","99.1435","98.9749","98.8387","98.7347"

          "5.000","98.5845","98.4083","98.2805","98.1688"

          "4.875","98.0163","97.8486","97.7131","97.6097"

          "4.750","97.4552","97.2879","97.1528","97.0417"

          "4.625","96.7500","96.5835","96.4490","96.3384"

          "4.500","95.7217","95.5488","95.4155","95.3058"

          "4.375","94.6900","94.5187","94.3866","94.2780"

          "4.250","93.6554","93.4857","93.3547","93.2471"

           

           

          /b[/quote]