coldfusion create csv file dymanic pass heading and columns

I am using Coldfusion 8, and need to create a csv file, output from a query.  
If is fix columns, the following code works.  
My issue is the output query general not fix columns.  
Example can be sometimes 6 columns, sometimes can be 10+- columns.  
Need help on make the following code to loop the heading and data...  
Thank you,



<cfif isdefined("TQuery.recordcount") and #TQuery.recordcount# gt 0>
      <CFSET TXTOUTPUT = "">
      <CFSET OUTFILE = "C:\TTemp_#dateformat(now(),'yyyymmdd')#.csv">
            
      <CFSET TXTCOLUMNS = '"T1","T2","T3","T4","T5","T6"'>
      <CFIF #FILEEXISTS(OUTFILE)# IS "No">
            <CFFILE ACTION="WRITE" FILE="#OUTFILE#" OUTPUT="#TXTCOLUMNS#">
      </CFIF>
      <CFLOOP QUERY="TQuery">
            <CFSET TXTOUTPUT = '"#T1#","#T2#","#T3#","#T4#","#T5#","#T6#"'>
            <CFFILE ACTION="APPEND" FILE="#OUTFILE#" OUTPUT="#TXTOUTPUT#">      
      </CFLOOP>
      <CFSET filename = "TList_#dateformat(now(),'yyyymmdd')#.csv">
      <CFHEADER  NAME="content-disposition" VALUE="attachment; filename=#filename#">
      <CFCONTENT  TYPE="APPLICATION/MSEXCEL" FILE="#OUTFILE#" DELETEFILE="TRUE" RESET="TRUE">

</cfif>
jfreeman2010Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:
Just one set of quotes..

Change:
<CFSET TXTCOLUMNS = '"T1","T2","T3","T4","T5","T6"'>

to :
<CFSET TXTCOLUMNS = '"T1,T2,T3,T4,T5,T6"'>
0
_agx_Commented:
Edit: Fix code typo

... and to generate the content dynamically as well, loop through TXTCOLUMNS inside the query loop:

