Forgive the asp site...but it's got screenshots :) http://www.c-sharpcorner.com/database/Connect/ConnectODBCText.asp
A couple things to add to what the above site says. An ini file (schema.ini in example) will be created in the same directory as the csv after the dsn is created. Take a look at it..it's fairly self-explanatory. The built in CF ini functions will let you access and change it around, and add new text files for it. You only need to create one ini file for an nearly unlimited number of csv files in the same directory. After you create the dsn in windows, create an odbc socket dsn in CF pointing to the text dsn you created. That's "textsource" in my example. The schema.ini for the app I used this code in looks like this, just a lot bigger... [0927TPE-1.txt] ColNameHeader=False Format=CSVDelimited MaxScanRows=0 CharacterSet=OEM Col1=CUSTNAME Char Width 255 Col2=REPNAME Char Width 255 Col3=CUSTEMAIL Char Width 255 [100402me-111.txt] ColNameHeader=False Format=CSVDelimited MaxScanRows=0 CharacterSet=OEM Col1=CUSTNAME Char Width 255 Col2=REPNAME Char Width 255 Col3=CUSTEMAIL Char Width 255 ... -- mailto:[EMAIL PROTECTED] Thursday, June 26, 2003, 11:14:31 PM, you wrote: AO> Jon, AO> I am trying to use your code to translate a CSV file into a query. You AO> reference datasource="textsource". What is textsource? AO> Andy AO> -----Original Message----- AO> From: jon hall [mailto:[EMAIL PROTECTED] AO> Sent: Wednesday, January 29, 2003 7:12 PM AO> To: CF-Talk AO> Subject: Re: New problem with csv... argghhh AO> By editing the schema.ini file you can add a text datasource with CF's AO> ini file functions. That way you can dynamically add dsn's when the AO> end user uploads the file. Take a look at the format of the schema.ini AO> file that is created, it's pretty straightforward. AO> The fact that these files are very large, would be a reason to not AO> write a manual parsing routine. Microsoft (or someone they bought :)) AO> already wrote a very good and fast csv parser into the ODBC Text AO> driver...no need to recreate the wheel imo. I wrote quite a few csv AO> imports using CF manually and there is still a indentation the size of AO> my forehead on my desk because of those sleepless nights :) AO> If you were working with SQL Server, using DTS to do the import would be a AO> great way as well. Just wanted to throw some options your way. AO> This is a cffunction that takes the filename after is has been AO> uploaded, and creates the Text DSN, and returns the query. It could be AO> easily modified it work in previous versions of CF though. AO> <cffunction name="cvsToQuery" returntype="query"> AO> <cfargument name="filename" type="string"> AO> <cfset var schemaFile = "E:\schema.ini"> AO> <cfif len(trim(getProfileString(schemaFile, arguments.filename, AO> "ColNameHeader"))) EQ 0> AO> <cfset rs = setProfileString(schemaFile, arguments.filename, AO> "ColNameHeader", "False")> AO> <cfset rs = setProfileString(schemaFile, arguments.filename, AO> "Format", "CSVDelimited")> AO> <cfset rs = setProfileString(schemaFile, arguments.filename, "MaxScanRows", "0")>> AO> <cfset rs = setProfileString(schemaFile, arguments.filename, AO> "CharacterSet", "OEM")> AO> <cfset rs = setProfileString(schemaFile, arguments.filename, AO> "Col1", "CUSTNAME Char Width 255")> AO> <cfset rs = setProfileString(schemaFile, arguments.filename, AO> "Col2", "REPNAME Char Width 255")> AO> <cfset rs = setProfileString(schemaFile, arguments.filename, AO> "Col3", "CUSTEMAIL Char Width 255")> AO> </cfif> AO> <cfquery datasource="textsource" name="textqry"> AO> SELECT * AO> FROM [#arguments.filename#] AO> </cfquery> AO> <cfreturn textqry> AO> </cffunction> AO> -- AO> jon AO> mailto:[EMAIL PROTECTED] AO> 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 AO> 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>> AO> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
