Hi Charlie and many thanks for the good advice. I got curious about running real queries on text files. I have done this some years ago using a DSN. I looked over your blog post on the zip code data. When I try to use this approach though I get the following error that the attribute "connectstring" does not exist for the cfquery tag (strange).
The tag does not have an attribute called connectstring. The valid attribute(s) are name, datasource, dbtype, sql, username, password, maxrows, blockfactor, timeout, dbname, cachedafter, cachedwithin, result, debug. On Mon, Mar 9, 2009 at 6:54 PM, Charlie Arehart <char...@carehart.org>wrote: > Dusty, I wonder if your problem isn’t a CF one, but a web server one. I’d > bet when you try to retrieve the file as a CSV, the web server steps in and > changes the mime type to something other than plain text, and therefore CF > (and the CFHTTP) gets something other than what they expected. > > > > (BTW, that code you show below doesn’t come from my site, per se, but > maybe you got it from a link off of it). > > > > I just ran a test of some working code (adapted from an example Ben Nadel > put together). It works fine for me, whether file is called .txt or .csv. > I’ve attached the files here. Do they work for you (may need to adjust the > url in the cfm page)? If not, then I’d think the web server is your issue. > > > > (You could also request the CSV in your browser, or—to remove browser > processing from the analysis--do your CFHTTP without the NAME attribute, > which then just reads it as a text file. Dump the entire CFHTTP scope, to > see the cfhttp.mimetype. > > > > Anyway, here’s a simpler way to read in a CSV as a database (one I do link > to from my CF411 site, and which would be easier than the older approach I > mentioned in my last note), which doesn’t rely on CFHTTP or then get > bothered by any web server mapping issues: > > > > http://www.coldfusionmuse.com/index.cfm/2007/2/5/csv > > > > Hope that’s helpful. > > > > /charlie > > > > PS I’d like to add, since someone said they didn’t realize it, that this > sort of troubleshooting is indeed what I do for a living. I’m happy to > answer questions free here on the list, as I do so often. But I just want to > note that if someone ever is in a pinch and doesn’t want to wait for free > answers, or doesn’t want to publicize their problem, or it may be too much > to communicate in email, I’m available for as little time as may be needed > to solve a problem. More at carehart.org/consulting/. > > > > *From:* ad...@acfug.org [mailto:ad...@acfug.org] *On Behalf Of *Dusty Hale > *Sent:* Monday, March 09, 2009 3:15 PM > *To:* discussion@acfug.org > *Subject:* Re: re[2]: [ACFUG Discuss] excel or csv to database table > > > > Thanks again. I tried the approach below which was recommended on > cf411.com. It works as long as the file extension is .txt. When using the > .csv extension, it throws an error "Variable onerow does not exist". > > I am fine with just using the .txt extention but if anyone has any clue why > or how I could use a .csv extension, I would love to hear it. > > Thanks again for the replies. It always helps to hear others opinions. > > Dusty > > ---------------------------------------------------- > *Building a query from a delimited text file* > > The cfhttp tag can create a ColdFusion query object form the response > body. To do so, the response body must consist of lines of text, with each > line having fields that are delimited by a character that identifies the > column breaks. The default delimiter is a comma (,). The response data can > also use a text qualifier; the default is a double-quotation mark ("). If > you surround a string field in the text qualifier, the field can contain the > delimiter character. To include the text qualifier in field text, escape it > by using a double character. The following line shows a two-line request > body that is converted into a query. It has three comma-delimited fields: > > Field1,Field2,Field3 > > "A comma, in text","A quote: ""Oh My!""",Plain text > > Run the following code to show how ColdFusion treats this data: > > <cfhttp method="Get" > > url="127.0.0.1:8500/tests/escapetest.txt" > > name="onerow"> > > <cfdump var="#onerow#"><br> > > > ------------------------------------------------------------- > 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> > ------------------------------------------------------------- -- Dusty Hale Website: www.DustyHale.com Email: du...@dustyhale.com Phone (Atlanta): 404.474.3754
