> I have been having this ongoing battle with large files (3 MB or more).
>
>
> Basically each file is a comma delimited text file that on every line
> has information that needs to be checked against the database and then
> either updated or inserted.
>
> Right now I loop through every line in the file and using CFQUERY to
> do the database check and insert/update.
>
> But with the files being 3 MB, it is slow and timesout. The customer
> is not happy.
>
> For the database check I use "IF EXISTS (....) UPDATE .... ELSE INSERT ....
> "
>
> The ISP is using CF Server MX 6 and SQL Server 2000. I cannot use
> CFFILE for security reasons, they created their own CFX tag to
> accomodate uploads. So trying to access the file via SQL Server 2000
> probably isn't possible.
>
> Is there a better way to deal with large files and trying to get them
> into the database?
>
> Any ideas would be appreciated!
If they allow cfobject or createObject you may want to try using the fileO and
line reader objects from java to read the file, then process each line. Here's
function that reads a file then puts the contents into an array:
<cffunction name="readFileContents" access="public" output="false">
<cfargument name="fileName" required="true" type="string" />
<cfset var fileReader = createObject("java", "java.io.FileReader") />
<cfset var lineReader = createObject("java","java.io.LineNumberReader")
/>
<cfset var thisLine = true />
<cfset var results = arrayNew(1) />
<cfset fileReader = fileReader.init(expandPath(arguments.filename)) />
<cfset lineReader = lineReader.init(fileReader) />
<cfloop condition='isDefined("thisLine")'>
<cfset thisLine = lineReader.readLine() />
<cfif isDefined("thisLine")>
<cfset arrayAppend(results,thisLine) />
</cfif>
</cfloop>
<!--- not sure whether these lines are necessary,
still learning how to work with Java objects --->
<cfset fileReader.close() />
<cfset lineReader.close() />
<cfreturn results />
</cffunction>
hth,
larry
--
Larry C. Lyons
Web Analyst
BEI Resources
American Type Culture Collection
http://www.beiresources.org
email: llyons(at)atcc(dot)org
tel: 703.365.2700.2678
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:280368
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
