Here is what I've done so far: 1. Set up ODBC System DSN called TextSource that uses Microsoft Text Driver, pointed to my Upload directory, and for simplicity in this discussion, defined the fields contained in my csv.
2. Went into MX Administrator and created CSVFile Data Source defined as an ODBC Socket, and selected TexttSource as the ODBC DSN. I get this error when I hit submit: a.. Connection verification failed for data source: CSVFile []java.sql.SQLException: SQLException occurred in JDBCPool while attempting to connect, please check your username, password, URL, and other connectivity info. The root cause was that: java.sql.SQLException: SQLException occurred in JDBCPool while attempting to connect, please check your username, password, URL, and other connectivity info. What am I doing wrong? I've assigned User being System, and I don't have passwords on this test box. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, June 26, 2003 10:55 PM To: CF-Talk Subject: Re: CSV Question 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 Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
