Ahh yes ... hostmysite had to set up the DSN for this and when I called last night I asked about that. I was working last night from my mac so wasn't able to check on a local server.
Now makes perfect sense. Honestly this is the first time I ever really used SQL code to query text files. Concerning the mask for the phone number to remove hyphens, actually in the real app there is other code that will mask all formats to (xxx) xxx-xxxx. Thanks so much Ajas for working on this. Check off list for another message from me. On Wed, Sep 8, 2010 at 8:00 AM, Ajas Mohammed <[email protected]> wrote: > Ok, I got the file from Dusty off the list and I played around with the > system dsn settings. Turns out you can set formatting for columns. See > attached snapshot. > > First things first. The data returned by query doesnt return the phone > number if it has Hyphen. It just returns empty string. I did a cfdump first > to see what is going on. > > Once that was done, I opened the System ODBC DSN I had created and > formatted the phone and fax as char and set width as 20. The initial > formatting was set to FLOAT. > > After I did that, the number appeared fine. > > Not sure if this is what you can do on your end for this project but its > something you can consider. > > Once I got the phone number in a query result set, I used <cfset > formatPhone = replace(qLEsd1.Phone,'-','','all') > to remove hyphens. Not > sure we need that but I got little excited. :-) > > Biggest thing was getting the phone and fax, and we have it now. :-) > > > > <Ajas Mohammed /> > http://ajashadi.blogspot.com > We cannot become what we need to be, remaining what we are. > No matter what, find a way. Because thats what winners do. > You can't improve what you don't measure. > Quality is never an accident; it is always the result of high intention, > sincere effort, intelligent direction and skillful execution; it represents > the wise choice of many alternatives. > > > On Wed, Sep 8, 2010 at 12:00 PM, Charlie Arehart <[email protected]>wrote: > >> 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 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
