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 <http://www.cflib.org/udf.cfm?ID=556> or http://www.cflib.org/udf.cfm?ID=1197 <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] <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 <http://ajashadi.blogspot.com> ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform <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>>
