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,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

[Dan_Bracuk]

If you use cfhttp to get your file, the name attribute produces a query object.  Then you can simply do queryname.fieldname[row number]

[fuzion8]

Thanks for the reply Dan

The problem is the HG for the current month can be on either the 30th row.. 31st row... i dont know the row number for the current month.. but I do know it will be the first time HG is in column 1

Thanks

[Dan_Bracuk]

ColdFusion has a daysinmonth function that might help.