#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    58
    Rep Power
    3

    How to approach for Downloading a CSV File?


    I'm running the following query and QoQ . Could you tell me how should I proceed for the "Download CSV" file option?
    Code:
        <!--- QoQ for FIRSTCONN --->
        
        <cfquery datasource = "XX.XX.X.XX" name="master1">
             SELECT STR_TO_DATE(date_format(Timedetail,'%m-%d-%Y'),'%m-%d-%Y') as FIRSTCONN
                    , COUNT(Timedetail) as FIRSTOccurances
                    , EVENTS 
             FROM  MyDatabase
             WHERE EVENTS = "FIRST" 
             GROUP BY FIRSTCONN ;
        </cfquery> 
    
        <!--- Detail Query --->
        
        <cfquery dbtype="query" name="detail1">
            SELECT  *
            FROM master1 
            WHERE FIRSTCONN  >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_varchar"> 
            AND   FIRSTCONN  <  <cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_varchar">;
        </cfquery>  
        
        
        <!--- QoQ for SECONDCONN --->
        
        <cfquery datasource = "XX.XX.X.XX" name="master2">
            SELECT STR_TO_DATE(date_format(Timedetail,'%m-%d-%Y'),'%m-%d-%Y') as SECONDCONN
                   , COUNT(Timedetail) as SECONDOccurances
                   , EVENTS 
            FROM  MyDatabase
            WHERE EVENTS = "SECOND" 
            GROUP BY SECONDCONN ;
        </cfquery> 
    
        
        <cfquery dbtype="query" name="detail2">
            SELECT  *
            FROM   master2 
            WHERE  SECONDCONN  >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_varchar"> 
            AND    SECONDCONN  <  <cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_varchar">;
        </cfquery>  
        
        
        <cfchart format="flash"  chartwidth="1000" chartheight="500" scalefrom="0" scaleto="50000" xAxisTitle="Dates" yaxistitle="Number of Connections">
             <cfchartseries  query="detail1" type="line" itemColumn="FIRSTCONN" valueColumn="FIRSTOccurances" > 
             <cfchartseries  query="detail2" type="line" itemColumn="SECONDCONN" valueColumn="SECONDOccurances" > 
             </cfchartseries>
        </cfchart>
    The cfform code and cfscript code I'm using is as follows:

    Code:
    <cfform format="flash" preloader ="false">
    
    
    <cfformgroup type="horizontal">
    
      <cfinput type="dateField" name="startdate" label="Start Date" width="100" value="#form.startdate#">
      <cfinput type="dateField" name="enddate" label="End Date" width="100" value="#form.enddate#">
      <cfinput name="submitApply" type="submit" value = "Apply">
      <cfinput name="cancel" type="submit" value="Download CSV">
    
    </cfformgroup>
    
    
    <cfscript>
        var tl ='';
        var nl = (Chr( 13 ) & Chr( 10 ));
        var fileContent = createObject("java","java.lang.StringBuffer").init();
        var counter =1;
        fileContent.append( 'FIRST');
         fileContent.append(nl);
                for(i=1;i<=detail1.recordCount;i=i+1){
                    tl = detail1.FIRST;
                    fileContent.append(tl);
                    fileContent.append(nl);
                }
    			
    			
    			
    			
      fileContent.append( 'SECOND');
         fileContent.append(nl);
                for(i=1;i<=detail2.recordCount;i=i+1){
                    tl = detail2.SECOND;
                    fileContent.append(tl);
                    fileContent.append(nl);
                }			
    
    </cfscript>
    
    <cfset absoluteFilePathAndName = " C:\ColdFusion8\runtime\servers\coldfusion\SERVER-INF\temp\wwwroot-tmp\">
    
    <cfset realtiveFilePathAndName = " C:\ColdFusion8\runtime\servers\coldfusion\SERVER-INF\temp\wwwroot-tmp\">
    
    <cffile action="write" file="#absoluteFilePathAndName#" output="#fileContent.toString()#"/>
    <a href="#realtiveFilePathAndName#>Download</a>
    Desired Output:

    I have attached the [IMG]image[/IMG] for the output below. Please find it attached.

    Basically, if a date range is 21June to 21 July. The output must be as shown in the image. (I have omitted THIRDCONN etc for the sake of simplicity in my code).

    I tried to attempt to the above problem,Do I need to write fileContent.append() for each and every column? Please let me know if I'm wrong.

    P.S. I'm new to CF and haven't done this before.

    The link for Image is as follows:
    https://www.dropbox.com/s/yfuh4expyodg6t1/Stats_Screenshot.jpg

    Thanks
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    Loop over the query and generate the CSV text, and then output it to the page using cfcontent and the appropriate MIME type (e.g. "text/csv").

    Perhaps one of these user-defined functions will help.

    As an aside, I spent a while answering your questions in the other threads and you just abandoned them without explaining the resolution. This is bad forum etiquette. Please go back and make a note about the solution so that others can use the information in the future.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    58
    Rep Power
    3
    Originally Posted by kiteless
    Loop over the query and generate the CSV text, and then output it to the page using cfcontent and the appropriate MIME type (e.g. "text/csv").

    Perhaps one of these user-defined functions will help.

    As an aside, I spent a while answering your questions in the other threads and you just abandoned them without explaining the resolution. This is bad forum etiquette. Please go back and make a note about the solution so that others can use the information in the future.
    Thanks for information.

    FYI:I haven't abandoned any thread at all. Please don't assume anything by yourself. I remember you asked me to mention the cfdump of SECONDCONN in one of my thread and since I don't have access to my workstation till Monday, I'm waiting.

    Anyway, thanks again for constantly answering my questions.
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    Sure, happy to help when I can.

    On the abandoned thread topic, the first thread you started is over a week old at this point. So it sure looked like you had just moved on since you've asked other questions since then.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    58
    Rep Power
    3
    Originally Posted by kiteless
    Sure, happy to help when I can.

    On the abandoned thread topic, the first thread you started is over a week old at this point. So it sure looked like you had just moved on since you've asked other questions since then.
    Sorry about the old thread. I'll check that again soon.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    58
    Rep Power
    3
    Originally Posted by Jack_Tauson_Sr
    Sorry about the old thread. I'll check that again soon.
    I'm getting a CSV output in my web browser as follows:

    Code:
    "SECONDCONN";"THIRDCONN";"FIFTHCONN";"FOURTHCONN";"OPENCONN";"SIXTHCONN";"SEVENTHCONN";"THEDATE"; "4285";"0";"87822";"3319";"38926";"0";"30";"{ts '2013-06-28 00:00:00'}"; "2843";"0";"83444";"3255";"34910";"0";"47";"{ts '2013-06-29 00:00:00'}"; "875";"0";"38403";"2890";"25682";"0";"36";"{ts '2013-06-30 00:00:00'}"; "4033";"0";"100172";"3469";"48400";"0";"35";"{ts '2013-07-01 00:00:00'}"; "3211";"0";"89518";"3296";"42847";"0";"46";"{ts '2013-07-02 00:00:00'}"; "2882";"0";"46468";"429";"30014";"0";"30";"{ts '2013-07-03 00:00:00'}"; "978";"0";"46074";"3339";"21047";"0";"30";"{ts '2013-07-04 00:00:00'}"; "1727";"0";"73243";"2904";"29982";"0";"32";"{ts '2013-07-05 00:00:00'}"; "811";"0";"62644";"3067";"25056";"0";"26";"{ts '2013-07-06 00:00:00'}"; "522";"0";"617";"24";"13733";"0";"23";"{ts '2013-07-07 00:00:00'}"; "2556";"0";"72609";"1706";"35753";"0";"43";"{ts '2013-07-08 00:00:00'}"; "1160";"0";"43604";"556";"20966";"0";"31";"{ts '2013-07-09 00:00:00'}"; "8580";"0";"124329";"1752";"41713";"0";"39";"{ts '2013-07-10 00:00:00'}"; "2630";"0";"63482";"991";"30976";"0";"42";"{ts '2013-07-11 00:00:00'}"; "12";"0";"287";"15";"1153";"0";"1";"{ts '2013-07-16 00:00:00'}"; "1";"0";"22";"2";"110";"0";"0";"{ts '2013-07-22 00:00:00'}";

    Now, I want to display it in an excel file when a user click on download button. Could you tell me how to proceed in this case?
  12. #7
  13. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    You basically use CFHEADER and CFCONTENT to tell the browser what file type you want the output to be, and then output the CSV data.

    See the section named create an Excel spreadsheet with CFCONTENT.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    58
    Rep Power
    3
    Originally Posted by kiteless
    You basically use CFHEADER and CFCONTENT to tell the browser what file type you want the output to be, and then output the CSV data.

    See the section named create an Excel spreadsheet with CFCONTENT.
    Thanks. Here is the code I'm using which is generating the file but with some problems which I have mentioned after the code:

    Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Cfchart</title>
    </head>
    
    <body>
    
    
    <cfset detail1 = queryNew("")>
    <cfset queryAddColumn(detail1, "FIRSTCONN", "date", listToArray("2013-07-31,2013-08-15,2013-08-17"))>
    <cfset queryAddColumn(detail1, "FIRSTOccurances", listToArray("3,5,6"))>
    
    <cfset detail2 = queryNew("")>
    <cfset queryAddColumn(detail2, "SECONDCONN", "date", listToArray("2013-08-10,2013-08-18,2013-08-20"))>
    <cfset queryAddColumn(detail2, "SECONDOccurances", listToArray("4,10,8"))>
    <cfchart format="flash"  xAxisTitle="Dates" yaxistitle="Number of Connections" showlegend="yes">
           <cfchartseries  query="detail1" type="line" itemColumn="FIRSTCONN" valueColumn="FIRSTOccurances" /> 
           <cfchartseries  query="detail2" type="line" itemColumn="SECONDCONN" valueColumn="SECONDOccurances" /> 
    </cfchart>
    
    
    
    
    <cfscript>
    
    function QueryToCsv(query){
    	var csv = "";
    	var cols = "";
    	var headers = "";
    	var i = 1;
    	var j = 1;
    	
    	if(arrayLen(arguments) gte 2) headers = arguments[2];
    	if(arrayLen(arguments) gte 3) cols = arguments[3];
    	
    	if(cols is "") cols = query.columnList;
    	if(headers IS "") headers = cols;
    	
    	headers = listToArray(headers);
    	
    	for(i=1; i lte arrayLen(headers); i=i+1){
    		csv = csv & """" & headers[i] & """;";
    	}
    
    	csv = csv & chr(13) & chr(10);
    	
    	cols = listToArray(cols);
    	
    	for(i=1; i lte query.recordCount; i=i+1){
    		for(j=1; j lte arrayLen(cols); j=j+1){
    			csv = csv & """" & query[cols[j]][i] & """;";
    		}		
    		csv = csv & chr(13) & chr(10);
    	}
    	return csv;
    }
    </cfscript>
    
    <cfset csvText = querytoCSV( detail1 )>
    
    
    <cfoutput>#csvText#</cfoutput> 
    
    <!--- EXCEL OUTPUT CODE --->
    
    
    <cfscript>
    
    function Query2Excel(query) {
    	var InputColumnList = query.columnList;
    	var Headers = query.columnList;
    
    	var AlternateColor = "FFFFFF";
    	var header = "";
    	var headerLen = 0;
    	var col = "";
    	var colValue = "";
    	var colLen = 0;
    	var i = 1;
    	var j = 1;
    	var k = 1;
    	
    	var HTMLData = "";
    	
    	if (arrayLen(arguments) gte 2) {
    		Headers = arguments[2];
    	}
    	if (arrayLen(arguments) gte 3) {
    		InputColumnList = arguments[3];
    	}
    
    	if (arrayLen(arguments) gte 4) {
    		AlternateColor = arguments[4];
    	}
    	if (listLen(InputColumnList) neq listLen(Headers)) {
    		return "Input Column list and Header list are not of equal length";
    	}
    	
    	HTMLData = HTMLData & "<table border=1><tr bgcolor=""C0C0C0"">";
    	for (i=1;i lte ListLen(Headers);i=i+1){
    		header=listGetAt(Headers,i);
    		headerLen=Len(header)*10;
    		HTMLData = HTMLData & "<th width=""#headerLen#""><b>#header#</b></th>";
    	}
    	HTMLData = HTMLData & "</tr>";
    	for (j=1;j lte query.recordcount;j=j+1){
    		if (j mod 2) {
    			HTMLData = HTMLData & "<tr bgcolor=""FFFFFF"">";
    		} else {
    			HTMLData = HTMLData & "<tr bgcolor=""#alternatecolor#"">";
    		}
    		for (k=1;k lte ListLen(InputColumnList);k=k+1) {
    			col=ListGetAt(InputColumnList,k);
    			colValue=query[trim(col)][j];
    			colLength=Len(colValue)*10;
    			if (NOT Len(colValue)) {
    				colValue="&nbsp;";
    			} 
    			if (isNumeric(colValue) and Len(colValue) gt 10) {
    				colValue="'#colValue#";
    			} 
    			HTMLData = HTMLData & "<td width=""#colLength#"">#colValue#</td>";
    		}
    	HTMLData = HTMLData & "</tr>";
    	}
    	HTMLData = HTMLData & "</table>";
    	
    	return HTMLData;
    }
    </cfscript>
    
    <CFHEADER NAME="Content-Disposition" VALUE="inline; filename=stats.xls">
    <cfcontent type="application/msexcel"><cfoutput>#Query2Excel(detail1)#</cfoutput> 
    <!--- </cfloop> --->
    
    
    
    
    
    
    
    
    </body>
    
    
    </html>

    1) The file you are trying to open, 'stats.xls' is in a different format than specified by the file extension.
    Verify that the file is not corrupted and is from a trusted source before opening the file.
    Do you wanna open the file now?

    I'm using Firefox browser.

    Please let me know how to overcome this problem.

    Thanks
  16. #9
  17. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    I believe this is a security feature built into Excel. See http://support.microsoft.com/kb/948615.

    So I'm not sure you can do anything about it with the file extension and MIME type set to Excel, since obviously the file you're sending isn't actually an .xls file.

    You can try setting it to the .csv extension and use "text/csv" as the MIME type, but that probably won't automatically open Excel, unless the user has set things up to automatically open csv files with Excel.

    Your other option is to actually generate a true binary Excel file. CF 9 supports this via cfspreadsheet and the Excel-related functions. Earlier versions of CF don't have this feature, due to their age (6+ years old). You'd probably have to try and use the Apache POI library in that case, which will be a lot more complicated.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    58
    Rep Power
    3
    Originally Posted by kiteless
    I believe this is a security feature built into Excel. See http://support.microsoft.com/kb/948615.

    So I'm not sure you can do anything about it with the file extension and MIME type set to Excel, since obviously the file you're sending isn't actually an .xls file.

    You can try setting it to the .csv extension and use "text/csv" as the MIME type, but that probably won't automatically open Excel, unless the user has set things up to automatically open csv files with Excel.

    Your other option is to actually generate a true binary Excel file. CF 9 supports this via cfspreadsheet and the Excel-related functions. Earlier versions of CF don't have this feature, due to their age (6+ years old). You'd probably have to try and use the Apache POI library in that case, which will be a lot more complicated.
    Hmm, may be I'll have to switch to CF 9 but for the time being I can't and hence would have to bear the error I'm facing with respect to the file opening.
  20. #11
  21. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    Again, to be clear, it's not en error. Excel itself deliberately shows this warning as a security precaution.

IMN logo majestic logo threadwatch logo seochat tools logo