Thanks for the suggestion. For one of my sites I'm going to need to do something along these lines, because the LOAD DATA function is disabled by the host in the shared hosting environment. I need to import/update the data (a CSV file that gets FTPed to the server) into a MySQL database a few times a day. It will be around 2000 lines of data with six columns.... Thankfully, I can control that the CSV file is in good condition and won't (knock on wood) throw any errors.
I'm also looking into CFHTTP. Any other ideas about what would be the most efficient and the least disruptive method? Many thanks, Megan Cytron >This might not be the most efficient way but it's easy. This could be a bit >quicker by running the loop inside the values statement but then you need to >to check for the last row of your .cvs file and remove the comma. I only run >this on 50 records or so, never really cared to look further into it. You >should also have values in in every insert statement, this doesn't look for >nulls either. Quick and dirty... > ><cffile action="read" file="C:\LOCATION TO FILE\Import.csv" >variable="fileData"> > <cfloop index="record" list="#fileData#" >delimiters="#Chr(10)##Chr(13)#"> > <cfquery name="qryInsertImport" datasource="#dsn#"> > INSERT INTO TheTable (THE 5 COLUMNS TO BE INSERTED) > VALUES ( > <cfqueryparam value="#listgetat(record,1,chr(44))#" >cfsqltype="cf_sql_numeric">, > <cfqueryparam value="#listgetat(record,2,chr(44))#" >cfsqltype="cf_sql_varchar">, > <cfqueryparam value="#listgetat(record,3,chr(44))#" >cfsqltype="cf_sql_varchar">, > <cfqueryparam value="#listgetat(record,4,chr(44))#" >cfsqltype="cf_sql_varchar">, > <cfqueryparam value="#listgetat(record,5,chr(44))#" >cfsqltype="cf_sql_varchar">, > ) > </cfquery> ></cfloop> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272538 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
