Everything sounds correct. The only other thing I did was uncheck maintain connections, which shouldn't affect whether or not it works. Does your schema.ini look normal, and is it in the right dir?
-- mailto:[EMAIL PROTECTED] Friday, June 27, 2003, 12:16:12 AM, you wrote: AO> Here is what I've done so far: AO> 1. Set up ODBC System DSN called TextSource that uses Microsoft Text AO> Driver, AO> pointed to my Upload directory, and for simplicity in this discussion, AO> defined AO> the fields contained in my csv. AO> 2. Went into MX Administrator and created CSVFile Data Source defined as an AO> ODBC Socket, and selected TexttSource as the ODBC DSN. AO> I get this error when I hit submit: AO> a.. Connection verification failed for data source: CSVFile AO> []java.sql.SQLException: SQLException occurred in JDBCPool while AO> attempting AO> to connect, please check your username, password, URL, and other AO> connectivity AO> info. AO> The root cause was that: java.sql.SQLException: SQLException occurred in AO> JDBCPool while attempting to connect, please check your username, password, AO> URL, and other connectivity info. AO> What am I doing wrong? I've assigned User being System, and I don't have AO> passwords on this test box. AO> -----Original Message----- AO> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] AO> Sent: Thursday, June 26, 2003 10:55 PM AO> To: CF-Talk AO> Subject: Re: CSV Question AO> Forgive the asp site...but it's got screenshots :) AO> http://www.c-sharpcorner.com/database/Connect/ConnectODBCText.asp AO> A couple things to add to what the above site says. AO> An ini file (schema.ini in example) will be created in the same AO> directory as the csv after the dsn is created. Take a look at it..it's AO> fairly self-explanatory. The built in CF ini functions will let you AO> access and change it around, and add new text files for it. You only AO> need to create one ini file for an nearly unlimited number of csv files in AO> the same directory. AO> After you create the dsn in windows, create an odbc socket dsn in CF AO> pointing to the text dsn you created. That's "textsource" in my AO> example. AO> The schema.ini for the app I used this code in looks like this, just AO> a lot bigger... AO> [0927TPE-1.txt] AO> ColNameHeader=False AO> Format=CSVDelimited AO> MaxScanRows=0 AO> CharacterSet=OEM AO> Col1=CUSTNAME Char Width 255 AO> Col2=REPNAME Char Width 255 AO> Col3=CUSTEMAIL Char Width 255 AO> [100402me-111.txt] AO> ColNameHeader=False AO> Format=CSVDelimited AO> MaxScanRows=0 AO> CharacterSet=OEM AO> Col1=CUSTNAME Char Width 255 AO> Col2=REPNAME Char Width 255 AO> Col3=CUSTEMAIL Char Width 255 AO> ... AO> -- AO> mailto:[EMAIL PROTECTED] AO> 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 AO> 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, AO> arguments.filename, AO>> "ColNameHeader", "False")> AO>> <cfset rs = setProfileString(schemaFile, AO> arguments.filename, AO>> "Format", "CSVDelimited")> AO>> <cfset rs = setProfileString(schemaFile, AO> arguments.filename, "MaxScanRows", "0")>>> AO>> <cfset rs = setProfileString(schemaFile, AO> arguments.filename, AO>> "CharacterSet", "OEM")> AO>> <cfset rs = setProfileString(schemaFile, AO> arguments.filename, AO>> "Col1", "CUSTNAME Char Width 255")> AO>> <cfset rs = setProfileString(schemaFile, AO> arguments.filename, AO>> "Col2", "REPNAME Char Width 255")> AO>> <cfset rs = setProfileString(schemaFile, AO> 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 AO> 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>> 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 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
