Re: load/parse large text file

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I've updated my code to read the entire file first, one line at a time into
an array (wow, that does seem faster, now down to about 30 seconds), parse
each line in memory (almost instant), and now I'm ready to set the worksheet
range equal to the array. I now have a few more questions. Using Excel 2003.

1) I'm never sure how many rows the data file will have, so I start with an
oversized array so I won't have to continually redim/preserve. My array is
dimmed as testarray(1 To 32, 1 To 50000). I'm not worried about memory, so
is it ok to dim an oversized array or will that cause some other problem I
don't know about yet?

2) I'm not sure how many rows are in the data file in advance, so I
increment a counter variable, rw, as I read each line. what is the
appropriate syntax to set a range equal to the array? I tried the following,
but it didn't work out.
Sheet1.range("A1") = testArray() 'in case Excel could autoexpand the range
to match the array size
Sheet1.range("A1:AF50000") = testArray() 'to paste the whole array at once,
risk overwriting surrounding areas with blanks
Sheet1.range("A1").resize(rw,32) = testArray() ' to paste only the same
number of rows as were in the raw file?

I keep getting a type mismatch error :(

3) I'm actually reading the raw file into (32, 1 to 50000), then parsing
each string back into fields 1-31. When I write the range back, is there an
easy way to just write the first 31 fields of the array? My alternative is
to erase field 32 before writing the whole array (probably with a
redim/preserve to make the array smaller), but I wasn't sure if I could just
write the desired section of the array- it might be useful if I later find
out I need to go back to the original string for any reason.

Many thanks!!
Keith

"Peter Grebenik" <pgrebug@xxxxxxxxx> wrote in message
news:1175878841.505173.36340@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Keith

My understanding is that each "write" to the spreadsheet carries an
overhead.

So, you could try parsing something like 100 rows of data into a
variant array and then writing that to the spreadsheet by simply
setting the appropriate range equal to the array. This will also cut
out your call to your ConvertCol routine.

HTH

Peter



.

Relevant Pages

  • Re: convert .txt to .wav
    ... Maybe you should read the text file first. ... Then, depending on what you read in from the text file, you should reformat that data and create an array that contains the time domain signalyou want to write to the WAV file. ...
    (comp.soft-sys.matlab)
  • Re: Array, foreach problem
    ... John W. Krahn wrote: ... With that foreach statement, it reads the file first and creates an ... array with each line as elements and that array is being looped so the ...
    (perl.beginners)
  • Re: Number of lines
    ... Fred Atkinson wrote: ... You have to read the file first. ... Either put the file contents in an array and count it: ... USENET would be a better place if everybody read: | to email me: use | ...
    (comp.lang.php)
  • Re: fopen and r+ problem
    ... Open the file first for reading, put its contents into an array or other ... PHP variable, then re-write the file with your new data on the first line, ...
    (alt.php)
  • Rounding Issues
    ... I have to tally the total amount ... charged to each agency. ... The spreadsheet is sorted by account number and I can't ... array are then multiplied, ...
    (microsoft.public.excel.worksheet.functions)