Sunday, July 8, 2007

Output Query To CSV file using Coldfusion MX and JAVA String Buffer

Using Java String buffer instead of CFFILE to convert a query to CSV file for output is much faster. Below is the code that is required, change the fileds and table name according to your dataabse.

<!--- Writing query to a CSV file using JAVA STRING BUFFER --->

<!--- get the details in a query, always use JOINS if you have more than one table --->
<cfquery name="get_records" datasource="xxxx">
select field1, field2, field3 from Table
</cfquery>

<!--- Give the location of your file where you will be writing the data --->
<cfset TempFile = (ExpandPath("dir") & ".csv") >
<!--- Create a STRING BUFFER to hold the output --->
<cfset QueryOutput = CreateObject("java","java.lang.StringBuffer").Init() >
<!--- append the column headers to string buffer, you also need to add a link break --->
<cfset QueryOutput.Append("field1, field2, field3" & chr(13) & chr(10))>
<!--- get an intial ID, so that we know when to append --->
<cfset Queryid = -1 >
<!--- loop through the query, and append the records to the buffer --->
<cfloop query="get_records">

<cfif (get_records.field1 NEQ Queryid)>

<cfset Queryid = get_records.field1 >

<cfset QueryOutput.Append("#get_records.field1#, #get_records.field2#, #get_records.field3#" & chr(13) & chr(10))>

</cfif>

<cfset QueryOutput.Append("#get_records.fields1#, #get_records.field2#, #get_records.field3#" & chr(13) & chr(10))>

</cfloop>

<!--- Write the string buffer to the file, suing ToString ---->

<cffile action="write" file="#TempFile#" output="#QueryOutput.ToString()">

<!--- End of writing query to a CSV file --->

5 comments:

Anonymous said...

Brijesh, this is an awesome solution. I had previously been using a cfloop process that wrote the records into a csv file one line at a time. On a 1,000,000 record query it was taking a long time. Great code, keep the tips coming!

Anonymous said...

Hi Brijesh...
I am using coldfusion 5. will this code works in coldfusion 5 also?

Brijesh said...

Hi Swamy, the above solution should work for CF 5, although I have not tested it. I have another code which works on CF 4.5, let me know if it does not work, I will post you the CF 4.5 code and it will work for CF 5.0

Anonymous said...

Hey,

Why you are taking Queryid as "-1"?

Inside loop, you are comparing the field1 NEQ QueryID and appending the data to StringBuffer and also appending outside CFIF. It is adding the first row two times as the first row field1 is not equal to Queryid.

Why you are doing this?
Why don't we just remove the cfif block inside the loop? The rest of the code works fine.

Thanks
Harry

Evelyne said...

Thanks.
Helps me a lot.

Evelyne