Stumper. (Thanks for the clarifications about multiple files in a text DSN. It's been years since I last used the feature, so my memory was hazy.)
/charlie From: [email protected] [mailto:[email protected]] On Behalf Of Dusty Hale Sent: Wednesday, September 08, 2010 11:54 AM To: [email protected] Subject: Re: [ACFUG Discuss] bizarre issue with output from cfquery There is one Cold Fusion datasource that points to a system datasource that uses the MS text file driver. It allows me to query any of the csv files I have in a folder. For example: <cfquery name="myname" datasource="mydata"> select phone, fax from [pathToFolder]\[filname].csv </cfquery> Then as a test scenario I output as follows: <cfloop query="myname"> <p>Phone: <cfif len(phone)>#phone#<cfelse>[empty]</cfif> <br />Fax: <cfif len(fax)>#fax#<cfelse>[empty]</cfif></p> </cfloop> There's no problems with HTML. Also to confirm, you don't have to define a datasource to one file. You can set up a datasource in CF that points to a machine user datasource to an entire folder and then query multiple files. I query the files individually though (no joins). For the most part it works great. So last night I emptied the file in question and started add the data back line by line (it only has 25 records/lines). I inspected the data manually and see nothing wrong with it. Correct number of commas. No escape character problems, etc. When adding data back I get really bizzare behavior. For example, the first few records output fine as long as the phone number in the first record DOES contain dashes (even though other below it don't), however REMOVE the dashes from the phone number in the first record and then suddenly all phone numbers with dashes (in other records) output empty. Then it gets more bizzarre because I'm adding the data to the file line by line trying to find something strange about the data itself and once I add line 5 of the data back, the empty phone string starts happening again. Yet inspecting the data, I really find absolutely nothing wrong. I've also notice that if I add any non numeric character to a phone number, it also outputs empty. Ironically, table1 (another csv file) which is identical in structure, does not have this problem (it has different but similar data in it). I also realized that file1 also has this problem but I wasn't catching it because there was only one phone number in the data that contained dashes and it also outputs empty. I've concluded that somehow it will not output the phone or fax field unless it contains a int type data (number). Adding any non-numeric character to the phone or fax will cause it to output empty. Yet I'm using text files and there is no way to configure data types that I'm aware of. On Wed, Sep 8, 2010 at 4:33 AM, Charlie Arehart <[email protected]> wrote: I'm curious about a couple of things: - you refer to "two tables(files) in the tdatabase", but being CSV files, there is no "database", right? Do you mean in "the datasource"? If so, why/how would you have two CSV files defined in one datasource? Does anything change if you create two DSNs, one for each file? Or if you flip the order of their definition in the one datasource (or database)? That's just a wild guess. The bigger questions are the two. - when you say things "are outputting" funky, are you outputting them as HTML? Have you looked at the underlying HTML source (in the browser)? Maybe the data is there, but something else is causing them to appear to be "empty". I've seen it happen. As an alternative to doing "view source" all the time, you can also use HTMLCodeFormat in CFML for the output you're generating, to show the resulting HTML on screen as the tags that would be built , rather than seeing the result of the HTML being rendered on screen. Not much, but hope that helps. /charlie From: [email protected] [mailto:[email protected]] On Behalf Of Dusty Hale Sent: Tuesday, September 07, 2010 9:42 PM To: [email protected] Subject: [ACFUG Discuss] bizarre issue with output from cfquery Has anyone ever experienced anything like this: 1. I have an ODBC datasource set up to query comma delimited text files. Works great except .... 2. I have two tables(files) in the tdatabase and both are identical in structure and both have a phone number field. In both tables some of the phone numbers contain dashes and some don't. 3. When I query table one and test the output, no problem, works great. When I query table two, also works fine except one very bizarre thing. The phone numbers with dashes are outputting as empty or null. Phone numbers that don't have dashes output as normal. So I've been struggling with this issue and don't see how it's possible being that both table are identical in structure. Since they're not identical in data, I assume it has to be something data related but still can find nothing wrong. Does anyone have any experience with CF and using text files as a database and had a problem like this. Any thought or advice is greatly appreciated. Kind regards, Dusty -- Dusty Hale - President Hale Technologies, Inc. Email: [email protected] Phone (Toll Free USA): 1.877.841.3370 Phone (Atlanta): 1.404.474.3754 Website: www.DustyHale.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> ------------------------------------------------------------- -- Dusty Hale - President Hale Technologies, Inc. Email: [email protected] Phone (Toll Free USA): 1.877.841.3370 Phone (Atlanta): 1.404.474.3754 Website: www.DustyHale.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 -------------------------------------------------------------
