Query specific cell in csv file
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,OPEN,HIGH,HIGH_AB_INDICATOR,LOW,LOW_AB_INDICATOR,LAST,LAST_AB_INDICATOR,SETTLE,PT_CHG,EST_VOL,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 SYMBOL | CONTRACT MONTH | CONTRACT YEAR | CONTRACT DAY | CONTRACT | PRODUCT DESCRIPTION | OPEN | HIGH | HIGH AB INDICATOR | LOW | LOW AB INDICATOR | LAST | LAST AB INDICATOR | SETTLE | PT CHG | EST. VOL | PRIOR SETTLE | PRIOR VOL | PRIOR INT | TRADEDATE |
| GC | 12 | 2012 | GCZ12 | Gold Futures | 1719.6 | 1729 | 1717.7 | 1727.2 | 10.7 | 65295 | 1716.5 | 276136 | 35289 | 11/29/2012 | |||||
| GC | 1 | 2013 | GCF13 | Gold Futures | 1720.4 | 1729 | 1719.8 | 1728.3 | 10.6 | 633 | 1717.7 | 2607 | 1437 | 11/29/2012 | |||||
| GC | 2 | 2013 | GCG13 | Gold Futures | 1722.4 | 1731.2 | 1720.1 | 1727.1 | 1729.5 | 10.7 | 177804 | 1718.8 | 196208 | 272660 | 11/29/2012 | ||||
| GC | 4 | 2013 | GCJ13 | Gold Futures | 1724 | 1732.1 | 1723.2 | 1731.6 | 10.8 | 2004 | 1720.8 | 6042 | 29539 | 11/29/2012 | |||||
| GC | 6 | 2013 | GCM13 | Gold Futures | 1726.5 | 1734 | 1725.2 | 1733.7 | 10.8 | 2167 | 1722.9 | 2463 | 29132 | 11/29/2012 | |||||
| GC | 8 | 2013 | GCQ13 | Gold Futures | 1732.8 | 1735.8 | 1731.8 | 1735.6 | 10.8 | 284 | 1724.8 | 891 | 17333 | 11/29/2012 | |||||
| GC | 10 | 2013 | GCV13 | Gold Futures | 1730.2 | 1736.5 | 1730.2 | 1737.6 | 10.9 | 9 | 1726.7 | 56 | 9505 | 11/29/2012 | |||||
| GC | 12 | 2013 | GCZ13 | Gold Futures | 1733.5 | 1740.1 | 1733 | 1739.7 | 10.9 | 1337 | 1728.8 | 1614 | 19489 | 11/29/2012 | |||||
| GC | 2 | 2014 | GCG14 | Gold Futures | 1733.8 | 1742.3 | 1733.8 | 1742.1 | 10.9 | 23 | 1731.2 | 38 | 2502 | 11/29/2012 | |||||
| GC | 4 | 2014 | GCJ14 | Gold Futures | 1744.1 | 1744.3 | 1744.1 | 1744.5 | 10.9 | 67 | 1733.6 | 39 | 3438 | 11/29/2012 | |||||
| GC | 6 | 2014 | GCM14 | Gold Futures | 1747 | 10.9 | 139 | 1736.1 | 101 | 9119 | 11/29/2012 | ||||||||
| GC | 8 | 2014 | GCQ14 | Gold Futures | 1749.5 | 10.9 | 0 | 1738.6 | 84 | 11/29/2012 | |||||||||
| GC | 10 | 2014 | GCV14 | Gold Futures | 1752 | 0 | 0 | 11/29/2012 | |||||||||||
| GC | 12 | 2014 | GCZ14 | Gold Futures | 1748.2 | 1754.7 | 1748.2 | 1754.5 | 10.8 | 162 | 1743.7 | 71 | 5274 | 11/29/2012 | |||||
| GC | 6 | 2015 | GCM15 | Gold Futures | 1762.8 | 10.7 | 70 | 1752.1 | 18 | 5747 | 11/29/2012 | ||||||||
| GC | 12 | 2015 | GCZ15 | Gold Futures | 1770 | 1770 | 1770 | 1771.3 | 10.4 | 706 | 1760.9 | 10 | 8606 | 11/29/2012 | |||||
| GC | 6 | 2016 | GCM16 | Gold Futures | 1780.3 | 10.3 | 0 | 1770 | 227 | 11/29/2012 | |||||||||
| GC | 12 | 2016 | GCZ16 | Gold Futures | 1789.7 | 10.2 | 0 | 1779.5 | 14 | 2221 | 11/29/2012 | ||||||||
| GC | 6 | 2017 | GCM17 | Gold Futures | 1803.2 | 8.9 | 100 | 1794.3 | 446 | 11/29/2012 | |||||||||
| GC | 12 | 2017 | GCZ17 | Gold Futures | 1819.3 | 8.9 | 0 | 1810.4 | 66 | 11/29/2012 | |||||||||
| GC | 6 | 2018 | GCM18 | Gold Futures | 1840.7 | 8.9 | 0 | 1831.8 | 15 | 11/29/2012 | |||||||||
| GCT | 2 | 2013 | GCTG13 | Gold Tas Futures | 0 | 0.1 | -0.1 | 0 | UNCH | 490 | 0 | 11/29/2012 | |||||||
| GVF | 2 | 2013 | GVFG13 | Gold Volatility Index (vix) Futures | 14 | -0.43 | 0 | 14.43 | 11/29/2012 | ||||||||||
| GVF | 4 | 2013 | GVFJ13 | Gold Volatility Index (vix) Futures | 15.53 | -0.37 | 0 | 15.9 | 11/29/2012 | ||||||||||
| GVF | 6 | 2013 | GVFM13 | Gold Volatility Index (vix) Futures | 16.57 | -0.33 | 0 | 16.9 | 11/29/2012 | ||||||||||
| GVF | 8 | 2013 | GVFQ13 | Gold Volatility Index (vix) Futures | 17.72 | -0.29 | 0 | 18.01 | 11/29/2012 | ||||||||||
| GVF | 10 | 2013 | GVFV13 | Gold Volatility Index (vix) Futures | 18.25 | -0.42 | 0 | 18.67 | 11/29/2012 | ||||||||||
| GVF | 12 | 2013 | GVFZ13 | Gold Volatility Index (vix) Futures | 19.29 | -0.2 | 0 | 19.49 | 11/29/2012 | ||||||||||
| HG | 12 | 2012 | HGZ12 | Copper Futures | 3.5285 | 3.606 | 3.524 | 3.5885 | 0.064 | 15535 | 3.5245 | 45023 | 12399 | 11/29/2012 | |||||
| HG | 1 | 2013 | HGF13 | Copper Futures | 3.5355 | 3.6125 | 3.5355 | 3.598 | 0.0665 | 1001 | 3.5315 | 449 | 2338 | 11/29/2012 | |||||
| HG | 2 | 2013 | HGG13 | Copper Futures | 3.555 | 3.617 | 3.555 | 3.6015 | 0.067 | 382 | 3.5345 | 284 | 1253 | 11/29/2012 | |||||
| HG | 3 | 2013 | HGH13 | Copper Futures | 3.5425 | 3.622 | 3.5375 | 3.62 | 3.6055 | 0.068 | 63145 | 3.5375 | 33116 | 93576 | 11/29/2012 | ||||
| HG | 4 | 2013 | HGJ13 | Copper Futures | 3.5665 | 3.6265 | 3.5665 | 3.6105 | 0.068 | 26 | 3.5425 | 28 | 645 | 11/29/2012 | |||||
| HG | 5 | 2013 | HGK13 | Copper Futures | 3.5465 | 3.6295 | 3.5465 | 3.6135 | 0.068 | 1808 | 3.5455 | 1069 | 12823 | 11/29/2012 | |||||
| HG | 6 | 2013 | HGM13 | Copper Futures | 3.6 | 3.6 | 3.6 | 3.6175 | 0.068 | 11 | 3.5495 | 16 | 495 | 11/29/2012 | |||||
| HG | 7 | 2013 | HGN13 | Copper Futures | 3.5585 | 3.6365 | 3.5585 | 3.6205 | 0.068 | 832 | 3.5525 | 173 | 5990 | 11/29/2012 | |||||
| HG | 8 | 2013 | HGQ13 | Copper Futures | 3.623 | 0.0675 | 4 | 3.5555 | 5 | 467 | 11/29/2012 | ||||||||
| HG | 9 | 2013 | HGU13 | Copper Futures | 3.61 | 3.637 | 3.61 | 3.625 | 0.0675 | 445 | 3.5575 | 43 | 3324 | 11/29/2012 | |||||
| HG | 10 | 2013 | HGV13 | Copper Futures | 3.627 | 0.0675 | 0 | 3.5595 | 5 | 498 | 11/29/2012 | ||||||||
| HG | 11 | 2013 | HGX13 | Copper Futures | 3.629 | 0.0675 | 0 | 3.5615 | 13 | 474 | 11/29/2012 | ||||||||
| HG | 12 | 2013 | HGZ13 | Copper Futures | 3.6405 | 3.6405 | 3.63 | 3.63 | 3.6305 | 0.0675 | 84 | 3.563 | 40 | 4244 | 11/29/2012 | ||||
| HG | 1 | 2014 | HGF14 | Copper Futures | 3.6325 | 0.0675 | 0 | 3.565 | 279 | 11/29/2012 | |||||||||
| HG | 2 | 2014 | HGG14 | Copper Futures | 3.634 | 0.0675 | 0 | 3.5665 | 32 | 11/29/2012 | |||||||||
| HG | 3 | 2014 | HGH14 | Copper Futures | 3.635 | 0.0675 | 1 | 3.5675 | 3 | 91 | 11/29/2012 | ||||||||
| HG | 4 | 2014 | HGJ14 | Copper Futures | 3.6355 | 0.0675 | 0 | 3.568 | 11/29/2012 | ||||||||||
| HG | 5 | 2014 | HGK14 | Copper Futures | 3.636 | 0.0675 | 0 | 3.5685 | 11/29/2012 | ||||||||||
| HG | 6 | 2014 | HGM14 | Copper Futures | 3.6345 | 0.0675 | 0 | 3.567 | 11/29/2012 | ||||||||||