<CFLOOP QUERY="TQuery">
       <CFSET TXTOUTPUT= "">
       <CFLOOP list="#TXTCOLUMNS#" index="colName">
            <CFSET TXTOUTPUT= listAppend(TXTOUTPUT, """"& TQuery[ colName ][currentRow] & """") >
       </CFLOOP>
       <!--- append non empty lines only --->
       <CFIF LEN(dataRow)>
            <CFFILE ACTION="APPEND" FILE="#OUTFILE#" OUTPUT="#TXTOUTPUT#">      
        </CFIF>
    </CFLOOP>

TList_#dateformat(now(),'yyyymmdd')#.csv
Aside, you might want to make the file name more unique. Otherwise, the file may be overwritten if multiple threads execute it at the same time.
0
jfreeman2010Author Commented:
Thank you both gdemaria and agx for the reply.

I also need to loop thru the query to get the heading.  This: <CFSET TXTCOLUMNS = '"T1,T2,T3,T4,T5,T6"'> need in a cfloop.

Thank you for help.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

jfreeman2010Author Commented:
or _agx_'s code include looping heading already?  thanks
0
jfreeman2010Author Commented:
Hi _agx_,

I got error: 'DATAROW' is undefined.  (  <CFIF LEN(dataRow)> ).

Should it be  <CFIF LEN(TXTOUTPUT)>?

Thanks
0
_agx_Commented:
Yep.  Sorry, my bad.
0
jfreeman2010Author Commented:
I am still have issue to create the output file.  
It have pop up window ask to save to open the file,
when I click on open the file, error said file not found,
'C:/temp/TQuery_20151104.csv not found'.  Thanks

Here is the currect code:

<cfif isdefined("TQuery.recordcount") and #TQuery.recordcount# gt 0>

      <CFSET OUTFILE = "C:\temp\TQuery_#dateformat(now(),'yyyymmdd')#.csv">
 
      <CFLOOP QUERY="TQuery">
            <CFSET TXTOUTPUT = "">
        <cfloop list="#TXTOUTPUT#" index="colName">
              <cfset TXTOUTPUT=listAppend(TXTOUTPUT, """"& TQuery[ colName ][currentRow] & """") >
        </cfloop>
       
        <CFIF #FILEEXISTS(OUTFILE)# IS "No" AND LEN(TXTOUTPUT)>
            <CFFILE ACTION="WRITE" FILE="#OUTFILE#" OUTPUT="#TXTOUTPUT#">
         <cfelseif LEN(TXTOUTPUT)>  
            <CFFILE ACTION="APPEND" FILE="#OUTFILE#" OUTPUT="#TXTOUTPUT#">  
        </cfif>    
      </CFLOOP>
   
      <CFSET filename = "C:\temp\TQuery_#dateformat(now(),'yyyymmdd')#.csv">
      <CFHEADER  NAME="content-disposition" VALUE="attachment; filename=#filename#">
      <CFCONTENT  TYPE="APPLICATION/MSEXCEL" FILE="#OUTFILE#" DELETEFILE="TRUE" RESET="TRUE">

</cfif>
0
gdemariaCommented:
Here's my proposed version - differences include:
1.  Create empty file early so it always exists and you don't have to test if file exists every row
2.  I don't know if this CFLOOP LIST value makes sense...<cfloop list="#TXTOUTPUT#" index="colName">
     You are looping over values of TXTOUTPUT and then appending values to it.   I changed this to loop over query columns
3.  I removed this line   <CFSET filename = "C:\temp\TQuery_#dateformat(now(),'yyyymmdd')#.csv">
     It is redundant as you have already defined the file name above.   I change the variable name so all values use the same variable
4.  I changed the format of this istAppend(TXTOUTPUT, """"& TQuery[ colName ][currentRow] & """")  
     to use fewer quotes


<cfif TQuery.recordcount>
      <CFSET OUTFILE = "C:\temp\TQuery_#dateformat(now(),'yyyymmdd')#.csv">
      <CFFILE ACTION="WRITE" FILE="#OUTFILE#" OUTPUT=""> <!--- create empty file so it will always exist even if empty --->
 
      <CFLOOP QUERY="TQuery">
         <CFSET TXTOUTPUT = "">
         <cfloop list="#TQuery.columnList#" index="colName">
            <cfset TXTOUTPUT = listAppend(TXTOUTPUT, '"#TQuery[ colName ][currentRow]#"') >
         </cfloop>
         <cfif len(textOutput)>
            <CFFILE ACTION="APPEND" FILE="#OUTFILE#" OUTPUT="#TXTOUTPUT#">  
		 </cfif>
      </CFLOOP>
   
      <CFHEADER  NAME="content-disposition" VALUE="attachment; OUTFILE=#OUTFILE#">
      <CFCONTENT  TYPE="APPLICATION/MSEXCEL" FILE="#OUTFILE#" DELETEFILE="TRUE" RESET="TRUE">
</cfif>

Open in new window

0
jfreeman2010Author Commented:
Hi gdemaria,

Code above close to work...
I was able to see the pop up ask to save or open the file.  The file name is not TQuery....csv, is my cfm page name.
0
_agx_Commented:
>> VALUE="attachment; OUTFILE=#OUTFILE#">

Change it to:

....   VALUE='attachment; filename="theFileNameYouWantHere.CSV" '>

Note, the quotes around the actual file name and the fact that it's the file name only, not a full path.
0
gdemariaCommented:
Thanks agx, that was my global find/replace of "filename"  that messed that up...

The file name is not TQuery....csv, is my cfm page name.
Not really sure what this means, this is the output file:
<CFSET OUTFILE = "C:\temp\TQuery_#dateformat(now(),'yyyymmdd')#.csv">
and was the same as the FiLENAME further below that the code tried to open.
0
_agx_Commented:
My "read" was the download window popped up, but the browser displayed:

     Download file;    someCFScriptFileNameHere.CFM

... rather than:

    Download:   TQuery_20151104.CSV

but that'll probably be solved by fixing the Content-Disposition value.
0
jfreeman2010Author Commented:
Thank you both for help.  I am getting a output file and able to open in excel.  2 issues:

1) no heading in the file
2) first line no read data - empty line

Thank you
0
gdemariaCommented:
Change this line to write your header instead of nothing..

 <CFFILE ACTION="WRITE" FILE="#OUTFILE#" OUTPUT=""> <!--- create empty file so it will always exist even if empty --->
0
jfreeman2010Author Commented:
How do I loop the heading from TQuery?
0
_agx_Commented:
Use column list. Adapted from gd's earlier example:

        <CFSET TXTOUTPUT = "">
         <cfloop list="#TQuery.columnList#" index="colName">
            <cfset TXTOUTPUT = listAppend(TXTOUTPUT, '"#colName#"') >
         </cfloop>
         <CFFILE ACTION="WRITE" FILE="#OUTFILE#" OUTPUT="#TXTOUTPUT#">
0
jfreeman2010Author Commented:
It works, file with heading now.  

The query had a address field with',' when open in excel, it will expend to 2 or 3 columns.  so can this code modify to handle it or I should remove all ',' in the query?

 <cfloop list="#TQuery.columnList#" index="colName">
              <cfset TXTOUTPUT=listAppend(TXTOUTPUT,  '"#TQuery[ colName ][currentRow]#"') >
 </cfloop>

Thank you
0
_agx_Commented:
Worked fine for me w/Excel 2013.  Are you sure the generated file has those fields enclosed in quotes? Because per the CSV specs, the value shouldn't be split if the field is wrapped in double quotes.
0
jfreeman2010Author Commented:
Sorry, I am working on a production issue so did not come back sooner.

When I take a more closed look, its not the comma issue, is all the columns display in the order of heading by alphabetical order, not the way query selection order...
0
gdemariaCommented:
Query.columnList is an alphabetical list of the column names of the query.    Usually a CSV file is for importing and order isn't important.   If it's important for your application, you can create a manual list instead of using columnList.

<cfset allcolumns = "Name,email,phone,address1,city,state,zip">

That way the order will always match this sequence.

If you want the user to be able to select the order, then you have to  save that order somewhere and use it to build the list.   Relying on the order of the select statements will not work, unless agx knows a way?
0
jfreeman2010Author Commented:
Since the query result columns are not fix, I mean, example like can be 10 up to 100 or more, I think I can't use this:

<cfset allcolumns = "Name,email,phone,address1,city,state,zip">
0
gdemariaCommented:
So the question is, where do the columns come from?   Whatever the source is, you need to use that source to create the ordered list.
0
jfreeman2010Author Commented:
The source did created a ordered list, but on the conversion to csv file, it show  alphabetical list, not the source ordered list.
0
_agx_Commented:
Yeah, that's because the new code uses query.columnList.   Unfortunately it's always alphabetical.

If you want to preserve the original order used in the SQL, either A) use the list of column names from the "source" (not sure where that comes from)  OR B) use getMetaData(query) with an array loop, not a list loop.

