Hi Charlie, Thanks for the input again. I was thinking of doing it manually and I guess thats what you are saying also and it involves typing the 84 column names in a variable. Correct me if I am wrong in my assumption here.
I found a solution to this problem and to be honest I am quite surprised that it works.It lists the query columns in the same order as the select query. I tried it and it works great. Looks like this one is undocumented feature. <cfoutput> <cfset colHeaderNames = ArrayToList(results.getColumnList()) /> #colHeaderNames# </cfoutput> I am so happy. By the way, how long did it take you to write that code you sent me yesterday? I want to develop my CF coding skills too. -- <Ajas Mohammed /> http://ajashadi.blogspot.com On 2/14/07, Charlie Arehart <[EMAIL PROTECTED]> wrote:
Thanks, Ajas. As for your challenge, here's a simple solution (sorry I didn't think to mention it yesterday). Put the list of columns you want to select in a variable, and use that variable in your SELECT (as in SELECT #mycollist# from ...) and then remove the line where I created it as a result of the sort of the built-in columnlist. That will cause the rest of the code to use "your" list. That should do it. Let us know how it goes. /Charlie http://www.carehart.org/blog/ ------------------------------ *From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] *On Behalf Of *Ajas Mohammed *Sent:* Wednesday, February 14, 2007 12:03 PM *To:* [email protected] *Subject:* Re: [ACFUG Discuss] CF - How to create tab delimited text file from query Charlie, All I can say is wow... Its a very neat & clean code... Thank you so much. I am bit sad myself as I didnt come up with a solution. Still learning I guess.... :-( answer to some of your questions --> Yes file has to be tab delimited. We send export file to client which should be a tab delimited text file. Your code will solve that issue. Thanks. The order of columns is very important for me. So I cannot live with the sort or the default CF way of displaying records with alpha sort. So the question is : How do we make CF return the results in the order the Select query is actually written? This is crucial since client expects the file in a particular order and I dont blame them because this file is used by there system for reporting or whatever. So can we do this??? My specs : *Server Details * Server Product ColdFusion MX Version 7,0,1,116466 Edition Enterprise Operating System Windows 2003 Thanks again for this wonderful script and advise on columns order issue. -- <Ajas Mohammed /> http://ajashadi.blogspot.com On 2/13/07, Charlie Arehart <[EMAIL PROTECTED]> wrote: > > Ajas, does it really need to be tab-delimited? Or is comma-separated > (CSV) ok? > > The latter is available at the cflib.org site, a great site with > pre-written solutions to common problems. It has a search area, and > searching for CSV found these: > http://www.cflib.org/udf.cfm?ID=556 > or > http://www.cflib.org/udf.cfm?ID=1197 > > But neither mentions creating tab-delimited. Actually, I first searched > there for tab and found nothing, so started on something of my own to offer > you. After writing it as a CSV solution, I was about to offer it and > just thought there had to be a CSV solution already. I searched and found > those about. > > Still, those are each a little more complicated (and perhaps more > feature-rich or better written) than mine, so I'll show you what I did > and how you can make it either comma- or tab-separated. It may offer some > points of learning for you or others: > > <CFQUERY NAME="demoq" DATASOURCE="cfdocexamples" > cachedwithin="#createtimespan(1,0,0,0)#"> > select lastname, firstname, phone, location from employees > </CFQUERY> > > <Cfsetting showdebugoutput="No" enablecfoutputonly="Yes"> > <cfcontent type=" text/plain "> > > <Cfset sep = chr(9)> > <Cfset delim = "" > > <cfset newline = chr(13) & chr(10)> > > <cfset mycollist = listsort(demoq.columnlist,"text")> > > <cfoutput>#listchangedelims(demoq.columnlist > ,sep,",")##newline#</cfoutput> > <cfloop query="demoq"> > <Cfset row=""> > <cfloop list="#mycollist#" index="i"> > <cfset > row=listappend(row,"#delim##demoq[i][currentrow]##delim#",sep)> > </cfloop> > <cfoutput>#row##newline#</cfoutput> > </cfloop> > > That creates output like this: > > FIRSTNAME LASTNAME LOCATION PHONE > Carolynn Peterson Newton (612) 832-7654 > Dave Heartsdale San Francisco (612) 832-7201 > If that doesn't look right above, just know that it would generate > tab-separated columns, which it seemed you desired. What's going on? > > Well, first note that it's dynamically getting the names of the columns > in the query, using the columnlist variable available for any cfquery, and > it loops over the record set to output those. But it's got to accommodate a > few things. > > First, if the order of the columns is important, such as if you wish > they would come out in the order listed in the SELECT, I don't think CF does > that (even if you use other than the SELECT * I did). It always came out in > alpha order. Still, I don't know what version you're running, so to force it > to always be in alpha order, you'll see I added a listsort. That sorted list > has to be used twice, once for the list of column headers, then to get the > data. > > To switch it from tabs to comma separated values, just change the sep > variable value from chr(9), a tab, to just a comma. > > Now, I'm not worrying about putting quotes around things that are > strings, and you may not need that. If you need to do that, I've provided a > delim variable that's currently empty. If you'd want to use quotes around > strings and not around numbers, I leave it to you to consider > expanding/revising that. > > You may wonder about the CFSETTING and CFCONTENT lines near the top. > Those will probably be critical. It may be important to tell the web > server to send it as plain text (instead of an html file), so lead it with > a: > > <cfcontent type="text/plain"> > > And more than that, you probably also want to remove any white space and > also avoid sending any debugging HTML at the end (if enabled). Since the > approach I took above only outputs data inside a single CFOUTPUT inside the > loop (rather than using a CFOUTPUT QUERY loop), you can just use the > following line (at the top) to kill 2 birds with one stone: > > <Cfsetting showdebugoutput="No" enablecfoutputonly="Yes"> > And as for causing a line break after the header line and between each > row of data, you can't use a <br> tag, since you're not generating HTML. > Instead, you'll note that I've used a newline variable which is set by > default to chr(13) chr(10), which creates a CRLF (carriage return/linefeed) > that I use when needed. > > I've not made it into a UDF, to be reusable (where you can pass it a > query and get the result), but again I leave that as an exercise for > readers. And while it would be nice to offer an update to one of the CFLIB > examples to make it support a tab, I've really spent too much time on this > as it is and will let anyone else interested propose that to their authors. > > Finally, Ajas, you said you wanted them to be able to download it. I > don't know if you really mean they want to save it, but I should add that if > your goal is ultimately for them to open it in a spreadsheet, you don't need > to make them save it first, or even right-click on your download link to > open in Excel, for instance. Instead, you can change the CFCONTENT and add a > CFHEADER: > > <CFHEADER NAME="Content-Disposition" VALUE="inline; filename=export.xls > "> > <CFCONTENT TYPE="application/msexcel"> > and now when the page is loaded it will open in the spreadsheet. Enjoy. > > /Charlie > http://www.carehart.org/blog/ > > > > ------------------------------ > *From:* [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED] *On Behalf Of *Ajas > Mohammed > *Sent:* Tuesday, February 13, 2007 4:39 PM > *To:* [email protected] > *Subject:* [ACFUG Discuss] CF - How to create tab delimited text file > from query > > Hi there, > Looks like today is DB day. I want to develop a feature > wherein user accessing the site should be able to create a tab delimited > text file ( i have a query which will be used) and user should also be able > to download this new tab delimited text file. > > Any suggestions are welcome. I have about 84 columns in that query so > please advise the best option. > > -- > <Ajas Mohammed /> > http://ajashadi.blogspot.com > ------------------------------------------------------------- > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?fa=login.edituserform > > For more info, see http://www.acfug.org/mailinglists > Archive @ http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by FusionLink <http://www.fusionlink.com> > ------------------------------------------------------------- > > ------------------------------------------------------------- > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?fa=login.edituserform > > For more info, see http://www.acfug.org/mailinglists > Archive @ http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by FusionLink <http://www.fusionlink.com> > ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink <http://www.fusionlink.com> ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink <http://www.fusionlink.com> -------------------------------------------------------------
-------------------------------------------------------------To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------
<<attachment: image002.jpg>>
<<attachment: image002.jpg>>
