3 Replies Latest reply on Nov 30, 2012 9:01 AM by Dan Bracuk

    Query specific cell in csv file

    fuzion8

      Hello

       

      I'm downloading a csv file daily. I need to extract a value that is in row 30 and 11th column.

       

      I'f you open the csv in Excel it has headers for the columns and the specificis

       

      Right now im using

       

      <cfftp action = "getFile"
          server = "#remoteFileLocation#"
          port="21"
          username="anonymous"
          password=""
          connection = "MyConn"
          passive = "Yes"
          localFile="D:\Inetpub\localFile.csv"
          remoteFile="Future.csv"
          failifexists="no">

      Did it succeed? <cfoutput>#cfftp.succeeded#</cfoutput>

      <cfftp action="close" connection="MyConn">

       

       

      <h1>Populate cpr table - future.csv</h1>
      <cfhttp url="http://www.website.com/future.csv"
      method="GET"
      name="qStockItems"
      delimiter=","""
      firstrowasheaders="yes"
      columns="PRODUCT,CONTRACT_MONTH,CONTRACT_YEAR,CONTRACT_DAY,CONTRACT,PRODUCT_DESCRIPTION,OP EN,HIGH,HIGH_AB_INDICATOR,LOW,LOW_AB_INDICATOR,LAST,LAST_AB_INDICATOR,SETTLE,PT_CHG,EST_VO L,PRIOR_SETTLE,PRIOR_VOL,PRIOR_INT,TRADEDATE">
      </cfhttp>
       
      <cfloop query="qStockItems" startrow="30" endrow="30">
      <!---   <cfloop from="1" to="40" index="i">--->
          <cfquery datasource="wire" name="addrecord">
        UPDATE cpr
              SET PRODUCT = '#qStockItems.PRODUCT#',
                  CONTRACT_MONTH = #qStockItems.CONTRACT_MONTH#,
                  CONTRACT_YEAR =  #qStockItems.CONTRACT_YEAR#,
                  SETTLE = #qStockItems.SETTLE#,
                  TRADEDATE = '#qStockItems.TRADEDATE#'
      </cfquery>
      <!--- </cfloop>--->
      </cfloop>      
      <cfoutput>#qStockItems.tradedate#</cfoutput>

       

      I'd like to retrieve the latest contract for copper, which is the first HG in column 1.

       

      If anyone can please chime in on the best programing technique to get the cell that contains the first time HG is in column 1 and then the value of its 11th column.

       

      Thanks for your input!

       

      PRODUCT SYMBOLCONTRACT MONTHCONTRACT YEARCONTRACT DAYCONTRACTPRODUCT DESCRIPTIONOPENHIGHHIGH AB INDICATORLOWLOW AB INDICATORLASTLAST AB INDICATORSETTLEPT CHGEST. VOLPRIOR SETTLEPRIOR VOLPRIOR INTTRADEDATE
      GC122012GCZ12Gold Futures1719.617291717.71727.210.7652951716.52761363528911/29/2012
      GC12013GCF13Gold Futures1720.417291719.81728.310.66331717.72607143711/29/2012
      GC22013GCG13Gold Futures1722.41731.21720.11727.11729.510.71778041718.819620827266011/29/2012
      GC42013GCJ13Gold Futures17241732.11723.21731.610.820041720.860422953911/29/2012
      GC62013GCM13Gold Futures1726.517341725.21733.710.821671722.924632913211/29/2012
      GC82013GCQ13Gold Futures1732.81735.81731.81735.610.82841724.88911733311/29/2012
      GC102013GCV13Gold Futures1730.21736.51730.21737.610.991726.756950511/29/2012
      GC122013GCZ13Gold Futures1733.51740.117331739.710.913371728.816141948911/29/2012
      GC22014GCG14Gold Futures1733.81742.31733.81742.110.9231731.238250211/29/2012
      GC42014GCJ14Gold Futures1744.11744.31744.11744.510.9671733.639343811/29/2012
      GC62014GCM14Gold Futures174710.91391736.1101911911/29/2012
      GC82014GCQ14Gold Futures1749.510.901738.68411/29/2012
      GC102014GCV14Gold Futures17520011/29/2012
      GC122014GCZ14Gold Futures1748.21754.71748.21754.510.81621743.771527411/29/2012
      GC62015GCM15Gold Futures1762.810.7701752.118574711/29/2012
      GC122015GCZ15Gold Futures1770177017701771.310.47061760.910860611/29/2012
      GC62016GCM16Gold Futures1780.310.30177022711/29/2012
      GC122016GCZ16Gold Futures1789.710.201779.514222111/29/2012
      GC62017GCM17Gold Futures1803.28.91001794.344611/29/2012
      GC122017GCZ17Gold Futures1819.38.901810.46611/29/2012
      GC62018GCM18Gold Futures1840.78.901831.81511/29/2012
      GCT22013GCTG13Gold Tas Futures00.1-0.10UNCH490011/29/2012
      GVF22013GVFG13Gold Volatility Index (vix) Futures14-0.43014.4311/29/2012
      GVF42013GVFJ13Gold Volatility Index (vix) Futures15.53-0.37015.911/29/2012
      GVF62013GVFM13Gold Volatility Index (vix) Futures16.57-0.33016.911/29/2012
      GVF82013GVFQ13Gold Volatility Index (vix) Futures17.72-0.29018.0111/29/2012
      GVF102013GVFV13Gold Volatility Index (vix) Futures18.25-0.42018.6711/29/2012
      GVF122013GVFZ13Gold Volatility Index (vix) Futures19.29-0.2019.4911/29/2012
      HG122012HGZ12Copper Futures3.52853.6063.5243.58850.064155353.5245450231239911/29/2012
      HG12013HGF13Copper Futures3.53553.61253.53553.5980.066510013.5315449233811/29/2012
      HG22013HGG13Copper Futures3.5553.6173.5553.60150.0673823.5345284125311/29/2012
      HG32013HGH13Copper Futures3.54253.6223.53753.623.60550.068631453.5375331169357611/29/2012
      HG42013HGJ13Copper Futures3.56653.62653.56653.61050.068263.54252864511/29/2012
      HG52013HGK13Copper Futures3.54653.62953.54653.61350.06818083.545510691282311/29/2012
      HG62013HGM13Copper Futures3.63.63.63.61750.068113.54951649511/29/2012
      HG72013HGN13Copper Futures3.55853.63653.55853.62050.0688323.5525173599011/29/2012
      HG82013HGQ13Copper Futures3.6230.067543.5555546711/29/2012
      HG92013HGU13Copper Futures3.613.6373.613.6250.06754453.557543332411/29/2012
      HG102013HGV13Copper Futures3.6270.067503.5595549811/29/2012
      HG112013HGX13Copper Futures3.6290.067503.56151347411/29/2012
      HG122013HGZ13Copper Futures3.64053.64053.633.633.63050.0675843.56340424411/29/2012
      HG12014HGF14Copper Futures3.63250.067503.56527911/29/2012
      HG22014HGG14Copper Futures3.6340.067503.56653211/29/2012
      HG32014HGH14Copper Futures3.6350.067513.567539111/29/2012
      HG42014HGJ14Copper Futures3.63550.067503.56811/29/2012
      HG52014HGK14Copper Futures3.6360.067503.568511/29/2012
      HG62014HGM14Copper Futures3.63450.067503.56711/29/2012