Untested, but something like this

<!--- get metadata from query object to gt column names in the SQL order --->
<cfset metaData = getMetaData(TQuery)>

<!--- then use an ARRAY loop to get the column header names --->
<CFSET TXTOUTPUT = "">
<cfloop array="#metaData#" index="colData">
            <cfset TXTOUTPUT = listAppend(TXTOUTPUT, '"#colData.name#"') >
</cfloop>

Use the array when generating the query data rows as well

....
<CFSET TXTOUTPUT = "">
<cfloop array="#metaData#" index="colData">
       <cfset TXTOUTPUT = listAppend(TXTOUTPUT, '"#TQuery[ colData.name ][currentRow]#"') >
</cfloop>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jfreeman2010Author Commented:
Thanks _agx_, I will try to and report back... Thanks!!
0
jfreeman2010Author Commented:
I am very close...

I got the correct heading and data, but got same bed data add to the file too.  1 run should be 48, instead, got 67.  the bad data added between the correct data.

Here are the most current code:

<cfif isdefined("TQuery.recordcount") and #TQuery.recordcount# gt 0>

    <CFSET OUTFILE = "C:\temp\TQuery_#dateformat(now(),'yyyymmdd')#.csv">
    <cfset metaData = getMetaData(TQuery)>
   
    <CFSET TXTOUTPUT = "">
   
    <cfloop array="#metaData#" index="colData">
              <cfset TXTOUTPUT = listAppend(TXTOUTPUT, '"#colData.name#"') >
    </cfloop>
    <CFFILE ACTION="WRITE" FILE="#OUTFILE#" OUTPUT="#TXTOUTPUT#">
 
   
    <CFLOOP QUERY="TQuery">
      <CFSET TXTOUTPUT = "">
        <cfloop array="#metaData#" index="colData">
              <cfset TXTOUTPUT=listAppend(TXTOUTPUT,  '"#TQuery[ colData.name ][currentRow]#"') >
        </cfloop>
       
       
         <cfif LEN(TXTOUTPUT)>  
            <CFFILE ACTION="APPEND" FILE="#OUTFILE#" OUTPUT="#TXTOUTPUT#">  
        </cfif>    
     </CFLOOP>
   
      <CFSET filename = "TQuery_#dateformat(now(),'yyyymmdd')#.csv">
      <CFHEADER  NAME="content-disposition" VALUE="attachment; filename=#filename#">
      <CFCONTENT  TYPE="APPLICATION/MSEXCEL" FILE="#OUTFILE#" DELETEFILE="TRUE" RESET="TRUE">

</cfif>
0
jfreeman2010Author Commented:
I reboot computer and created a smaller excel file.  It looks correct with all heading and data.

with csv file, I had to save the file first, then open the file, not direct open the file.

anyway it works.

Thank you very much for all your help.
0
jfreeman2010Author Commented:
Thank you
0
_agx_Commented:
Not sure if it applies, but one thing I wanted to mention ... be sure you're NOT passing a list of columns from the client side (form or url) and using it directly in the SQL query like this:

<!--- don't do this --->
<cfquery ...> SELECT #form.listOfColumnNames# FROM someTable</cfquery>

At least not without validating the column names. Otherwise, you're exposing the db to sql injection.
0
jfreeman2010Author Commented:
Thank you, _agx_.  The source of the query is a stored procedure.
0
_agx_Commented:
It's not using dynamic sql though right? ie Accepting a list of column names as a parameter and executing them in a dynamic sql string (unsafe)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.