just watch out for international numbers! :P On Wed, Sep 8, 2010 at 2:21 PM, Dusty Hale <[email protected]> wrote:
> 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 > > -- Steve Ross web application & interface developer http://blog.stevensross.com [mobile] (912) 344-8113 [ AIM / Yahoo! : zeriumsteven ] [googleTalk : nowhiding ]
