Ask Ben: Exporting A Query To CSV Without Using Column Names
Posted October 29, 2008 at 9:58 AM by Ben Nadel
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:
- <cffunction
- name="PrepareCSVValue"
- access="public"
- returntype="string"
- output="false"
- hint="I take a CSV value and prepare it for the file.">
-
- <!--- Define arguments. --->
- <cfargument
- name="Value"
- type="string"
- required="true"
- hint="I am the raw data value."
- />
-
- <!---
- Esacpe any quotes in the value and then return the clean
- escaped string surrounded by quotes.
- --->
- <cfreturn
- (
- """" &
- Replace(
- ARGUMENTS.Value,
- """",
- """""",
- "all"
- ) &
- """"
- )
- />
- </cffunction>
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:
- <!---
- Create out data query (you would be using this as your
- database query, for demonstration, I have to build it
- manually).
- --->
- <cfset qData = QueryNew( "" ) />
-
- <!--- Add Query columns. --->
- <cfset QueryAddColumn(
- qData,
- "id",
- "cf_sql_integer",
- ListToArray( "1,2,3,4,5" )
- ) />
-
- <cfset QueryAddColumn(
- qData,
- "name",
- "cf_sql_varchar",
- ListToArray( "Ann,Kit,Libby,Jane,Beth" )
- ) />
-
- <cfset QueryAddColumn(
- qData,
- "pet_name",
- "cf_sql_varchar",
- ListToArray( "Cutie,""Honey"",Baby,Sugar,Sweetness" )
- ) />
-
-
-
- <!---
- Now, let's create our array of row data. We are going to
- create a CSV data set where each array index is its own
- row of data.
- --->
- <cfset arrData = [] />
-
- <!---
- Now, we are going to start off by building a row of column
- headers. Since we have (potentially) a lot of columns, we
- are going to be using the query's column list. Let's first
- break that up in to an array do its faster to navigate.
- --->
- <cfset arrColumns = ListToArray( qData.ColumnList ) />
-
-
- <!--- Ok, now lets create an array for our header data. --->
- <cfset arrRow = [] />
-
- <!--- Loop over columns. --->
- <cfloop
- index="strValue"
- array="#arrColumns#">
-
- <!--- Add prepared CSV value to row array. --->
- <cfset ArrayAppend(
- arrRow,
- PrepareCSVValue( strValue )
- ) />
-
- </cfloop>
-
- <!---
- Now that we have built up our row data, let's collapse it
- and add it to our master data array.
- --->
- <cfset ArrayAppend(
- arrData,
- ArrayToList( arrRow )
- ) />
-
-
- <!---
- Now that we have a header row out of the way, we have to
- basically do the same thing for the actual data rows.
- --->
- <cfloop query="qData">
-
- <!--- Create the array for this row. --->
- <cfset arrRow = [] />
-
- <!--- Loop over columns. --->
- <cfloop
- index="strColumn"
- array="#arrColumns#">
-
- <!--- Add prepared CSV value to row array. --->
- <cfset ArrayAppend(
- arrRow,
- PrepareCSVValue( qData[ strColumn ][ qData.CurrentRow ] )
- ) />
-
- </cfloop>
-
- <!---
- Now that we have built up our row data, let's collapse
- it and add it to our master data array.
- --->
- <cfset ArrayAppend(
- arrData,
- ArrayToList( arrRow )
- ) />
-
- </cfloop>
-
-
- <!---
- At this point, we have completely built up our master data
- array into index-based rows. Now, we have to collapse all
- those rows down into a single string variable that we can
- write to the file.
- --->
- <cfset strData = ArrayToList(
- arrData,
- (Chr( 13 ) & Chr( 10 ))
- ) />
-
-
-
- <!---
- We can now write this data to a file or simply output it.
- I am outputting for demonstration purposes.
- --->
- <cfoutput>
- #strData#
- </cfoutput>
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.
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
Hi,
Thanks for this Ben, I was looking for something similar, I wanted to contruct a table data display from an unknown query object, I don't know the column names or the values.
This pointed me in the right direction, in terms of creating the columnlist as an Array, and looping through it. I was using a nasty Evaluate() function before that.
@Shaun,
Using array-style notation on queries comes in super handy! Not only can we access queries values with dynamic names, we can also update cell values in a given record.
I came across that "is not indexable by 1.0" error. I was thinking of the result as an array and not as a query so when I was using COUPON_FEEDS[i] within my loop, I got that error. Problem solved when I used query of queries to get TOP 1 * and then got the name by FEED.NAME. One of my brain fart moments but I hope this helps someone else!
