Ajas, that's a really neat function. Looks like it is indeed undocumented. It's a method of the cfquery resultset object (it works in CFMX 6.1 as well as 7). I modified the line setting mycollist in my code to use that, and the line following it, to be: <cfset mycollist = ArrayToList(demoq.getColumnList()) /> <cfoutput>#listchangedelims(mycollist,sep,",")##newline#</cfoutput>
And it worked like a charm. Cool stuff. (Though as for "doing it manually", if you'd not found this, I would have just said take the SELECT column list and put it in a variable. You must have already had one, in some order that was important. But this is indeed much better.) As for how long it took to write that example, it really didn't take long. But then, I was already familiar with the key points that would make it work: the fact that the column list was obtainable as a variable, and that it was a list, and that therefore it could be looped over, as well as the way to refer to query columns and rows dynamically (the trickiest bit). From there, it was just a matter of testing it a few times and evolving it to add more and more functionality. It may have taken an hour total, and then time to write up the email. I didn't mind, because I figured in the end it would result in something that not only you but others would benefit from. In particular, since there was none in the CFlib, it seemed worth offering, as much for the teaching opportunity it offered as for the specific result it provided. Glad it was helpful. BTW, if it really saved you in a professional way, I'll point out that I do have an Amazon wish list. :-) http://TinyURL.com/lhms5 /Charlie http://www.carehart.org/blog/ _____ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ajas Mohammed Sent: Wednesday, February 14, 2007 2:48 PM To: [email protected] Subject: Re: [ACFUG Discuss] CF - How to create tab delimited text file from query 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] <mailto:[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] <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 <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 <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>>
<<attachment: image002.jpg>>
