CODEWITHSUNDEEP
csvcoldfusion
Paul
Paul

Reputation: 13

Convert returned CSV data to searchable / usable format in ColdFusion

I have CSV data returned from http://api.wunderground.com/weatherstation/WXDailyHistory.asp?ID=IHAMPSHI46&month=3&day=25&year=2011&format=1 and need to get it into a format where I can select one of the resulting rows based on the time in the first column.

Would it be better to convert to struct/array/xml and what would be the best way to achieve this?

Upvotes: 1

Views: 604

Answers (4)

KobbyPemson
KobbyPemson

Reputation: 2539

Using an H2 database (www.h2database.com) you can use the ever handy csvread function. http://cfstuff.blogspot.com/2009/06/using-h2-database-in-coldfusion.html 

<cfset u="http://api.wunderground.com/weatherstation/WXDailyHistory.asp?ID=IHAMPSHI46&month=3&day=27&year=2011&format=1"/>

<cfhttp url="#u#" result="csv" />

<cffile 
    action="write" 
    file="#ExpandPath('./w3.csv')#" 
    output="#rereplace(csv.fileContent, "<br>|<!--.*-->", "", "all")#" />


<cfquery name="w2" datasource="h3"> 
    select * from csvread('#ExpandPath('./w3.csv')#')

</cfquery>
<cfdump var=#w2# expand="no"/>

<cfset yesterday = dateadd("d",-1,now())/>
<cfquery name="w2" datasource="h3"> 

    SELECT Time, TemperatureF, WindSpeedMPH FROM csvread('#ExpandPath('./w3.csv')#')
    WHERE  Time BETWEEN '#dateFormat(yesterday, "YYYY-MM-DD")# #timeFormat(dateAdd("h", -1, yesterday), "HH:MM")#:00' AND '#dateFormat(yesterday, "YYYY-MM-DD")# #timeFormat(dateAdd("h", 1, yesterday), "HH:MM")#:00' 
</cfquery>


<cfdump var=#w2#/>

Upvotes: 0

Todd Sharp
Todd Sharp

Reputation: 3355

OK, I dumped the result and it looks like the CSV file is using <br> tags to denote a newline so you'll have to roll your own conversion. Here's an example that uses a UDF from cflib:

<cfscript>
/**
* Transform a CSV formatted string with header column into a query object.
* 
* @param cvsString      CVS Data. (Required)
* @param rowDelim      Row delimiter. Defaults to CHR(10). (Optional)
* @param colDelim      Column delimiter. Defaults to a comma. (Optional)
* @return Returns a query. 
* @author Tony Brandner (&#116;&#111;&#110;&#121;&#64;&#98;&#114;&#97;&#110;&#100;&#110;&#101;&#114;&#115;&#46;&#99;&#111;&#109;)
* @version 1, September 30, 2005 
*/
function csvToQuery(csvString){
    var rowDelim = chr(10);
    var colDelim = ",";
    var numCols = 1;
    var newQuery = QueryNew("");
    var arrayCol = ArrayNew(1);
    var i = 1;
    var j = 1;

    csvString = trim(csvString);

    if(arrayLen(arguments) GE 2) rowDelim = arguments[2];
    if(arrayLen(arguments) GE 3) colDelim = arguments[3];

    arrayCol = listToArray(listFirst(csvString,rowDelim),colDelim);

    for(i=1; i le arrayLen(arrayCol); i=i+1) queryAddColumn(newQuery, arrayCol[i], ArrayNew(1));

    for(i=2; i le listLen(csvString,rowDelim); i=i+1) {
        queryAddRow(newQuery);
        for(j=1; j le arrayLen(arrayCol); j=j+1) {
            if(listLen(listGetAt(csvString,i,rowDelim),colDelim) ge j) {
                querySetCell(newQuery, arrayCol[j],listGetAt(listGetAt(csvString,i,rowDelim),j,colDelim), i-1);
            }
        }
    }
    return newQuery;
}
</cfscript>

<cfset u = "http://api.wunderground.com/weatherstation/WXDailyHistory.asp?ID=IHAMPSHI46&month=3&day=25&year=2011&format=1" />

<cfhttp url="#u#" result="csv" />
<!--- if you dump the result you can see that the result contains <br> tags to indicate new lines --->
<!--- 
<cfdump var="#csv.fileContent#" />
--->
<cfdump var="#csvToQuery(replace(csv.fileContent, "<br>", "", "all"))#" />

Upvotes: 2

JS Mitrah
JS Mitrah

Reputation: 686

Your CSV result is using < br > instead of linebreak. Use this cflib function, http://www.cflib.org/udf/CSVToQuery

Upvotes: 0

Todd Sharp
Todd Sharp

Reputation: 3355

Check the docs for cfhttp. Use the name attribute and you'll get a query back from the http call. Then you can do a query of queries on the result.

Upvotes: 1

Related Questions