Speeding up the importing of data from file

[Fan924]

I am using the following to import binary data from a file and convert
to hexadecimal. Is there a different way to do this that would be
faster? I got list help last year to speed up my checksum routine by
putting the data into a variant array. I was able to also us it for
saving to a file too. I need some of the same magic importing data
from a file. File size can be over a meg in size and is taking minutes
to load.
_____________________________________________________

Dim fs, f, ts, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(FileNameWithPath)
Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
ColumnCount = 3
RowCount = 1
Do While ts.atendofstream = False 'Loop to fill column with HEX
numbers
DecimalByte = Asc(ts.Read(1))
HexByte = Hex(DecimalByte)
Cells(RowCount, ColumnCount) = HexByte
RowCount = RowCount + 1
Loop
ts.Close

 

[Fan924]

I was experimenting with this

 

[Barb Reinhardt]

I'd add this at the beginning of code

Application.ScreenUpdating = false
Application.calculation = XLCalculationManual

and at the end

Application.ScreenUpdating = True
Application.Calculation = xlcalculationAutomatic

 

[Fan924]

Already there.

 

[geoff_ness]

I am using the following to import binary data from a file and convert
to hexadecimal. Is there a different way to do this that would be
faster?  I got list help last year to speed up my checksum routine by
putting the data into a variant array. I was able to also us it for
saving to a file too. I need some of the same magic importing data
from a file. File size can be over a meg in size and is taking minutes
to load.
_____________________________________________________

Dim fs, f, ts, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(FileNameWithPath)
    Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
    ColumnCount = 3
    RowCount = 1
Do While ts.atendofstream = False 'Loop to fill column with HEX
numbers
    DecimalByte = Asc(ts.Read(1))
    HexByte = Hex(DecimalByte)
    Cells(RowCount, ColumnCount) = HexByte
    RowCount = RowCount + 1
Loop
ts.Close

I would change the way you're returning results to the spreadsheet.
Try populating an array in the loop, and then transferring values all
at once at the end rather than a cell at a time - this is likely to be
a lot faster. Reading the file into the textstream does not take that
long, even for a file of the size you're dealing with - I timed
something similar in Python at around 18 seconds for 100 iterations to
read a 250KB pdf, convert byte-by-byte to ansi code, then hex, then
return to an array. VBScript is a bit slower perhaps, but certainly
with Excel transferring data to the worksheet is the more likely place
for a bottleneck to occur.

 

[joel]

He is some more info on reading files

1) You don't read into a string. You are using an I/O function in th
windows operating system that may not read the entire file at once.
that is why you are able to read more the 64K

2) Windows when it opens a file doesn't different things depending o
the amount of memory in your computer and the size of the file.
a) You computer has some very fast memory and Windows tries t
put the entire file into that memory. This takes time to move a larg
file in to this memory from a slower hard disk so it is done in th
background on large files so your program can continue to run.

b) Part fast memory is a I/O buffer that you program directl
uses and is controller by VBA. Windows will detect when the I/O buffe
empties windows will fill the buffer with more data from the file. I'
not sure what the buffer size is today (haven't looked in a while) bu
used to be around 64K bytes.

B) But it must share that memory with other process. S
depending on the size of the file some of the file may get moved to swa
space in slower memory or even back intp swap space on the hard drive.

3) Reading a file sequentiually (one line at a time) is slow bu
requires only a small amount of memory *the I/O buffer)

4) Using the Scripting.FileSystemObject object the is a method READAL
that will read the entire file in one statement. This could speed u
that program, or slow it down. Reading the entore file could put som
of the data into swap space on the hard drive. I haven't had the nee
to in years at analysing all the combinations of events to give a
accurate accounting of what wil happening today on your computer wit
the size of the file and the amount of memory in your computer. I jus
understand very well the theory