Re: cftransaction ( mysql load data infile)--CFHTTP??
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
Re: cftransaction ( mysql load data infile)--CFHTTP??
On 3/13/07, megan c wrote: Thanks for the suggestion. For one of my sites I'm going to need to do something along It is EASY with cfhttp (if you've got well formed data), but I have never load tested it. or (not nearly as easy) If you've got createObject power you can use HSSQLDB, which seems pretty robust for CSV stuff- but CFHTTP is probably fastest from 0 to Query. The one advantage to rolling your own, is you can do it all in one statement (if it's really well formed, mind), with the old INSERT INTO table (col1,col2,col3) VALUES ( (row,one,values), (row,two,values), (row,three,values) ) Or something like that. Instead of 2000 inserts, you can do one- but it'll be a monster. Just another option or two to keep in mind, or whatnot. Happy hunting! ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272617 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cftransaction ( mysql load data infile)--CFHTTP??
It is EASY with cfhttp (if you've got well formed data), but I have never load tested it. or (not nearly as easy) If you've got createObject power you can use HSSQLDB, which seems pretty robust for CSV stuff- but CFHTTP is probably fastest from 0 to Query. The one advantage to rolling your own, is you can do it all in one statement (if it's really well formed, mind), with the old INSERT INTO table (col1,col2,col3) VALUES ( (row,one,values), (row,two,values), (row,three,values) ) Or something like that. Instead of 2000 inserts, you can do one- but it'll be a monster. Thank you so much--this worked like a charm (once I realized that there was a tab at the end of the lines mucking things up, that is). ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272627 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cftransaction ( mysql load data infile)--CFHTTP??
On 3/13/07, megan c wrote: Thank you so much--this worked like a charm (once I realized that there was a tab at the end of the lines mucking things up, that is). Glad I could help! ~| ColdFusion MX7 and Flex 2 Build sales marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272634 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
