Skip to main content
Participating Frequently
September 13, 2008
Answered

Need help with QueryToCSV, QueryAddRow, and QuerySetCell

  • September 13, 2008
  • 3 replies
  • 804 views
Thanks for taking the time to read my post.

I need to convert a query to a .csv file so users of my family Web site can download it and import it into Outlook or Thunderbird instead of typing in each family member's info.

I've visited many CF blogs that have post about QueryToCSV and QuerySetCell, and found this on Ray Camden's site CFLib.org at http://www.cflib.org/index.cfm?event=page.udfbyid&udfid=556

The code from the above link:
--------------------------------------------------------------------------------------
<cfset test_query = QueryNew("ValueField, DisplayField")>
<cfset QueryAddRow(test_query, 3)>
<cfset QuerySetCell(test_query,"ValueField","blue", 1)>
<cfset QuerySetCell(test_query,"DisplayField","my favorite color is blue", 1)>
<cfset QuerySetCell(test_query,"ValueField","changed the text for the heck of it", 2)>
<cfset QuerySetCell(test_query,"DisplayField","blah blah blah", 2)>
<cfset QuerySetCell(test_query,"ValueField","Louisiana", 3)>
<cfset QuerySetCell(test_query,"DisplayField","The State of Louisiana", 3)>
<cfoutput>
<pre>
#querytoCSV(test_query)#
</pre>
</cfoutput>
--------------------------------------------------------------------------------------

However, I'm stuck because I cannot figure out how to dynamically set the number of columns in the QueryAddRow function and also in the QuerySetCell function. This is needed because the number of registered family members will obviously change with time.

I'm assuming qryMember.RecordCount would work:
(i.e., <cfset QueryAddRow(qryMembers, qryMembers.RecordCount)>) for the QueryAddRow function, but I'm still confused about the QuerySetCell part.

Any suggestions?

Thanks!
This topic has been closed for replies.
Correct answer -__cfSearching__-
> The way I understand what you said is that I do not need any of the QueryAddRow
> or QuerySetCell functions.

Correct

> Just run the <cfscript> part

Add the <cfscript> part to your page, yes.

> and then <cfset csvText = querytoCSV( theNameOfYourQueryHere )> Correct?

Yes. You can then display the results using cfoutput.
<cfoutput>#csvText#</cfoutput>

Once that is working, you can add in <cfheader> and <cfcontent> tags so the results can be downloaded.

3 replies

doug123Author
Participating Frequently
September 13, 2008
Got it. I tried it and it works like a charm.

Thanks again for your help...it's nice to get such quick replies.
doug123Author
Participating Frequently
September 13, 2008
Thanks for answering.

I found a different one (which is very similar) on the same site at:
http://www.cflib.org/udf/QueryToCSV2

However, I'm still confused by what you said and obviously, I'm not very good at programming.

Anyway, you said "If you already have a query, you do not need to use that code. Just pass your query to the function."

The way I understand what you said is that I do not need any of the QueryAddRow or QuerySetCell functions. Just run the <cfscript> part and then <cfset csvText = querytoCSV( theNameOfYourQueryHere )>

Correct?

-__cfSearching__-Correct answer
Inspiring
September 13, 2008
> The way I understand what you said is that I do not need any of the QueryAddRow
> or QuerySetCell functions.

Correct

> Just run the <cfscript> part

Add the <cfscript> part to your page, yes.

> and then <cfset csvText = querytoCSV( theNameOfYourQueryHere )> Correct?

Yes. You can then display the results using cfoutput.
<cfoutput>#csvText#</cfoutput>

Once that is working, you can add in <cfheader> and <cfcontent> tags so the results can be downloaded.
Inspiring
September 13, 2008
doug123 wrote:
> I need to convert a query to a .csv file

If you already have a query, you do not need to use that code. Just pass your query to the function.

<cfset csvText = querytoCSV( theNameOfYourQueryHere )>

The sample code (QueryNew,QueryAddRow,QuerySetCell...) is just for demonstration only. So you could test the function, even without a database.