working with CSV

Question

I'm running the following query and QoQ . Could you tell me how should I proceed for the "Download CSV" file option?

<!--- 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>[/CODE]

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

Thanks

So you want to generate CSV data from a recordset? Is all the above bumpf basically a very long-winded way of asking that? — Adam Cameron, Jul 22 '13 at 19:05
Yes, I want to generate CSV when a user click on Download button. — Tan, Jul 22 '13 at 19:15
No no no. "creating CSV data from a recordset" is a Stack Overflow question; "how do I finish writing my code for this specific piece of work" is soliciting free consultancy. You need to do your own work. And to do your own work, you need to break the task at hand down into general chunks. First chunk: you need to know how to create CSV data from a recordset. Not even "this specific record set", but the general solution for "recordset to CSV". I suggest you start by googling "cfml query to csv". — Adam Cameron, Jul 22 '13 at 19:31
I wrote a cfscript if you look at my code properly and asking questions on that. Would you like me to do more research? — Tan, Jul 22 '13 at 19:36
Yuo shoudl start by getting rid of all the crap that's got nothing to do with a) the recordset that has the data you want to convert to CSV; b) the code that creates CSV data. Your question doesn't need forms and charts and HTML and all that stuff. Just start with a record set. And work out how to make CSV data out of it (I see no code above that does that... you might need to look up what CSV data looks like, too). Like I said, break the overall requirement into chunks, and resolve each chuck. Whilst learning how to do each chunk, don't worry about the over-all big picture. — Adam Cameron, Jul 22 '13 at 20:06
@AdamCameron - Agreed on most of that. However, he did include his attempt at generating a csv (albeit after the extraneous chart/form code). Tan - You did not articulate the question clearly, which is "how to export **multiple** queries to a *single* CSV file". Nor did you explain the problem with your current code. While we can make some educated guesses, we should not have to run the code just to understand the problem. (cont'd) — Leigh, Jul 22 '13 at 21:48
That said, I think you are making it more complex than it needs to be. Start [by generating a *single* resultset](http://stackoverflow.com/a/17796580/104223), not multiples, then take it step by step as suggested. — Leigh, Jul 22 '13 at 21:49
@Leigh there's some code that generates a string. It's not CSV format though. Hence my observation "you might need to look up what CSV data looks like, too". — Adam Cameron, Jul 23 '13 at 10:47
@AdamCameron - True enough. That is why I said "attempt" ;) But agreed about tackling this in smaller chunks. — Leigh, Jul 23 '13 at 15:02

score 0 · Accepted Answer · answered Jul 22 '13 at 20:14

0

Using separate queries makes this a LOT more difficult than necessary. For it to work, you essentially need to pivot or transpose the rows of each query into separate columns. Not an easy task, unless all of your queries are guaranteed to contain the exact same dates, in the same order (unlikely).

If you have a fixed number of events, it is much simpler to use a single database query to generate all of counts. Use a CASE statements to build the counts based on the "Events" value:

(Note: This cannot be done inside a QoQ. They do not support CASE).

 SELECT STR_TO_DATE(date_format(Timedetail,'%m-%d-%Y'),'%m-%d-%Y') as TheDate
        , SUM( CASE WHEN EVENTS = 'First' THEN 1 ELSE 0 END ) AS FirstConn
        , SUM( CASE WHEN EVENTS = 'Second' THEN 1 ELSE 0 END ) AS SecondConn
        , SUM( CASE WHEN EVENTS = 'Third' THEN 1 ELSE 0 END ) AS ThirdConn
 FROM  YourTableName
 WHERE Timedetail  >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_date"> 
 AND   Timedetail  <  <cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_date">
 GROUP BY STR_TO_DATE(date_format(Timedetail,'%m-%d-%Y'),'%m-%d-%Y')

Once you have all the results in a single query, you can do the rest on your own. As Adam mentioned, just do a search on ColdFusion query to csv. There are tons of examples you can follow, as well as a number of pre-built functions for converting queries to CSV:

answered Jul 22 '13 at 20:14

Leigh

28,765
10
55
103

Thanks for the clarification. Just one question, does Query to CSV means that the data would be outputted in an Excel sheet? Or do I need to look at cfspreadsheet option? Just wanted to makes sure I'm heading in right direction. – Tan Jul 22 '13 at 22:15
1

No, CSV means ... [CSV format](http://en.wikipedia.org/wiki/Comma-separated_values#Example). The program assigned to open .csv files is up to the user. Typically Excel, but it could also be a different program. – Leigh Jul 22 '13 at 22:36
Thanks for clarification. Please take a look at my updated code. – Tan Jul 23 '13 at 16:31
Please ignore my previous comment and code. I have figured out other way to work around with this problem and now getting the CSV generated code in my output. Thanks – Tan Jul 23 '13 at 16:40
@Tan - Yikes! Try not to erase questions and replace them with a completely new one .. because now the answer above makes absolutely no sense! :) Instead make small updates/edits. If there is a *drastic* change, it is best to open a separate question. But do not forget to close out the old one. FYI: I have rolled back the original question to preserve the context of this thread. – Leigh Jul 23 '13 at 16:52
Okay. Since the original question was answered, please close out this thread. If the final code involves more than the query above, feel free to post it as a separate answer (just the parts relating to generating your CSV file) – Leigh Jul 23 '13 at 17:44
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/34017/discussion-between-tan-and-leigh) – Tan Jul 23 '13 at 21:07

working with CSV

1 Answers1