Thanks Martyn, was hoping someone had a MySQL method to handle large text files. Might be needing that.
-- Josh ----- Original Message ----- From: "Martyn Bowis" <[EMAIL PROTECTED]> To: "CF-Talk" <cf-talk@houseoffusion.com> Sent: Friday, July 28, 2006 1:29 AM Subject: Re: reading large text file > Hi Martin, > > Try using a bulk import or bulk copy method with your SQL database. MSSQL > has a DTS that can do this, and MYSQL has the following: > > <cfset newline = Chr(13) & Chr(10) > > <cfquery ....the usual stuff here ... > > LOAD DATA INFILE '#fabsolute_file_path#' INTO TABLE RAWDATA > FIELDS TERMINATED BY ',' ENCLOSED BY '"' > LINES TERMINATED BY '#newline#' > IGNORE 1 LINES > </cfquery> > > Create your RAWDATA table first - can even do this dynamically if the > number and naming of your data columns varies with each file. > Newline is a line break and carriage return ascii values. > Ignore first line if it has column titles in it. > Be sure that you use double backslashes in your file path for MYSQL (can > do with ReReplace function) > Use DROP TABLE RAWDATA to quickly empty your database of that table's > content. Then just CREATE it again. > > What could take many minutes with CFFile, etc. will only take seconds with > bulk import/copy. > > Once you have your data in your temporary table, then just use SQL > statements to put it into your other tables as required. > > Cheers, > Martyn > > > > On 7/28/2006 7:48:40 PM, gert franz ([EMAIL PROTECTED]) wrote: >> Hi Martin, >> >> you could use Railo instead, since Railo can loop through a large file >> line by line without reading it completely. Just use the <cfloop >> file="..."> tag to process the file, just as explained here: >> >> http://www.railo.ch/en/index.cfm?treeID=144 >> >> Regards Gert >> >> Greetings / Grüsse >> Gert Franz >> Customer Care >> [EMAIL PROTECTED] >> www.railo.ch >> >> Join our Mailing List / Treten Sie unserer Mailingliste bei: >> deutsch: http://de.groups.yahoo.com/group/railo/ >> english: http://groups.yahoo.com/group/railo_talk/ >> >> >> >> Martin Thorpe wrote: >> > Hello all. >> > >> > >> > >> > I am uploading a 74 + MB tab delimited text file that I am then reading >> > and inserting the values into a database. The problem is it always >> > times out, or just takes too long (did not finish over night!!!!) to >> > read the file. It is uploaded fine. >> > >> > Any suggestions about how I may approach this to make it work with such >> > large files? >> > >> > I looked at a bit of Java code someone had posted here but it went no >> > quicker really. >> > >> > I was thinking of maybe chopping the file into slices and then >> > processing but > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248037 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
