Ask Ben: Exporting A Query To CSV Without Using Column Names

Posted October 29, 2008 at 9:58 AM

Tags: ColdFusion, Ask Ben

I have an issue that's been bugging me for a while now and I've had to back out of my CF application just to get the job done with SQL Server Enterprise Mgr. The issue is this: I am trying to export a cfquery result set to a file and I want to use all the columns in the table. There are over 300 columns in this table with about 700+ rows. I have been able to create a header row with .columnlist but I cannot seem to get beyond that. I am stuck on what to do with "output" under cffile. I've tried all sorts of string manipulations to get it to print every column. It has not worked. What would you say is the way to get past this? Please don't tell me I have to write out each column name and surround them with hash marks.

You are very close to the solution using the .ColumnList property; I'm sure you'd probably get it eventually, so I'll just help fill in the blanks. Basically, what we want to do is use the ColumnList property to output both the column headers as well as the data values. The trick to this solution is knowing that you can reference a query value using array notation:

Query[ ColumnName ][ RowIndex ]

Once you know this, you can probably start to see how a Query can be thought of as a two-dimensional array. And, once you make that mental leap, it's not too hard to imagine the nested loop that could be employed to loop over every cell value.

When it comes to creating CSV files, I like to put all of my columns in quotes if I think that any of them might contain quotes or commas. I know that this can end up putting a few extra characters in the CSV file, but I think it's a solid methodology. To help centralize this task, I write some sort of ColdFusion user defined function that encapsulates the preparation of each CSV value:

 Launch code in new window » Download code as text file »

Notice that this UDF both escapes any quotes in the value and as well as wrapping the given value in its own quotes. We can always change this later; for example, we may decide that we want to check to see if a value has any characters that require escaping before we actually quote the entire value.

Ok, now that we have that out of the way, let's create a query (for testing purposes) and prepare the CSV data:

 Launch code in new window » Download code as text file »

As you can see, we never make a single hard-coded reference to any column names. We simply break the column name list into an array and loop over that array any time we need to get at that column's values. Running the above code, we get the following output:

"ID","NAME","PET_NAME"
"1","Ann","Cutie"
"2","Kit","""Honey"""
"3","Libby","Baby"
"4","Jane","Sugar"
"5","Beth","Sweetness"

I only have a few columns and a few rows, but since none of the columns or rows are hard-coded, this is implicitly scalable to any size query you have (assuming you don't run out of RAM building the values). Also, notice also that the quotes in the "Honey" value were escaped as per the CSV standard.

Hope that helps. If you would like to see a UDF for this process that does mostly the same thing, but allows you to choose which columns to use, check out my QueryToCSV() ColdFusion function.

Download Code Snippet ZIP File

Post Comment  |  Ask Ben  |  Permalink  |  Other Searches  |  Print Page

• Add To Del.icio.us
• Add To Digg
• Add to Spurl.net
• Add to Wists
• Add to Simpy
• Add to Newsvine
• Add to BlinkList
• Add to Furl
• Add to reddit
• Add to FARK
• Add to BlogMarks
• Add to Yahoo! My Web
• Add to smarking
• Add to Ma.gnolia
• Add to Segnalo

Reader Comments

I am getting compile errors with '[' and '{' in your cfset statements.
<cfset arrData = [] />
It says it's not a valid CFML statement. I wonder if it's because I'm using Cf MX 7 and these are used in CF 8. True?

@Jonathan,

Yeah, those are CF7 vs. CF8 errors. You need to replace [] with ArrayNew( 1 ). You will also need to update the loop tags. Right now, the solution uses array looping. In CF7, you need to use index looping:

<cfloop index="intColIndex" from="1" to="#ArrayLen( arrColumns )#">

<cfset strColumn = arrColumns[ intColIndex ] />

... and there's probably a few of those.

And, if you do run into a RAM issue (Ben's note about "assuming you don't run out of RAM building the values"), and you might with 210,000 values, then just think about writing to your output file one line at a time, or blocks of lines at a time.

So, where Ben shows
ArrayAppend(
arrData,
ArrayToList( arrRow )
)

instead use that location to CFFILE action="append" the ArrayToList( arrRow ) to your target file. That will cause a lot more file I/O, but will not overwhelm the server RAM. To cut down on file I/O, you could keep writing to arrData, but then just write out arrData to the file on every 20th row or something and then reset arrData so it doesn't get too huge.

HTH,
-jfish

@Jason,

Excellent point; thanks.

Ben,

In the line 'qData[ strColumn ][ qData.CurrentRow ]', I am getting a compile error saying:

[Table (rows 5 columns id, name, pet_name): [id: coldfusion.sql.QueryColumn@a46637] [name: coldfusion.sql.QueryColumn@132cb29] [pet_name: coldfusion.sql.QueryColumn@1b829c7] ] is not indexable by 1.0

I saw your "...by name" entry from '06 but I'm not sure if this is another MX7 v. 8 deal or something different. Any help?

And thanks Jason for the optimization!

@Jonathan,

It means you are trying to use the column name, "1.0". I am not sure that you can have periods in column names which leads me to think that something went wrong somewhere. Are you using the index to get the column name? The first thing you need to do inside the CFLoop is get a true reference to the column name:

<cfset strColumn = arrColumns[ intColumnIndex ] />

If you don't do that, you are probably using the NUMERIC index as your column name.

It was my fault as I was changing up the code for Cf 7. I was getting the loop count number and not the value of the array, which Cf 8 seems to do you in the cfloop tag. Now that I was able to get your code to work on my box, I'll apply the same technique to my CSV application. Really appreciate your help.

Hello, look at the new i/o features on cf8 usually it helps...

page: 26

http://cfunited.com/presentations/2007/AC308

Post Comment  |  Ask Ben

Post A Comment

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Formatting: <strong>bold</strong> <em>italic<em>
	Remember my information
	Subscribe to entry *
	Send me a copy of this comment (as part of the comment thread)

 

* If you subscribe, any new posts to this thread will be sent to your email address.