Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003: VBE Won't let me break into running code
Hello all,
I'm trying to manipulate huge text files (1 - 4 GB each) with the TextStream object. Each file contains 6 months of data...I'm trying to create 6 smaller files with one month of data each. The first step in the process is reading through the whole file one line at a time so I can get a total number of lines (any advice to do this faster/ alternate way would be greatly appreciated...I looked, didn't see a "line count" property of the TextStream or File object so I could avoid reading whole file). This part of the first file (smallest file) took 5 - 10 mins to run. I use the total line count for progress reporting in Excel's progress bar for the next part...building the 6 monthly files from the main file. At some point when the code is running (I don't know what point it is...might change each time code is run), the VBE will no longer let me break into running code ([Ctrl] + [Pause/Break]) for debuging...it just stops running code...it no longer offers me the option to stop or debug. The time before last, I had a breakpoint set just after getting total line count and a "Break when value is true" watch set just a few lines before finishing the first monthly file (roughly 1/6th of the way through the 2nd full read through of the main file) so I could debug the code closing the first monthly file, creating the 2nd monthly file, then continuing to write data. Breakpoint broke into code without problems (VBE not yet tripped up), but "Break..." watch was ignored. This last time running code, it made it through the Total Line Count run through, but threw an error (Data Type mismatch...if I remember correctly). Instead of giving me the option to stop or debug...it just stopped the code...I really need to be able to debug this. XL/VBE goes into "Not Responding" mode (but code is still running) usually when Windows Focus changes to another program. Maybe that is when the VBE trips up and won't let a break-in. Anyone else have issues with this? Is there anything that can be done to fix this (other than staring at XL the whole time code is running hoping a reminder won't pop up to take focus away from XL...hoping XL won't go into "Not Responding" mode)? Is XL 2007's VBE more stable that it will be able to handle this better (I do have XL2007 installed on this machine)? Thanks for any help anyone can provide, CTB |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003: VBE Won't let me break into running code
I've never worked with text files as large as GBs but I know that MB
files process fairly quickly using arrays and normal VB I/O (as opposed to FileSystemObject). Essentially, I read the entire file into a Variant variable in one shot and then use the Split() function to dump it into another Variant variable, resulting in a dynamic array. If the file contains data records AND the first line contains the data fieldnames, the UBound of the array is the record count. If the first line doesn't contain fieldnames then the line count is the UBound+1. To parse the data into separate months would be no problem by looping the array checking which month each element belongs to and dumping that into a String variable. If you set up a String variable for each month then you should only have to loop the array once, and use a Select Case construct to build the separate month strings. Once you've parsed the file into appropriate month strings you can write them back to individual files in one shot (each). Keep in mind that handling files of this size will burden resources some, but if your machine is fit for such tasks it should be no problem to do. You can run a progress bar in the array loop, OR report the progress in the StatusBar for each line as it gets processed. Summary: 1. Read source file into array in one shot. 2. Parse data into separate month strings using one loop. 3. Write month strings in one shot to separate files. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003: VBE Won't let me break into running code
It sounds as if you are running out of memory.
Do not use "ReadAll". It will do that. Use: "Application.EnableCancelKey = Interrupt" and use DoEvents inside of any loops. Your posted code did not show up in my newsreader. The following function will take < 10 minutes to return the number of lines in a text file... "--- Function NumberOfLines(sFilePath As String) As Long Dim fs As Object Dim f As Object Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.OpenTextFile(sFilePath, 8) ' 8 - Appending NumberOfLines = f.Line - 1 f.Close Set f = Nothing Set fs = Nothing End Function '--- Jim Cone Portland, Oregon USA . http://www.mediafire.com/PrimitiveSoftware . (free and commercial excel programs) "CTB" wrote in message ... Hello all, I'm trying to manipulate huge text files (1 - 4 GB each) with the TextStream object. Each file contains 6 months of data...I'm trying to create 6 smaller files with one month of data each. The first step in the process is reading through the whole file one line at a time so I can get a total number of lines (any advice to do this faster/ alternate way would be greatly appreciated...I looked, didn't see a "line count" property of the TextStream or File object so I could avoid reading whole file). This part of the first file (smallest file) took 5 - 10 mins to run. I use the total line count for progress reporting in Excel's progress bar for the next part...building the 6 monthly files from the main file. At some point when the code is running (I don't know what point it is...might change each time code is run), the VBE will no longer let me break into running code ([Ctrl] + [Pause/Break]) for debuging...it just stops running code...it no longer offers me the option to stop or debug. The time before last, I had a breakpoint set just after getting total line count and a "Break when value is true" watch set just a few lines before finishing the first monthly file (roughly 1/6th of the way through the 2nd full read through of the main file) so I could debug the code closing the first monthly file, creating the 2nd monthly file, then continuing to write data. Breakpoint broke into code without problems (VBE not yet tripped up), but "Break..." watch was ignored. This last time running code, it made it through the Total Line Count run through, but threw an error (Data Type mismatch...if I remember correctly). Instead of giving me the option to stop or debug...it just stopped the code...I really need to be able to debug this. XL/VBE goes into "Not Responding" mode (but code is still running) usually when Windows Focus changes to another program. Maybe that is when the VBE trips up and won't let a break-in. Anyone else have issues with this? Is there anything that can be done to fix this (other than staring at XL the whole time code is running hoping a reminder won't pop up to take focus away from XL...hoping XL won't go into "Not Responding" mode)? Is XL 2007's VBE more stable that it will be able to handle this better (I do have XL2007 installed on this machine)? Thanks for any help anyone can provide, CTB |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003: VBE Won't let me break into running code
I'd like to see the code you are using to read the lines
- shouldn't be more than a few lines of CODE (as opposed to DATA that is!) I've handled large log files, tho' not in the GB range It's vital you do not read the whole file, but just a block at a time and within that only one logical line at a time. My issue was that some data files were exUnix (Lf) and other data ex Windoze (CrLf line terminated) and VB Line Input statement needs CRLF data. My solution was to write a class (UnixDosReader) with properties of filename, size etc, and methods of fOpen, GetNextLine and fClose I'm looking at my code now (not having used it since I retired) I see I handled the file unblocking in VBA myself if it was a Unix file, reading one block of data (of whatever size I chose) at a time. What I did was to scan the 1st lump of the data at file open time to determine if it was LF or CRLF data If CRLF in block 1, then my GetNextLine method reads the file one line at at time using std VB IO code Line Input #pFileNo, dataLine If it's Unix it gets a bit more complicated, with a Private Sub inside the class: ReadaBlock(s as String, Size as Integer) 'size is size of block buffer which internally did Get #pFile,,s '(read into s String argument) You have to gwt it right at the end of the file, which doesn't fill your standard blocksize. Altogether there's 273 lines of code (of which the 1st 94 are solid comments) I could send the code - it's a bit long to post here BUT I have one little nagging worry... FileSize is typed as Long, which on my 32 bit machine only gets to 2GB. I assume you're on a 64 bit box? Just checking.. You'll have to look carefully at any such Typing matters if you want to use my code spilly "CTB" wrote in message ... Hello all, I'm trying to manipulate huge text files (1 - 4 GB each) with the TextStream object. Each file contains 6 months of data...I'm trying to create 6 smaller files with one month of data each. The first step in the process is reading through the whole file one line at a time so I can get a total number of lines (any advice to do this faster/ alternate way would be greatly appreciated...I looked, didn't see a "line count" property of the TextStream or File object so I could avoid reading whole file). This part of the first file (smallest file) took 5 - 10 mins to run. I use the total line count for progress reporting in Excel's progress bar for the next part...building the 6 monthly files from the main file. At some point when the code is running (I don't know what point it is...might change each time code is run), the VBE will no longer let me break into running code ([Ctrl] + [Pause/Break]) for debuging...it just stops running code...it no longer offers me the option to stop or debug. The time before last, I had a breakpoint set just after getting total line count and a "Break when value is true" watch set just a few lines before finishing the first monthly file (roughly 1/6th of the way through the 2nd full read through of the main file) so I could debug the code closing the first monthly file, creating the 2nd monthly file, then continuing to write data. Breakpoint broke into code without problems (VBE not yet tripped up), but "Break..." watch was ignored. This last time running code, it made it through the Total Line Count run through, but threw an error (Data Type mismatch...if I remember correctly). Instead of giving me the option to stop or debug...it just stopped the code...I really need to be able to debug this. XL/VBE goes into "Not Responding" mode (but code is still running) usually when Windows Focus changes to another program. Maybe that is when the VBE trips up and won't let a break-in. Anyone else have issues with this? Is there anything that can be done to fix this (other than staring at XL the whole time code is running hoping a reminder won't pop up to take focus away from XL...hoping XL won't go into "Not Responding" mode)? Is XL 2007's VBE more stable that it will be able to handle this better (I do have XL2007 installed on this machine)? Thanks for any help anyone can provide, CTB |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003: VBE Won't let me break into running code
spilly39 expressed precisely :
I'd like to see the code you are using to read the lines - shouldn't be more than a few lines of CODE (as opposed to DATA that is!) I've handled large log files, tho' not in the GB range It's vital you do not read the whole file, but just a block at a time and within that only one logical line at a time. My issue was that some data files were exUnix (Lf) and other data ex Windoze (CrLf line terminated) and VB Line Input statement needs CRLF data. My solution was to write a class (UnixDosReader) with properties of filename, size etc, and methods of fOpen, GetNextLine and fClose I'm looking at my code now (not having used it since I retired) I see I handled the file unblocking in VBA myself if it was a Unix file, reading one block of data (of whatever size I chose) at a time. What I did was to scan the 1st lump of the data at file open time to determine if it was LF or CRLF data If CRLF in block 1, then my GetNextLine method reads the file one line at at time using std VB IO code Line Input #pFileNo, dataLine If it's Unix it gets a bit more complicated, with a Private Sub inside the class: ReadaBlock(s as String, Size as Integer) 'size is size of block buffer which internally did Get #pFile,,s '(read into s String argument) You have to gwt it right at the end of the file, which doesn't fill your standard blocksize. Altogether there's 273 lines of code (of which the 1st 94 are solid comments) I could send the code - it's a bit long to post here BUT I have one little nagging worry... FileSize is typed as Long, which on my 32 bit machine only gets to 2GB. I assume you're on a 64 bit box? Just checking.. You'll have to look carefully at any such Typing matters if you want to use my code spilly Thanks for stepping in! I ran into the same issues with reading larger files in all at once. Like you I've never done GB-size files but have had to read files as large as 10MB in blocks as you describe. For me, however, this was a onetime workaround and so I never bothered to refine it beyond that. Your solution sounds exactly what this OP needs. I'd also be very interested if you're willing to share. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003: VBE Won't let me break into running code
"GS" wrote in message ... spilly39 expressed precisely : I'd like to see the code you are using to read the lines - shouldn't be more than a few lines of CODE (as opposed to DATA that is!) I've handled large log files, tho' not in the GB range It's vital you do not read the whole file, but just a block at a time and within that only one logical line at a time. My issue was that some data files were exUnix (Lf) and other data ex Windoze (CrLf line terminated) and VB Line Input statement needs CRLF data. My solution was to write a class (UnixDosReader) with properties of filename, size etc, and methods of fOpen, GetNextLine and fClose I'm looking at my code now (not having used it since I retired) I see I handled the file unblocking in VBA myself if it was a Unix file, reading one block of data (of whatever size I chose) at a time. What I did was to scan the 1st lump of the data at file open time to determine if it was LF or CRLF data If CRLF in block 1, then my GetNextLine method reads the file one line at at time using std VB IO code Line Input #pFileNo, dataLine If it's Unix it gets a bit more complicated, with a Private Sub inside the class: ReadaBlock(s as String, Size as Integer) 'size is size of block buffer which internally did Get #pFile,,s '(read into s String argument) You have to gwt it right at the end of the file, which doesn't fill your standard blocksize. Altogether there's 273 lines of code (of which the 1st 94 are solid comments) I could send the code - it's a bit long to post here BUT I have one little nagging worry... FileSize is typed as Long, which on my 32 bit machine only gets to 2GB. I assume you're on a 64 bit box? Just checking.. You'll have to look carefully at any such Typing matters if you want to use my code spilly Thanks for stepping in! I ran into the same issues with reading larger files in all at once. Like you I've never done GB-size files but have had to read files as large as 10MB in blocks as you describe. For me, however, this was a onetime workaround and so I never bothered to refine it beyond that. Your solution sounds exactly what this OP needs. I'd also be very interested if you're willing to share. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hi Garry That was quick! I see no reason not to share. In fact I'd be willing to post an attachment here if that's allowed, but I doubt it is. I'm not a big NG man, so I'm unsure of the Netiquette; I'm really only subscribed (today!) becuase of another prob I've got but when I saw this thread, it was a dead cert for my UnixDosReader class... Oh sod it! I'll try an attachment anyway - it's got the usual chances: Success or Failure! Health warning: I'm a totally self taught VBA afficionado. I'm always impressed with my code when I write it but never impressed when I read it again two years later emails from me to GS (those are MY initials!!) at somehwere dot net ain't likely to reach you. However if you email me on spilly39 buttons (my other prob is to do with ActiveX buttons), that's a valid but disposable email address, & I should get your mail. Listening out spilly |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003: VBE Won't let me break into running code
"spilly39" wrote in message
... [ ] Hi Garry That was quick! I see no reason not to share. In fact I'd be willing to post an attachment here if that's allowed, but I doubt it is. I'm not a big NG man, so I'm unsure of the Netiquette; I'm really only subscribed (today!) becuase of another prob I've got but when I saw this thread, it was a dead cert for my UnixDosReader class... Oh sod it! I'll try an attachment anyway - it's got the usual chances: Success or Failure! Health warning: I'm a totally self taught VBA afficionado. I'm always impressed with my code when I write it but never impressed when I read it again two years later Boy, that song sure sounds familiar! <grin 300 lines of code? Just copy / paste into your reply and we'll all get it just fine! (as you may know by now, the attachment didn't make it through the eternal september servers) Welcome to the NGs! [Good tutors in this classroom!] -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003: VBE Won't let me break into running code
Clif McIrvin formulated on Wednesday :
"spilly39" wrote in message ... [ ] Hi Garry That was quick! I see no reason not to share. In fact I'd be willing to post an attachment here if that's allowed, but I doubt it is. I'm not a big NG man, so I'm unsure of the Netiquette; I'm really only subscribed (today!) becuase of another prob I've got but when I saw this thread, it was a dead cert for my UnixDosReader class... Oh sod it! I'll try an attachment anyway - it's got the usual chances: Success or Failure! Health warning: I'm a totally self taught VBA afficionado. I'm always impressed with my code when I write it but never impressed when I read it again two years later Boy, that song sure sounds familiar! <grin 300 lines of code? Just copy / paste into your reply and we'll all get it just fine! (as you may know by now, the attachment didn't make it through the eternal september servers) Welcome to the NGs! [Good tutors in this classroom!] Actually, I did get the attachment. As you can see, I'm using MesNews as my reader and so attachments are supported. It shows up as a link ("File: unixdosreader.cls") in my message window. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003: VBE Won't let me break into running code
Hi Graham,
Thanks so much! I got your attachment as an attachment in the message window. This looks well thought out. The detailed comments are a blessing, though this will take some time to digest. Can't wait to do a test drive...<bg -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003: VBE Won't let me break into running code
"spilly39" wrote in message
... Oh! I thought it had. My reader shows the attachment fine OK, Here's the code anyway (and I've snipped the other thread text) Enjoy! [ ] My bad. I'm using OE; and I completely missed the "This message has an attachment" flag. When I went back and checked, sure enough ... I can get the original attachment also. Thanks much for sharing your work! -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code corrupts file (XL2003) | Excel Programming | |||
Code corrupts file (XL2003) | Excel Programming | |||
XL2003 to XL2000 code converson | Excel Programming | |||
XL2003 VBA: Debuging...can not break or run in break/step mode. | Excel Programming | |||
Running procedures on events xl2003 | Excel Programming |