wow this is clearly the way to go moving forward - but now I need to ask if I'm so far into this already that I need to bully my way through based on current contract work/payments, or scrap all these hours...
At 08:11 PM 1/29/03 -0500, you wrote: >By editing the schema.ini file you can add a text datasource with CF's >ini file functions. That way you can dynamically add dsn's when the >end user uploads the file. Take a look at the format of the schema.ini >file that is created, it's pretty straightforward. > >The fact that these files are very large, would be a reason to not >write a manual parsing routine. Microsoft (or someone they bought :)) >already wrote a very good and fast csv parser into the ODBC Text >driver...no need to recreate the wheel imo. I wrote quite a few csv >imports using CF manually and there is still a indentation the size of >my forehead on my desk because of those sleepless nights :) >If you were working with SQL Server, using DTS to do the import would be a >great way as well. Just wanted to throw some options your way. > >This is a cffunction that takes the filename after is has been >uploaded, and creates the Text DSN, and returns the query. It could be >easily modified it work in previous versions of CF though. > ><cffunction name="cvsToQuery" returntype="query"> > <cfargument name="filename" type="string"> > <cfset var schemaFile = "E:\schema.ini"> > > <cfif len(trim(getProfileString(schemaFile, arguments.filename, > "ColNameHeader"))) EQ 0> > <cfset rs = setProfileString(schemaFile, > arguments.filename, "ColNameHeader", "False")> > <cfset rs = setProfileString(schemaFile, > arguments.filename, "Format", "CSVDelimited")> > <cfset rs = setProfileString(schemaFile, > arguments.filename, "MaxScanRows", "0")> > <cfset rs = setProfileString(schemaFile, > arguments.filename, "CharacterSet", "OEM")> > <cfset rs = setProfileString(schemaFile, > arguments.filename, "Col1", "CUSTNAME Char Width 255")> > <cfset rs = setProfileString(schemaFile, > arguments.filename, "Col2", "REPNAME Char Width 255")> > <cfset rs = setProfileString(schemaFile, > arguments.filename, "Col3", "CUSTEMAIL Char Width 255")> > </cfif> > <cfquery datasource="textsource" name="textqry"> > SELECT * > FROM [#arguments.filename#] > </cfquery> > <cfreturn textqry> ></cffunction> > > >-- > jon > mailto:[EMAIL PROTECTED] > >Tuesday, January 28, 2003, 7:27:05 PM, you wrote: >RAB> At 06:11 PM 1/29/03 -0500, you wrote: > >>Create a ODBC Text Datasource to the file one the machine and then > >>just use cfquery to turn it into a query...which you can loop over or > >RAB> THe problem is it's a multi-thousand record product table from multiple >RAB> manufacturers and new prices come out all the time - so the client >needs a >RAB> way to batch change specific product records... If I create an ODBC >RAB> connection, wouldn't I need to shut it off for every time the Text >RAB> datasource is changed? > >RAB> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
