This method of creating a CSV really works out well. You could also do a nested loop
and loop through rows of a 'normal' query, then loop through the cols for each row.
What you find with this is that when the query gets to be any size of significance
(even just a thousand records), the performance hit becomes huge. I had code that was
taking over a minute to run. Lesson learned: nested loop bad, efficient SQL and
ValueList() good.
I have also found the MS-SQL COALESCE function to be critical, because in the example
below if fieldB is null, it won't return that row at all. My example below is dumping
varchar fields.
To do dates you will have to do
COALESCE(CONVERT(varchar(10), Start_DT, 101), '')
To show a numeric field:
CAST(TranID AS varchar(30))
<cfquery datasource="#application.Datasource#" name="GetCSV"
cachedwithin="#LocalRosterQueryCacheTimeSpan#" blockfactor="50">
SELECT COALESCE(CHANGE, '') + ', ' +
COALESCE(SSN, '') + ', ' +
COALESCE(MEMBERNO, '') + ', ' +
COALESCE(LASTNAME, '') + ', ' +
COALESCE(FIRSTNAME, '') + ', ' +
COALESCE(I, '') + ', ' +
COALESCE(SUFFIX, '') + ', ' +
COALESCE(ADDRESS1, '') + ', ' +
COALESCE(ADDRESS2, '') + ', ' +
COALESCE(CITY , '') + ', ' +
COALESCE(STATE, '') + ', ' +
COALESCE(ZIP, '') + ', ' +
COALESCE(COUNTRY_CO, '') + ', ' +
COALESCE(A, '') + ', ' +
COALESCE(M, '') + ', ' +
COALESCE(SEX, '') + ', ' +
COALESCE(INS, '') + ', ' +
COALESCE(ID_MEMBER, '') + ', ' +
COALESCE(CONGR_DIST, '') + ', ' +
COALESCE(START_DT, '') + ', ' +
COALESCE(EMPLOYER, '') + ', ' +
COALESCE(BIRTH_DT, '') + ', ' +
COALESCE(PAY_GROUP, '') + ', ' +
COALESCE(COUNCIL, '') + ', ' +
COALESCE(HEALTH_CODE, '') + ', ' +
COALESCE(ACCIDENT_INSUR, '') + ', ' +
COALESCE(DENTAL_INSUR, '') + ', ' +
COALESCE(POS_CODE, '')
AS Row
FROM Membership
WHERE Local = '#session.LocalRoster.Local#'
AND District = '#session.LocalRoster.District#'
ORDER BY LastName, FirstName
</cfquery>
<!--- convert the query to csv format --->
<cfset l_Time = GetTickCount()>
<cfset BR = chr(13) & chr(10)>
<!--- Put in the header row --->
<cfset l_Output="CHANGE,SSN,MEMBER NO,LAST NAME,FIRST
NAME,I,SUFFIX,ADDRESS1,ADDRESS2,CITY,STATE,ZIP,CC,A,M,SEX,INS,ID,CD,Init.
Date,Agency,B. Date,PAY GRP.,COUNCIL,LH,LA,LD,Off. CODE#BR#">
<!--- Put in the data --->
<cfset l_Output = l_Output & ValueList(GetCSV.Row,BR)>
<cfset l_Time = GetTickCount() - l_Time>
<!--- Send the csv file to them via cfcontent --->
<cfcontent type="application/unknown"><cfoutput>#l_Output#</cfoutput><cfabort>
<!--- We must <cfabort> here so that no more HTML junk is output by the template --->
>>> "Joseph Thompson" <[EMAIL PROTECTED]> 07/14/02 12:18PM >>>
This script adds the ' and , right in the SQL. It concatenates all the
fields you need into a single comma seperated column called "line".
ValueList() creates a list from a query object; in this case we set the
delimiters to the windows "newline/carriage return" sequence. This makes it
very easy to insert the whole query into a file with a sing CFFILE call.
(note that you may need to use CFFILE action="write" if the file does not
allready exist)
> >
> ><CFQUERY NAME="GetDocs"...>
> > SELECT
> > '"' + RTRIM(FieldA) + '"' + ',' +
> > '"' + RTRIM(FieldB) + '"' + ',' +
> > '"' + RTRIM(FieldC) + '"'
> > AS line
> >
> > FROM
> > table
> ></CFQUERY>
> >
> ><!--- create the file content ---->
> ><CFSET filecontent = ValueList(GetDocs.line,"#CHR(13)##CHR(10)#")>
> >
> ><!--- and save it --->
> ><cffile action="APPEND" file="#csvfile#" output="#filecontent#">
> >
> >
>
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
