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
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
