Converting A ColdFusion Query To CSV Using QueryToCSV()
Posted May 19, 2008 at 9:04 AM
UPDATE: I have posted an updated, faster more accurate version of this UDF here.
The other day, someone asked me about converting a ColdFusion query to a "comma separated values" file, or CSV. I was about to point them to my QueryToCSV() user defined function when I realized something crazy - I didn't have one. I have done a lot of work with converting CSV values into queries, but I guess I just never did as much going the other way as the task is significantly less complicated. However, in an effort to make future explanations easier, I have created one below:
Launch code in new window » Download code as text file »
- <cffunction
- name="QueryToCSV"
- access="public"
- returntype="string"
- output="false"
- hint="I take a query and convert it to a comma separated value string.">
-
- <!--- Define arguments. --->
- <cfargument
- name="Query"
- type="query"
- required="true"
- hint="I am the query being converted to CSV."
- />
-
- <cfargument
- name="Fields"
- type="string"
- required="true"
- hint="I am the list of query fields to be used when creating the CSV value."
- />
-
- <cfargument
- name="CreateHeaderRow"
- type="boolean"
- required="false"
- default="true"
- hint="I flag whether or not to create a row of header values."
- />
-
- <cfargument
- name="Delimiter"
- type="string"
- required="false"
- default=","
- hint="I am the field delimiter in the CSV value."
- />
-
- <!--- Define the local scope. --->
- <cfset var LOCAL = {} />
-
- <!---
- First, we want to set up a column index so that we can
- iterate over the column names faster than if we used a
- standard list loop on the passed-in list.
- --->
- <cfset LOCAL.ColumnNames = {} />
-
- <!---
- Loop over column names and index them numerically. We
- are going to be treating this struct almost as if it
- were an array. The reason we are doing this is that
- look-up times on a table are a bit faster than look
- up times on an array (or so I have been told).
- --->
- <cfloop
- index="LOCAL.ColumnName"
- list="#ARGUMENTS.Fields#"
- delimiters=",">
-
- <!--- Store the current column name. --->
- <cfset LOCAL.ColumnNames[ StructCount( LOCAL.ColumnNames ) + 1 ] = Trim( LOCAL.ColumnName ) />
-
- </cfloop>
-
- <!--- Store the column count. --->
- <cfset LOCAL.ColumnCount = StructCount( LOCAL.ColumnNames ) />
-
-
- <!---
- Now that we have our index in place, let's create
- a string buffer to help us build the CSV value more
- effiently.
- --->
- <cfset LOCAL.Buffer = CreateObject( "java", "java.lang.StringBuffer" ).Init() />
-
- <!--- Create a short hand for the new line characters. --->
- <cfset LOCAL.NewLine = (Chr( 13 ) & Chr( 10 )) />
-
-
- <!--- Check to see if we need to add a header row. --->
- <cfif ARGUMENTS.CreateHeaderRow>
-
- <!--- Loop over the column names. --->
- <cfloop
- index="LOCAL.ColumnIndex"
- from="1"
- to="#LOCAL.ColumnCount#"
- step="1">
-
- <!--- Append the field name. --->
- <cfset LOCAL.Buffer.Append(
- JavaCast(
- "string",
- """#LOCAL.ColumnNames[ LOCAL.ColumnIndex ]#"""
- )
- ) />
-
- <!---
- Check to see which delimiter we need to add:
- field or line.
- --->
- <cfif (LOCAL.ColumnIndex LT LOCAL.ColumnCount)>
-
- <!--- Field delimiter. --->
- <cfset LOCAL.Buffer.Append(
- JavaCast( "string", ARGUMENTS.Delimiter )
- ) />
-
- <cfelse>
-
- <!--- Line delimiter. --->
- <cfset LOCAL.Buffer.Append(
- JavaCast( "string", LOCAL.NewLine )
- ) />
-
- </cfif>
-
- </cfloop>
-
- </cfif>
-
-
- <!---
- Now that we have dealt with any header value, let's
- convert the query body to CSV. When doing this, we are
- going to qualify each field value. This is done be
- default since it will be much faster than actually
- checking to see if a field needs to be qualified.
- --->
-
- <!--- Loop over the query. --->
- <cfloop query="ARGUMENTS.Query">
-
- <!--- Loop over the columns. --->
- <cfloop
- index="LOCAL.ColumnIndex"
- from="1"
- to="#LOCAL.ColumnCount#"
- step="1">
-
- <!--- Append the field value. --->
- <cfset LOCAL.Buffer.Append(
- JavaCast(
- "string",
- """#ARGUMENTS.Query[ LOCAL.ColumnNames[ LOCAL.ColumnIndex ] ][ ARGUMENTS.Query.CurrentRow ]#"""
- )
- ) />
-
- <!---
- Check to see which delimiter we need to add:
- field or line.
- --->
- <cfif (LOCAL.ColumnIndex LT LOCAL.ColumnCount)>
-
- <!--- Field delimiter. --->
- <cfset LOCAL.Buffer.Append(
- JavaCast( "string", ARGUMENTS.Delimiter )
- ) />
-
- <cfelse>
-
- <!--- Line delimiter. --->
- <cfset LOCAL.Buffer.Append(
- JavaCast( "string", LOCAL.NewLine )
- ) />
-
- </cfif>
-
- </cfloop>
-
- </cfloop>
-
-
- <!--- Return the CSV value. --->
- <cfreturn LOCAL.Buffer.ToString() />
- </cffunction>
As you can see, you have to pass in the query object and the list of field names that you want to output (in a given order). By default, the QueryToCSV() UDF assumes you want to create a header row and to use the comma as your field delimiter. When I create the CSV values, I am qualifying every field value with double quotes. While this is not necessary, it makes the process faster; if I had to evaluate each field to see if it required a field qualifier, the process would be much slower.
And, just a little sample code to see how easily this can be applied:
Launch code in new window » Download code as text file »
- <!--- Create a new part query. --->
- <cfset qPart = QueryNew(
- "id, serial_number, price",
- "cf_sql_integer, cf_sql_varchar, cf_sql_decimal"
- ) />
-
-
- <!--- Create some record. --->
- <cfloop
- index="intI"
- from="1"
- to="1000"
- step="1">
-
- <!--- Add a row to the query. --->
- <cfset QueryAddRow( qPart ) />
-
- <!--- Populate row with random data. --->
- <cfset qPart[ "id" ][ qPart.RecordCount ] = JavaCast( "int", qPart.RecordCount ) />
- <cfset qPart[ "serial_number" ][ qPart.RecordCount ] = JavaCast( "string", CreateUUID() ) />
- <cfset qPart[ "price" ][ qPart.RecordCount ] = JavaCast( "float", RandRange( 1, 100 ) ) />
-
- </cfloop>
-
-
- <!--- Get the CSV value. --->
- <cfset strOutput = QueryToCSV(
- qPart,
- "id, serial_number, price"
- ) />
Not much to see here, this is mostly for future reference on how to take a ColdFusion query and quickly and easily convert it to a CSV value.
Download Code Snippet ZIP File
Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Newer Post
XmlSearch() Ignores CDATA Sections In ColdFusion XPath
Older Post
The Philosophy Of Undocumented ColdFusion Features
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.
