Thanks Rich, I've manipulated the file schema.ini, which defines all the tables and fields and the text files that make up the datasource. But when I define the "Interestrate" field as "float", I get no result into that column, because the actual content of the column is "14.0005%" not "14.0005" The percent symbol in the field means (I think) that it can only ever be defined as a text field. Unless I have missed something. Have I?
The only calculation I have to do with this field is sort so the output is in ascending order of interestrate (i.e. lowest rate at the top) - is there any other way that I'm overlooking to achieve this? (We don't have CF5 so we can't use query of query to do it) Cheers, Mike Kear AFPWebworks, Windsor, NSW, Australia. > -----Original Message----- > From: Rich Wild [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, December 04, 2001 9:07 PM > To: CF-Talk > Subject: RE: Manipulating a text file ... how to convert a type? > > > Mike - you can setup the text driver to auto-cast the datatypes > > go to your dsn's properties ODBC Text Setup >> Options >> Define Format > > >From there you can setup the datatype of each of the csv's columns. > > > -----Original Message----- > > From: Mike Kear [mailto:[EMAIL PROTECTED]] > > Sent: 04 December 2001 01:31 > > To: CF-Talk > > Subject: Manipulating a text file ... how to convert a type? > > > > > > I'm working with a text file, using it as a datasource, that > > an information > > provider puts into our site daily. One of the fields is > > "InterestRate" and > > the contents are values like "14.0005%" and "8.5000%". There > > is no problem > > as long as I just want to display this value. > > > > But now I want to sort the rows on the interest rate, and of > > course since > > it's a character field, it sorts 14.0005% as lower than > > 8.5000%. How can > > I convert the value from characters to numbers in the SQL? > > > > > > We're using the Microsoft .txt/.csv driver. Since the file > > is put in our > > site daily by the information provider, I can't change the > > file at all, > > because it's going to be replaced with a new one tomorrow. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
