On 3/12/07, megan cytron <[EMAIL PROTECTED]> wrote:
>
> >Does load data infile query of mysql doesnt work in cftransaction?
>
> I'm curious... did you ever figure this out? I'm also trying to determine
> the simplest/most efficient way of loading the data from a .csv file into a
> mysql DB using CF.
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:272467
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
