extracting data from a huge text file
I'm trying to retrieve data and put it into a database from a text file. The text file is over 100MB with (I'm guessing) around 260,000 records, each record contains 85 fields. I tried to create a conditional loop at each line break, which works fine if I am working on a smaller version of the file, but chokes on the 100MB monster. Is there a more efficient way to deal with this other than looping through it or is there a way to break this file up into smaller chunks and deal with it piece by piece? I tried using cfhttp and that didn't work. I think the format of the data is not suitable for that. Below is a sample of two records. miamimls","FL","M805531","1003958","MAJESTIC PROPERTIES INC","3055341999","",",","","","","","","M","","","A","459000","","FL","","","","","","","","Miami Beach","Y22391","33139","","42","DADE","12025","","THE SUNDANCE","0","","","","","1612","2","2","2","","","","","","","","","","","","","","","THE SUNDANCE","00-42-00-000-0006","0","0","0","","0","","","","","","","","","","","","","","","","","", "","T16" "miamimls","FL","F425895","620406","P C REALTY","9545818853","",",","","","","","","M","","","A","36000","","FL","","","","","","","","Sunri se","Y23056","33322","","3850","BROWARD","12011","","SUNRISE LAKES","80","","","","","","2","2","2","","","","","","","","","","","","","","","SUNRISE LAKES 60 CONDO UNIT 302","494129AA0260","666","1","29","","","","","","","","","","","","","","","","","","","","","C05, E05,J02,J07,M01,M05,N02,N13,Q08,T11,U01,W30,X35" any suggestions would be very much appreciated. Chris __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: extracting data from a huge text file
what database? - Original Message - From: "Chris Giminez" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, January 24, 2002 5:09 PM Subject: extracting data from a huge text file > I'm trying to retrieve data and put it into a database from a text file. > The text file is over 100MB with (I'm guessing) around 260,000 records, each record contains 85 > fields. > > I tried to create a conditional loop at each line break, which works fine if I am working on a > smaller version of the file, but chokes on the 100MB monster. Is there a more efficient way to deal > with this other than looping through it or is there a way to break this file up into smaller chunks > and deal with it piece by piece? > > I tried using cfhttp and that didn't work. I think the format of the data is not suitable for that. > Below is a sample of two records. > > miamimls","FL","M805531","1003958","MAJESTIC PROPERTIES > INC","3055341999","",",","","","","","","M","","","A","459000","","FL","","" ,"","","","","","Miami > Beach","Y22391","33139","","42","DADE","12025","","THE > SUNDANCE","0","","","","","1612","2","2","2","","","","","","","","","",""," ","","","","THE > SUNDANCE","00-42-00-000-0006","0","0","0","","0","","","","","","","","","", "","","","","","","","", > "","T16" > > > "miamimls","FL","F425895","620406","P C > REALTY","9545818853","",",","","","","","","M","","","A","36000","","FL","", "","","","","","","Sunri > se","Y23056","33322","","3850","BROWARD","12011","","SUNRISE > LAKES","80","","","","","","2","2","2","","","","","","","","","","","",""," ","","SUNRISE LAKES 60 > CONDO UNIT > 302","494129AA0260","666","1","29","","","","","","","","","","","","","","" ,"","","","","","","C05, > E05,J02,J07,M01,M05,N02,N13,Q08,T11,U01,W30,X35" > > > any suggestions would be very much appreciated. > > Chris > __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: extracting data from a huge text file
Forget about using CF to read a file that big unless you've got a lot of memory in the server. If CF is your only tool for actually getting the data into your database, you might use something like Perl to first break the file up into many smaller files, then run those through CF. If you're a bit more proficient at Perl, just use Perl to read the file and insert the data in the db and be done with it. Jim - Original Message - From: "Chris Giminez" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, January 24, 2002 5:09 PM Subject: extracting data from a huge text file > I'm trying to retrieve data and put it into a database from a text file. > The text file is over 100MB with (I'm guessing) around 260,000 records, each record contains 85 > fields. > > I tried to create a conditional loop at each line break, which works fine if I am working on a > smaller version of the file, but chokes on the 100MB monster. Is there a more efficient way to deal > with this other than looping through it or is there a way to break this file up into smaller chunks > and deal with it piece by piece? __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: extracting data from a huge text file
You can use Perl or Visual Basic and write an EXE to handle it. I had a huge file as such and it would contain about 500k records. It was a historical file containing about 5 years of data. If it is a SQL database, look at using the DTS package If per say an Access database, either Perl or VB would be suitable solution. - Original Message - From: "Jim McAtee" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, January 24, 2002 7:19 PM Subject: Re: extracting data from a huge text file > Forget about using CF to read a file that big unless you've got a lot of > memory in the server. If CF is your only tool for actually getting the data > into your database, you might use something like Perl to first break the > file up into many smaller files, then run those through CF. If you're a bit > more proficient at Perl, just use Perl to read the file and insert the data > in the db and be done with it. > > Jim > > > - Original Message - > From: "Chris Giminez" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Thursday, January 24, 2002 5:09 PM > Subject: extracting data from a huge text file > > > > I'm trying to retrieve data and put it into a database from a text file. > > The text file is over 100MB with (I'm guessing) around 260,000 records, > each record contains 85 > > fields. > > > > I tried to create a conditional loop at each line break, which works fine > if I am working on a > > smaller version of the file, but chokes on the 100MB monster. Is there a > more efficient way to deal > > with this other than looping through it or is there a way to break this > file up into smaller chunks > > and deal with it piece by piece? > > __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: extracting data from a huge text file
Chris, You're cfhttp-ing to the file so it might not be local to you, but if it is and if you're on CF5 try to make a dynamic datasource connection to the .txt file and query it that way. I don't know the syntax for a .txt cfquery connectstring attribute off the top of my head - sorry, t ** Tyler M. Fitch Certified Advanced ColdFusion 5 Developer http://isitedesign.com ** -Original Message- From: Chris Giminez [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 24, 2002 4:10 PM To: CF-Talk Subject: extracting data from a huge text file I'm trying to retrieve data and put it into a database from a text file. The text file is over 100MB with (I'm guessing) around 260,000 records, each record contains 85 fields. I tried to create a conditional loop at each line break, which works fine if I am working on a smaller version of the file, but chokes on the 100MB monster. Is there a more efficient way to deal with this other than looping through it or is there a way to break this file up into smaller chunks and deal with it piece by piece? I tried using cfhttp and that didn't work. I think the format of the data is not suitable for that. Below is a sample of two records. miamimls","FL","M805531","1003958","MAJESTIC PROPERTIES INC","3055341999","",",","","","","","","M","","","A","459000","","FL"," ","","","","","","","Miami Beach","Y22391","33139","","42","DADE","12025","","THE SUNDANCE","0","","","","","1612","2","2","2","","","","","","","","","", "","","","","","THE SUNDANCE","00-42-00-000-0006","0","0","0","","0","","","","","","","","" ,"","","","","","","","","", "","T16" "miamimls","FL","F425895","620406","P C REALTY","9545818853","",",","","","","","","M","","","A","36000","","FL" ,"","","","","","","","Sunri se","Y23056","33322","","3850","BROWARD","12011","","SUNRISE LAKES","80","","","","","","2","2","2","","","","","","","","","","","", "","","","SUNRISE LAKES 60 CONDO UNIT 302","494129AA0260","666","1","29","","","","","","","","","","","",""," ","","","","","","","","C05, E05,J02,J07,M01,M05,N02,N13,Q08,T11,U01,W30,X35" any suggestions would be very much appreciated. Chris __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: extracting data from a huge text file
On Thu, 24 Jan 2002 16:09:54 -0800, "Chris Giminez" <[EMAIL PROTECTED]> wrote: >I'm trying to retrieve data and put it into a database from a text file. >The text file is over 100MB with (I'm guessing) around 260,000 records, each record >contains 85 >fields. > >I tried to create a conditional loop at each line break, which works fine if I am >working on a >smaller version of the file, but chokes on the 100MB monster. Is there a more >efficient way to deal >with this other than looping through it or is there a way to break this file up into >smaller chunks >and deal with it piece by piece? Been a while since I used it but I wrote a cfx called... hmm.. I don't recall what it was called /-) ... anyway, the cfx was to read in log files one line at a time. Which is what it does, one carriage-deliminated line per call, not the entire file all at once. It's somewhere at http://www.intrafoundation.com/freeware.html. --min __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: extracting data from a huge text file
Yes, an MS SQL DTS package, or even the import wizard will get that data in a blink of an eye. Access will work as well, slower albeit. -Original Message- From: Randy Adkins [mailto:[EMAIL PROTECTED]] You can use Perl or Visual Basic and write an EXE to handle it. I had a huge file as such and it would contain about 500k records. It was a historical file containing about 5 years of data. If it is a SQL database, look at using the DTS package If per say an Access database, either Perl or VB would be suitable solution. - Original Message - From: "Jim McAtee" <[EMAIL PROTECTED]> > Forget about using CF to read a file that big unless you've got a lot > of memory in the server. If CF is your only tool for actually getting > the data > into your database, you might use something like Perl to first break > the file up into many smaller files, then run those through CF. If > you're a bit > more proficient at Perl, just use Perl to read the file and insert the data > in the db and be done with it. > > Jim > > > - Original Message - > From: "Chris Giminez" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Thursday, January 24, 2002 5:09 PM > Subject: extracting data from a huge text file > > > > I'm trying to retrieve data and put it into a database from a text > > file. The text file is over 100MB with (I'm guessing) around > > 260,000 records, > each record contains 85 > > fields. > > > > I tried to create a conditional loop at each line break, which works fine > if I am working on a > > smaller version of the file, but chokes on the 100MB monster. Is > > there a > more efficient way to deal > > with this other than looping through it or is there a way to break > > this > file up into smaller chunks > > and deal with it piece by piece? > > __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: extracting data from a huge text file
How does it accomplish this when you want to loop over the contents of a large file? Does it keep the file open, or when you call it to read the 250,000th line, has it opened and closed and read through the file 249,999 times already? Jim - Original Message - From: "Lewis Sellers" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, January 24, 2002 5:54 PM Subject: Re: extracting data from a huge text file > On Thu, 24 Jan 2002 16:09:54 -0800, "Chris Giminez" > <[EMAIL PROTECTED]> wrote: > > >I'm trying to retrieve data and put it into a database from a text file. > >The text file is over 100MB with (I'm guessing) around 260,000 records, each record contains 85 > >fields. > > > >I tried to create a conditional loop at each line break, which works fine if I am working on a > >smaller version of the file, but chokes on the 100MB monster. Is there a more efficient way to deal > >with this other than looping through it or is there a way to break this file up into smaller chunks > >and deal with it piece by piece? > > > Been a while since I used it but I wrote a cfx called... hmm.. I don't > recall what it was called /-) ... anyway, the cfx was to read in log > files one line at a time. > > Which is what it does, one carriage-deliminated line per call, not the > entire file all at once. > > It's somewhere at http://www.intrafoundation.com/freeware.html. > > --min > __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: extracting data from a huge text file
It's called CFX_ReadLine and with an initial test, it looks like it's going to do the trick. thanks very much!! Chris > > >I'm trying to retrieve data and put it into a database from a text file. > >The text file is over 100MB with (I'm guessing) around 260,000 records, each >record contains 85 > >fields. > > > >I tried to create a conditional loop at each line break, which works fine if I am >working on a > >smaller version of the file, but chokes on the 100MB monster. Is there a more >efficient way to deal > >with this other than looping through it or is there a way to break this file up >into smaller chunks > >and deal with it piece by piece? > > > Been a while since I used it but I wrote a cfx called... hmm.. I don't > recall what it was called /-) ... anyway, the cfx was to read in log > files one line at a time. > > Which is what it does, one carriage-deliminated line per call, not the > entire file all at once. > > It's somewhere at http://www.intrafoundation.com/freeware.html. > > --min > __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: extracting data from a huge text file
On Thu, 24 Jan 2002 18:10:10 -0700, "Jim McAtee" <[EMAIL PROTECTED]> wrote: >How does it accomplish this when you want to loop over the contents of a >large file? Does it keep the file open, or when you call it to read the >250,000th line, has it opened and closed and read through the file 249,999 >times already? > >Jim Nothing complicated involved. Every time you call the tag it opens the file and reads in the next carriage-deliminated string. Then closes the file. *You* pass to it the position in the file you want to read the next line from. "0" is the beginning of a file. Of course to make your life easy, it returns the position in the file the next line starts at. :) Just use the number in the next call while looping... or save it somewhere for later. With all that file opening, I'm not saying it's fast -- but it's simple and gets the job done. As for your example, if you happen to already know (or can guess -- say all lines are a fixed width) you can go directly to it. Otherwie, yes, you would have to parse through all the previous lines to know what the position of the line is in the file. __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: extracting data from a huge text file
Actually, that's pretty well thought-out by returning the position where you ended your last read. Lots of file open/closes, but at least you don't have to scan the file each time to find your line. Is it possible to create a CFX that leaves the file open and passes back a file handle, allowing subsequent tag calls to use the file handle as a parameter? Needless to say, this would be dangerous, but I assume that the proper use of CF error trapping would catch fatal errors, allowing you to close the file and preventing you from leaving files open all over the place. Jim - Original Message - From: "Lewis Sellers" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, January 24, 2002 7:59 PM Subject: Re: extracting data from a huge text file > On Thu, 24 Jan 2002 18:10:10 -0700, "Jim McAtee" > <[EMAIL PROTECTED]> wrote: > > >How does it accomplish this when you want to loop over the contents of a > >large file? Does it keep the file open, or when you call it to read the > >250,000th line, has it opened and closed and read through the file 249,999 > >times already? > > > >Jim > > Nothing complicated involved. Every time you call the tag it opens the > file and reads in the next carriage-deliminated string. Then closes > the file. > > *You* pass to it the position in the file you want to read the next > line from. "0" is the beginning of a file. > > Of course to make your life easy, it returns the position in the file > the next line starts at. :) Just use the number in the next call while > looping... or save it somewhere for later. > > With all that file opening, I'm not saying it's fast -- but it's > simple and gets the job done. > > As for your example, if you happen to already know (or can guess -- > say all lines are a fixed width) you can go directly to it. Otherwie, > yes, you would have to parse through all the previous lines to know > what the position of the line is in the file. __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: extracting data from a huge text file
> Been a while since I used it but I wrote a cfx called... hmm.. I don't > recall what it was called /-) ... anyway, the cfx was to read in log > files one line at a time. > > Which is what it does, one carriage-deliminated line per call, not the > entire file all at once. > > It's somewhere at http://www.intrafoundation.com/freeware.html. okay, min, put DOWN the cough syrup, and step away from the keyboard. You know, if you didn't write such goshdarned excellent code, people would think you were nuts. ;) Ron Hornbaker President/CTO . . . . . . . . . . . . http://humankindsystems.com . . . . . . . . . . . . w e c o d e. w e c a r e. . email tracking and routing made easy: http://answertrack.com __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: extracting data from a huge text file
This should be IDEAL for you. http://www.macromedia.com/v1/handlers/index.cfm?ID=22277&method=full DRE -Original Message- From: Jim McAtee [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 24, 2002 6:10 PM To: CF-Talk Subject: Re: extracting data from a huge text file How does it accomplish this when you want to loop over the contents of a large file? Does it keep the file open, or when you call it to read the 250,000th line, has it opened and closed and read through the file 249,999 times already? Jim - Original Message - From: "Lewis Sellers" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, January 24, 2002 5:54 PM Subject: Re: extracting data from a huge text file > On Thu, 24 Jan 2002 16:09:54 -0800, "Chris Giminez" > <[EMAIL PROTECTED]> wrote: > > >I'm trying to retrieve data and put it into a database from a text file. > >The text file is over 100MB with (I'm guessing) around 260,000 records, each record contains 85 > >fields. > > > >I tried to create a conditional loop at each line break, which works fine if I am working on a > >smaller version of the file, but chokes on the 100MB monster. Is there a more efficient way to deal > >with this other than looping through it or is there a way to break this file up into smaller chunks > >and deal with it piece by piece? > > > Been a while since I used it but I wrote a cfx called... hmm.. I don't > recall what it was called /-) ... anyway, the cfx was to read in log > files one line at a time. > > Which is what it does, one carriage-deliminated line per call, not the > entire file all at once. > > It's somewhere at http://www.intrafoundation.com/freeware.html. > > --min > __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: extracting data from a huge text file
Another option.. Why not open it as an ODBC data source, and let the ODBC drivers worry about the size of the file? We recently did something like this with VB for a text file, only a few lines/records though. But it should still work well with larger files. -Original Message- From: Chris Giminez [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 24, 2002 6:56 PM To: CF-Talk Subject: Re: extracting data from a huge text file It's called CFX_ReadLine and with an initial test, it looks like it's going to do the trick. thanks very much!! Chris > > >I'm trying to retrieve data and put it into a database from a text file. > >The text file is over 100MB with (I'm guessing) around 260,000 records, each record contains 85 > >fields. > > > >I tried to create a conditional loop at each line break, which works fine if I am working on a > >smaller version of the file, but chokes on the 100MB monster. Is there a more efficient way to deal > >with this other than looping through it or is there a way to break this file up into smaller chunks > >and deal with it piece by piece? > > > Been a while since I used it but I wrote a cfx called... hmm.. I don't > recall what it was called /-) ... anyway, the cfx was to read in log > files one line at a time. > > Which is what it does, one carriage-deliminated line per call, not the > entire file all at once. > > It's somewhere at http://www.intrafoundation.com/freeware.html. > > --min > __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: extracting data from a huge text file
On Thu, 24 Jan 2002 23:46:14 -0600, "Ron Hornbaker" <[EMAIL PROTECTED]> wrote: >okay, min, put DOWN the cough syrup, and step away from the keyboard. You >know, if you didn't write such goshdarned excellent code, people would >think you were nuts. ;) There is a bee on my keyboard. Ekkk. Oh wait. I am a glass of orange juice. Being what it seeks, I drown the bee in heaven. --min /-> (Min thinks he must practice the fine lost art of "sleep" more often.) >Ron Hornbaker >President/CTO __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: extracting data from a huge text file
the format of the text file is not very consistent. It's a comma delimited list, but the last field is itself a comma delimited list and fields 8 and 9 contain one double quote instead of a pair like every other field. It's very strange and I can't get the writers of this text file to explain their logic or change it so far. I don't think an automated solution will work for these reason, so I am parsing each line piece by piece and saving as individual variables. > Another option.. Why not open it as an ODBC data source, and let the > ODBC > drivers worry about the size of the file? We recently did something > like > this with VB for a text file, only a few lines/records though. But it > should still work well with larger files. > > > -Original Message- > From: Chris Giminez [mailto:[EMAIL PROTECTED]] > Sent: Thursday, January 24, 2002 6:56 PM > To: CF-Talk > Subject: Re: extracting data from a huge text file > > > It's called CFX_ReadLine and with an initial test, it looks like it's > going > to do the trick. > thanks very much!! > > Chris > > > > > > > > >I'm trying to retrieve data and put it into a database from a text > file. > > >The text file is over 100MB with (I'm guessing) around 260,000 > records, > each record contains 85 > > >fields. > > > > > >I tried to create a conditional loop at each line break, which works > fine > if I am working on a > > >smaller version of the file, but chokes on the 100MB monster. Is > there a > more efficient way to > deal > > >with this other than looping through it or is there a way to break > this > file up into smaller > chunks > > >and deal with it piece by piece? > > > > > > Been a while since I used it but I wrote a cfx called... hmm.. I > don't > > recall what it was called /-) ... anyway, the cfx was to read in log > > files one line at a time. > > > > Which is what it does, one carriage-deliminated line per call, not > the > > entire file all at once. > > > > It's somewhere at http://www.intrafoundation.com/freeware.html. > > > > --min > > > > __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: extracting data from a huge text file
You might still use the Merant ODBC text driver to do the work of retrieving the lines, though. Just treat it as a single large field and continue to parse it as you're doing. Jim - Original Message - From: "Chris Giminez" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, January 25, 2002 10:42 AM Subject: Re: extracting data from a huge text file > the format of the text file is not very consistent. It's a comma delimited list, but the last field > is itself a comma delimited list and fields 8 and 9 contain one double quote instead of a pair like > every other field. It's very strange and I can't get the writers of this text file to explain their > logic or change it so far. > I don't think an automated solution will work for these reason, so I am parsing each line piece by > piece and saving as individual variables. > > > > Another option.. Why not open it as an ODBC data source, and let the > > ODBC > > drivers worry about the size of the file? We recently did something > > like > > this with VB for a text file, only a few lines/records though. But it > > should still work well with larger files. __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: extracting data from a huge text file
It does look ideal. Unfortunately, I'm not sure how to plug in and compile the Java code and don't know that I can take the time to learn just now. Anyone have the class files for this that can be plugged in ready to roll? Chris > This should be IDEAL for you. > http://www.macromedia.com/v1/handlers/index.cfm?ID=22277&method=full > > DRE > > -Original Message- > From: Jim McAtee [mailto:[EMAIL PROTECTED]] > Sent: Thursday, January 24, 2002 6:10 PM > To: CF-Talk > Subject: Re: extracting data from a huge text file > > > How does it accomplish this when you want to loop over the contents of > a > large file? Does it keep the file open, or when you call it to read > the > 250,000th line, has it opened and closed and read through the file > 249,999 > times already? > > Jim > > > - Original Message - > From: "Lewis Sellers" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Thursday, January 24, 2002 5:54 PM > Subject: Re: extracting data from a huge text file > > > > On Thu, 24 Jan 2002 16:09:54 -0800, "Chris Giminez" > > <[EMAIL PROTECTED]> wrote: > > > > >I'm trying to retrieve data and put it into a database from a text > file. > > >The text file is over 100MB with (I'm guessing) around 260,000 > records, > each record contains 85 > > >fields. > > > > > >I tried to create a conditional loop at each line break, which works > fine > if I am working on a > > >smaller version of the file, but chokes on the 100MB monster. Is > there a > more efficient way to deal > > >with this other than looping through it or is there a way to break > this > file up into smaller chunks > > >and deal with it piece by piece? > > > > > > Been a while since I used it but I wrote a cfx called... hmm.. I > don't > > recall what it was called /-) ... anyway, the cfx was to read in log > > files one line at a time. > > > > Which is what it does, one carriage-deliminated line per call, not > the > > entire file all at once. > > > > It's somewhere at http://www.intrafoundation.com/freeware.html. > > > > --min > > > > __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: extracting data from a huge text file
ok, worth a try, I set it up in CF Admin, it said it's "ok" but when I try to read in RDS, it requires a username/password. I didn't set one up or even have the ability to do so in admin that I saw. What am I missing? > You might still use the Merant ODBC text driver to do the work of retrieving > the lines, though. Just treat it as a single large field and continue to > parse it as you're doing. > > Jim > > > - Original Message - > From: "Chris Giminez" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Friday, January 25, 2002 10:42 AM > Subject: Re: extracting data from a huge text file > > > > the format of the text file is not very consistent. It's a comma delimited > list, but the last field > > is itself a comma delimited list and fields 8 and 9 contain one double > quote instead of a pair like > > every other field. It's very strange and I can't get the writers of this > text file to explain their > > logic or change it so far. > > I don't think an automated solution will work for these reason, so I am > parsing each line piece by > > piece and saving as individual variables. > > > > > > > Another option.. Why not open it as an ODBC data source, and let the > > > ODBC > > > drivers worry about the size of the file? We recently did something > > > like > > > this with VB for a text file, only a few lines/records though. But it > > > should still work well with larger files. > > __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: extracting data from a huge text file
Hi, I have had it complied but have not try it out yet. If you want please contact me off list, because I cannot attach any file here. Regards, Sima -Original Message- From: Chris Giminez [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 12:54 PM To: CF-Talk Subject: Re: extracting data from a huge text file It does look ideal. Unfortunately, I'm not sure how to plug in and compile the Java code and don't know that I can take the time to learn just now. Anyone have the class files for this that can be plugged in ready to roll? Chris > This should be IDEAL for you. > http://www.macromedia.com/v1/handlers/index.cfm?ID=22277&method=full > > DRE > > -Original Message- > From: Jim McAtee [mailto:[EMAIL PROTECTED]] > Sent: Thursday, January 24, 2002 6:10 PM > To: CF-Talk > Subject: Re: extracting data from a huge text file > > > How does it accomplish this when you want to loop over the contents of > a > large file? Does it keep the file open, or when you call it to read > the > 250,000th line, has it opened and closed and read through the file > 249,999 > times already? > > Jim > > > - Original Message - > From: "Lewis Sellers" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Thursday, January 24, 2002 5:54 PM > Subject: Re: extracting data from a huge text file > > > > On Thu, 24 Jan 2002 16:09:54 -0800, "Chris Giminez" > > <[EMAIL PROTECTED]> wrote: > > > > >I'm trying to retrieve data and put it into a database from a text > file. > > >The text file is over 100MB with (I'm guessing) around 260,000 > records, > each record contains 85 > > >fields. > > > > > >I tried to create a conditional loop at each line break, which works > fine > if I am working on a > > >smaller version of the file, but chokes on the 100MB monster. Is > there a > more efficient way to deal > > >with this other than looping through it or is there a way to break > this > file up into smaller chunks > > >and deal with it piece by piece? > > > > > > Been a while since I used it but I wrote a cfx called... hmm.. I > don't > > recall what it was called /-) ... anyway, the cfx was to read in log > > files one line at a time. > > > > Which is what it does, one carriage-deliminated line per call, not > the > > entire file all at once. > > > > It's somewhere at http://www.intrafoundation.com/freeware.html. > > > > --min > > > > __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: extracting data from a huge text file
Chris, Sima, Can you keep me posted on what you figure out with that java io buisness. I'll have to do something similar soon myself. And it will probably give a bit of a headsup on NEO. Thanks. DRE [EMAIL PROTECTED] -Original Message- From: Sima Lee [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 11:24 AM To: CF-Talk Subject: RE: extracting data from a huge text file Hi, I have had it complied but have not try it out yet. If you want please contact me off list, because I cannot attach any file here. Regards, Sima -Original Message- From: Chris Giminez [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 12:54 PM To: CF-Talk Subject: Re: extracting data from a huge text file It does look ideal. Unfortunately, I'm not sure how to plug in and compile the Java code and don't know that I can take the time to learn just now. Anyone have the class files for this that can be plugged in ready to roll? Chris > This should be IDEAL for you. > http://www.macromedia.com/v1/handlers/index.cfm?ID=22277&method=full > > DRE > > -Original Message- > From: Jim McAtee [mailto:[EMAIL PROTECTED]] > Sent: Thursday, January 24, 2002 6:10 PM > To: CF-Talk > Subject: Re: extracting data from a huge text file > > > How does it accomplish this when you want to loop over the contents of > a > large file? Does it keep the file open, or when you call it to read > the > 250,000th line, has it opened and closed and read through the file > 249,999 > times already? > > Jim > > > - Original Message - > From: "Lewis Sellers" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Thursday, January 24, 2002 5:54 PM > Subject: Re: extracting data from a huge text file > > > > On Thu, 24 Jan 2002 16:09:54 -0800, "Chris Giminez" > > <[EMAIL PROTECTED]> wrote: > > > > >I'm trying to retrieve data and put it into a database from a text > file. > > >The text file is over 100MB with (I'm guessing) around 260,000 > records, > each record contains 85 > > >fields. > > > > > >I tried to create a conditional loop at each line break, which works > fine > if I am working on a > > >smaller version of the file, but chokes on the 100MB monster. Is > there a > more efficient way to deal > > >with this other than looping through it or is there a way to break > this > file up into smaller chunks > > >and deal with it piece by piece? > > > > > > Been a while since I used it but I wrote a cfx called... hmm.. I > don't > > recall what it was called /-) ... anyway, the cfx was to read in log > > files one line at a time. > > > > Which is what it does, one carriage-deliminated line per call, not > the > > entire file all at once. > > > > It's somewhere at http://www.intrafoundation.com/freeware.html. > > > > --min > > > > __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: extracting data from a huge text file (my update on it)
Hi Chris and Andre, Now I made this java class code works on both CF4.5 and CF5.0. On both version the class file is OK, nothing needs to be changed. But changes should be made on the JavaFileTest.cfm , the sample test file. For it to work on CF4.5 just take out the tag in the javafileTest.cfm (Does it come with CF5.0?). But for it to work on CF5.0 we need to add two extra lines. In the original javafileTest.cfm , a FileReader to be initiated by calling the constructor with a String object: the file name. But CF5.0 does not accept it. Instead I create a file object first and pass the file object into FileReader constructor instead of the String object . Then it works just fine. So after the first line I add: Then after the this line: you pass the file object to the FileReader constructor: Since the test file is very short I will just copy and past the whole file here. JavaFileTest.cfm starts here: Untitled #replace(curLine,"<","<","ALL")# JavaFileTest.cfm ends here. Hope this update helps Regards, Sima -Original Message- From: Andre Turrettini [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 3:12 PM To: CF-Talk Subject: RE: extracting data from a huge text file Chris, Sima, Can you keep me posted on what you figure out with that java io buisness. I'll have to do something similar soon myself. And it will probably give a bit of a headsup on NEO. Thanks. DRE [EMAIL PROTECTED] -Original Message- From: Sima Lee [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 11:24 AM To: CF-Talk Subject: RE: extracting data from a huge text file Hi, I have had it complied but have not try it out yet. If you want please contact me off list, because I cannot attach any file here. Regards, Sima -Original Message- From: Chris Giminez [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 12:54 PM To: CF-Talk Subject: Re: extracting data from a huge text file It does look ideal. Unfortunately, I'm not sure how to plug in and compile the Java code and don't know that I can take the time to learn just now. Anyone have the class files for this that can be plugged in ready to roll? Chris > This should be IDEAL for you. > http://www.macromedia.com/v1/handlers/index.cfm?ID=22277&method=full > > DRE > > -Original Message- > From: Jim McAtee [mailto:[EMAIL PROTECTED]] > Sent: Thursday, January 24, 2002 6:10 PM > To: CF-Talk > Subject: Re: extracting data from a huge text file > > > How does it accomplish this when you want to loop over the contents of > a > large file? Does it keep the file open, or when you call it to read > the > 250,000th line, has it opened and closed and read through the file > 249,999 > times already? > > Jim > > > - Original Message - > From: "Lewis Sellers" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Thursday, January 24, 2002 5:54 PM > Subject: Re: extracting data from a huge text file > > > > On Thu, 24 Jan 2002 16:09:54 -0800, "Chris Giminez" > > <[EMAIL PROTECTED]> wrote: > > > > >I'm trying to retrieve data and put it into a database from a text > file. > > >The text file is over 100MB with (I'm guessing) around 260,000 > records, > each record contains 85 > > >fields. > > > > > >I tried to create a conditional loop at each line break, which works > fine > if I am working on a > > >smaller version of the file, but chokes on the 100MB monster. Is > there a > more efficient way to deal > > >with this other than looping through it or is there a way to break > this > file up into smaller chunks > > >and deal with it piece by piece? > > > > > > Been a while since I used it but I wrote a cfx called... hmm.. I > don't > > recall what it was called /-) ... anyway, the cfx was to read in log > > files one line at a time. > > > > Which is what it does, one carriage-deliminated line per call, not > the > > entire file all at once. > > > > It's somewhere at http://www.intrafoundation.com/freeware.html. > > > > --min > > > > __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: extracting data from a huge text file (my update on it)
Thanks Sima, Thats some pretty cool functionality. Thanks again for debugging it. I'll look forward to using it. DRE -Original Message- From: Sima Lee [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 9:48 AM To: CF-Talk Subject: RE: extracting data from a huge text file (my update on it) Hi Chris and Andre, Now I made this java class code works on both CF4.5 and CF5.0. On both version the class file is OK, nothing needs to be changed. But changes should be made on the JavaFileTest.cfm , the sample test file. For it to work on CF4.5 just take out the tag in the javafileTest.cfm (Does it come with CF5.0?). But for it to work on CF5.0 we need to add two extra lines. In the original javafileTest.cfm , a FileReader to be initiated by calling the constructor with a String object: the file name. But CF5.0 does not accept it. Instead I create a file object first and pass the file object into FileReader constructor instead of the String object . Then it works just fine. So after the first line I add: Then after the this line: you pass the file object to the FileReader constructor: Since the test file is very short I will just copy and past the whole file here. JavaFileTest.cfm starts here: Untitled #replace(curLine,"<","<","ALL")# JavaFileTest.cfm ends here. Hope this update helps Regards, Sima -Original Message- From: Andre Turrettini [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 3:12 PM To: CF-Talk Subject: RE: extracting data from a huge text file Chris, Sima, Can you keep me posted on what you figure out with that java io buisness. I'll have to do something similar soon myself. And it will probably give a bit of a headsup on NEO. Thanks. DRE [EMAIL PROTECTED] -Original Message- From: Sima Lee [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 11:24 AM To: CF-Talk Subject: RE: extracting data from a huge text file Hi, I have had it complied but have not try it out yet. If you want please contact me off list, because I cannot attach any file here. Regards, Sima -Original Message- From: Chris Giminez [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 12:54 PM To: CF-Talk Subject: Re: extracting data from a huge text file It does look ideal. Unfortunately, I'm not sure how to plug in and compile the Java code and don't know that I can take the time to learn just now. Anyone have the class files for this that can be plugged in ready to roll? Chris > This should be IDEAL for you. > http://www.macromedia.com/v1/handlers/index.cfm?ID=22277&method=full > > DRE > > -Original Message- > From: Jim McAtee [mailto:[EMAIL PROTECTED]] > Sent: Thursday, January 24, 2002 6:10 PM > To: CF-Talk > Subject: Re: extracting data from a huge text file > > > How does it accomplish this when you want to loop over the contents of > a > large file? Does it keep the file open, or when you call it to read > the > 250,000th line, has it opened and closed and read through the file > 249,999 > times already? > > Jim > > > - Original Message - > From: "Lewis Sellers" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Thursday, January 24, 2002 5:54 PM > Subject: Re: extracting data from a huge text file > > > > On Thu, 24 Jan 2002 16:09:54 -0800, "Chris Giminez" > > <[EMAIL PROTECTED]> wrote: > > > > >I'm trying to retrieve data and put it into a database from a text > file. > > >The text file is over 100MB with (I'm guessing) around 260,000 > records, > each record contains 85 > > >fields. > > > > > >I tried to create a conditional loop at each line break, which works > fine > if I am working on a > > >smaller version of the file, but chokes on the 100MB monster. Is > there a > more efficient way to deal > > >with this other than looping through it or is there a way to break > this > file up into smaller chunks > > >and deal with it piece by piece? > > > > > > Been a while since I used it but I wrote a cfx called... hmm.. I > don't > > recall what it was called /-) ... anyway, the cfx was to read in log > > files one line at a time. > > > > Which is what it does, one carriage-deliminated line per call, not > the > > entire file all at once. > > > > It's somewhere at http://www.intrafoundation.com/freeware.html. > > > > --min > > > > __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
