Reading a text file into excel 2000

[matt_h]

I am trying to automate reading a text file into an excel spreadsheet...

The text file doesn't have a structure to it so I am reading each line at a time then processign it.

I have opened the text file as a stream and can read the line, but how can I detect when I am at the end of the file???

The other problem I am having is adding a new worksheet to an excel spreadsheet ready to receive the data, as it always appears before the current worksheet, to the left, and I need it to appear after the current worksheet. I have looked at worksheet.add and I can see you can specify after but I can't get my head around how to do it.

One final problem I am encountering is the amount of time it is taking to process a 2mb text file, around 9 mins. This is obviously due to the fact that after reading the line I am having to check for a variety of conditions to determine how it should be processed. How can I reduce the amount of time it takes to process the file?

Am using VB.NET.

Hope you guys can help.

regards,

Matt

[matt_h]

Anybody give me some clues??

[jmcilhinney]

I have opened the text file as a stream and can read the line, but how can I detect when I am at the end of the file???

You should be using a StreamReader. Test the EndOfStream property and call the ReadLine method.

One final problem I am encountering is the amount of time it is taking to process a 2mb text file, around 9 mins. This is obviously due to the fact that after reading the line I am having to check for a variety of conditions to determine how it should be processed. How can I reduce the amount of time it takes to process the file?

I'm doing something. It takes a long time. Can you tell me how to make it quicker? Of course you can't, because you have no idea what I'm doing or how I'm doing it. Now consider what we know about what you're doing. How can we know how to speed up your "processing" when we have no idea whatsoever what your "processing" involves? That is just common sense, is it not?

[matt_h]

Thanx for reply...

With regards the second point, yes I do appreciate what you're saying. I guess my question would should have been, Is there an alternative to opening up a stream that is a quicker option for reading large files.

regards,

Matt

[jmcilhinney]

You said yourself that the speed issue is a result of the data processing, not the I/O, so using a different method of I/O is unlikely to have a significant effect. What might have an effect would be to use pointers to process the data, either in C# or unmanaged code. If you were to use unmanaged code to do the data processing then unmanaged code for the I/O as well might be an option. That said, we'd have to know significantly more about this data processing to have any idea of what could be done to speed it up, plus you'd have to have an idea of how to use pointers.

[matt_h]

Hi,


Reading the file is not so straight forward as it includes empty lines, spurious characters, etc, etc.


I have done a test with:

dim filelines as new collection
dim filestream as system.io.streamreader
try
dim iss as string
filestream = system.io.files.opentext(curpath & "\sum\" & filename & ".sum")
do
iss = filestream.readline()
console.writeline(iss) ' used to see what is in currentline
loop
catch myerror system.io.endofstreamexception
catch myerror as exception
messagebox.show(myerror.message)
finally
if not filestream is nothing then
filestream.close()
end if
end try

It doesn't seem to throw an exception, just continues reading lines. What have I done wrong.

As an alternate solution I have tried

do

loop until filestream.peek = -1


This seems to work ok, but I am not sure it's the best option.

regards,


Matt

[matt_h]

Anybody???


regards,

Matt