Converting A ColdFusion Query To CSV Using QueryToCSV()

Posted May 19, 2008 at 9:04 AM by Ben Nadel

Tags: ColdFusion

Reader Comments

http://cfzen.instantspot.com/blog/2007/4/18/queryToCsv2-util-function-updated

;}

I thought it might be helpful to add the following to the end of your "little sample code" (assuming someone want's to make the .csv file available for download):

<cfheader name="content-disposition" value="attachment; filename=download.csv" />
<cfcontent type="text/plain" /><cfoutput>#strOutput#</cfoutput>

(Or, you could substitute "application/msexcel" for the content type and achieve the same thing.)

I can't believe you went about it this way.
I guess knowing about underlying java techniques makes you overthink things, wich just aren't needed in coldfusion.

I think just using cfcontent and cfoutput is enough.
the columnnames can be done with as much as:
#listChangeDelims(query.columnnames,';',',')#

I realize this sometimes too, that Im just overthinking.

Hi Ben,

I noticed that you store column values as a struct, as opposed to looping over the list. You stated that you do this for speed reasons. I found this not to be true.

I took your example code and re-wrote it to use lists instead of structs.

Running the example, there was a no difference between the lists and structs. However, I noticed a slight speed increase (2%) using the list method, when I increased the list size to 20.

--j

I have been working on a similar issue lately and I have found that the arrayAppend/ArrayToList is SIGNIFICANTLY faster than the string buffer. This is especially noticeable when working on particularly large datasets--100k records or so.

I have to agree with David. I also try to use Arrays whenever possible as I have found it faster than looping through lists or structures. I havent tested the whole array/list/struct looping in 8 and see the speed differences.

@Steve
Don't forget to include ";charset=utf-8" (CF's default output mode) to support non-ascii characters! Or else whatever charset you specified in <cfprocessingdirective> if you did so.

<cfcontent type="text/plain; charset=utf-8">

@All,

I am a bit shocked that looping over a list is faster than looping over a collection. I didn't actually test the speed, but maybe you guys are right. I will run some tests on this.

As far as the use of Arrays, Jim did demonstrate to me that using an Array for each row, then appending that to the StringBuffer was very performant.

@David,

Again, I would be shocked if using just arrays for such large records would be faster on such huge files.

I think I need to dig into this a bit more. Thanks for the great feedback.

@Jim & @Ben
I did some tests of my own to compare various loop types for performance.

In short: <cfloop array> is the fastest object loop I could find, with <cfloop list> being a close second. <cfloop collection> definitely didn't perform as well as these. I talk a bit about why I think this is for each type of loop over on my blog.

http://www.bandeblog.com/2008/05/relative-cfloop-performance-for-various-loop-structures/

@Eric,

Wow. I am a bit shocked at the results.

@Eric,
Good point on including utf-8 for encoding ... also thanks for sharing the loop performance info.

Hi guys, thanks for share experience, I would share the way I do CSV. Im using SQL query to create CSV line like:

SELECT (
'"' + Data1 + '",'
+ '"' + Data2 + '",'
+ '"' + Data3 + '",'
+ '"' + Data4 + '"' ) as Line
FROM ......

and then just put result into the file:

<cfset filecontent = ValueList(q_csv.line,"#CHR(13)#")>
<cffile action="write" file="#fName#" output="#filecontent#" addnewline="yes">

@Misha

This a great solution--assuming the data is coming from a DB table AND you know ahead of time which fields you'll be pulling.

In my case, I'm actually using a Text Driver DSN to read in a CSV with commas, then create a new version of the file that is double pipe delimited (||). Each file could have a different set of columns. . .

Devid. yes, but you always can convert all data into the string like:

convert(nvarchar(50),qty)

and put some NULL validation as well like:

convert(nvarchar(50),ISNULL(SUM(QtyOrdered),0))

ISNULL(data1,'')

@Misha

I should have added before that my process of replicating and then modifying a CSV file would probably be best handled OUTSIDE of CF altogether but I'm working on an existing process.

I'm not sure whether that last bit on converting the data and setting nulls would work as my goal is to avoid, as much as possible, changing the data that my user has placed in the original--but I will look into it. I'm also not sure that, in my case, this will relieve CF at all, since I'm assuming that the driver for reading the original file isn't relying on the SQL server in any way.

@Misha,

Very cool solution. I would have never thought of that.

@Misha
Don't forget you have to escape any columns which might contain double quotes or line feeds.

Eric. From that point regardless what method you use to create CSV you should care about.

I don't disagree =)

Based on the feedback I have gotten, I created an updated version of of the QueryToCSV() method here:

http://www.bennadel.com/index.cfm?dax=blog:1239.view

@Eric,

Thanks for catching the whole escaping quotes point. I totally forgot to do that in my original version of the method. This has been fixed in the updated version. Dynamite catch :)

Hello,

I was wondering if you had som more examples of on how to use your QuerytoCSV calling a stored procedure or som actual queries.

Thanks in advance. I like what you have created.

@SDaniel,

As this is a ColdFusion function, it cannot be called from a stored procedure. Also, there is no difference between a manually constructed query as I have and query returned from the database in terms of how the QueryToCSV() method would be called.

I think this function would be better if the fields argument was not required.

If not given the function uses all fields.

@Anthony,

You could set the fields arguments to be optional and then give it a default value of #arguments.query.columnList#. Of course, then you'd have to be sure to use named-arguments rather than ordered ones.

For several years now, I have been using a solution similar to Misha's for creating a CSV file from a dynamic list of fields (concatenating all the columns in a sql query).

Unfortunately, some of the list of fields have become very large (300+ columns) and I am getting a SQL server error "The query processor ran out of stack space during query optimization." In one case, eliminating a single concatenation allows the query to run.

I have searched but have not found any suggestions on how I might increase the stack space to eliminate the issue. Most of the solutions involve rewriting the query.

I am therefore going to have to implement a new solution, and hope that Ben's excellent approach will work.

Has anyone tried it with very large data sets (columns and rows)?

@Tom,

I have never tried it with anything even approaching 300 columns. Good luck!

@Ben

You could make an arg for the field delim, and set your preferred delim as the default. Then if someone wanted no delims they could pass delim="". Should be just as fast to do """ as #delim#, right?

@Grant,

My good man, there is already an argument for a field delimiter that defaults to comma - this can be overridden with the method is invoked. Unless you're referring to the line delimiter (new line, etc.).

@Ben,

Actually, I meant the column escape character (triple quotes). Thought there was a comment about qualifier being faster to "just do". But I may have misunderstood the comment.

Apologies about the wrong terminology. Loooong couple of days and hit enter too soon.

@Grant,

Ahh, I see what you're talking about. That's an interesting idea.

@Misha

I tried your:

SELECT (
'"' + Data1 + '",'
+ '"' + Data2 + '",'
+ '"' + Data3 + '",'
+ '"' + Data4 + '"' ) as Line
FROM ......

Unfortunately I'm using MYSQL, using a different concat operator.

@vlad

I'm using MySql 5.5 & this works fine:

SELECT (CONCAT(
tbltollfree.tfNumber, ",1,",
tbltollfree.tfRingTo)) as Line
FROM tbltollfree
ORDER BY tbltollfree.tfNumber

Question to Misha or all?
- How do I "Append" some header & footer text to this query?
- Header1 = ;NVFILE 2.0
- header2 = DN,W1,DN1
- Footer = ;NVFILE END

Example of output would be:

;NVFILE 2.0
DN,W1,DN1
8002081111,1,1234567890
8002251112,1,1234567891
8002211113,1,1234567892
8002311114,1,1234567893
;NVFILE END

Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment