3

I'm trying to create a function to create csv files from queries. After I run the query, I'm looping through it and appending each row's fields to a StringBuffer object. To that end, I'm putting the column names into an array:

<cfset indexes = #ListToArray(Arguments.header)# />

where the argument is currently a string like:

"col1, col2, col3...."

I've verified that both the query and the array are what they should be by dumping. The trouble comes when looping through the query results. Given:

<cfset indexes_length = #ArrayLen(indexes)# />
<cfloop query="query_for_csv">
        <cfloop index="i" from="1" to="#indexes_length#">
            <cfset attr = #indexes[i]# />
            <cfset sbOutput.Append(
                "#query_for_csv[attr][query_for_csv.CurrentRow]#") />
        </cfloop>           
    </cfloop> 

Only the first value of the first row is output before I get the error message:

[Table (rows 10 columns col1, col2, col3):
[col1: coldfusion.sql.QueryColumn@6f731eba]
[col2: coldfusion.sql.QueryColumn@6ee67e7f] 
[col3: coldfusion.sql.QueryColumn@5c6647cb] 
is not indexable by col2 

If I replace the variable #attr# with the literal "col2":

#query_for_csv['col2'][query_for_csv.CurrentRow]#

then the loop sails through with no problem, and spits out all the values indexed by 'col2'. Any ideas?

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
earachefl
  • 1,880
  • 7
  • 31
  • 55
  • 2
    If you search for `QueryToCsv` in a search engine you will find existing functions that do exactly what you want, only they are already written and very likely have been tested for bugs too. – Peter Boughton Nov 09 '11 at 18:21
  • 1
    CFLib.org is a great resource for Peter's comment. You can also use cfgloss.com to search ColdFusion documentation and CFlib.org content. – Aaron Greenlee Nov 09 '11 at 18:36
  • Yeah, I found that Ben Nadel's solution mentioned below, with a few modifications, works great. Still have to figure out how to allow the user to choose where to save the file to. Another question! – earachefl Nov 09 '11 at 20:42

2 Answers2

4

I would guess it's the spaces in your header list that is the problem, so probably this would work:

<cfset attr = trim(indexes[i]) />


However, since you're not using them, you probably don't need that and can just do this...

<cfloop query="QueryName">
    <cfloop index="CurCol" list=#QueryName.ColumnList# >
        <cfset sbOutput.Append(
            QueryName[CurCol][QueryName.CurrentRow]
        )/>
    </cfloop>
</cfloop>


p.s.
You'll note here that there's only one pair of hashes - there only needs to be one pair in your original code snippets too (in the to attribute) - the rest are unnecessary noise.

Peter Boughton
  • 110,170
  • 32
  • 120
  • 176
  • Well, I'll be damned - that did it. Your tip on using the ColumnList attribute saves me having to pass a header argument as well. Thank you! – earachefl Nov 09 '11 at 18:33
2

As has already been said before, try to avoid spaces before or after a list element.

In case you want to compare notes, check out the approach Ben Nadel chose to implement such a Query2CSV converter: http://www.bennadel.com/blog/1239-Updated-Converting-A-ColdFusion-Query-To-CSV-Using-QueryToCSV-.htm

mz_01
  • 495
  • 3
  • 13