Read a large txt file using VBA

[Photomofo]

Here's a snippet of code that reads in a big txt file. It used to work but the size of the INPUT text file grew such that I'm now getting an out of memory error. On a whim I changed objTS.Read(objFile.Size) to objTS.Read(objFile.Size / 20) and the file is getting read again but apparently only 1/20th of it.

I'm looking for a widget that would break the file into smaller chunks but I can't seem to find one that works.

Any advice on a different way to approach this problem?

File_x = Application.GetOpenFilename()
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFile = objFS.GetFile(File_x)
Set objTS = objFile.OpenAsTextStream(ForReading, TristateFalse)
EBA_File = objTS.Read(objFile.Size / 20)


'Chop up EBA_File into Smaller Strings
'***************************************************************
k = Application.RoundUp(Len(EBA_File) / 50000000, 0)


For i = 1 To k


String_Index(i) = Mid(EBA_File, (i - 1) * 50000000 + 1, 51000000)


Next i


EBA_File = vbNullString

 

[Logit]

.
Dim i As Long ????

 

[Rick Rothstein]

How large is the text file (what does File Explorer show for it)?

 

[iliace]

EBA_File = objTS.Read(objFile.Size / 20)

If that reads 1/20th of file, shouldn't you be able to do something like:

Dim iRead As Long
Dim iInputSize As Long

' open the file, get the dimensions

iInputSize = objFile.Size \ 20

For iRead = 0 to ObjFile.Size Step iInputSize
  EBA_File = objFile.Read(iInputSize)
  ' .......
Next i

...and then manipulate the input from there?

 

[Photomofo]

It's 346 MB.

 

[Photomofo]

I'll give it a try. Thanks.

 

[Photomofo]

Hmmm... That didn't work. It hangs up on the EPA = objFile.Read(iInputSize) step.

 

[iliace]

Hmmm... That didn't work. It hangs up on the EPA = objFile.Read(iInputSize) step.

OK, sorry - I don't work with FSO very often, it seemed like it should work. Can you describe what exactly happens, and what is the value of iRead at that point?

What is EBA_File? If it is a String type, it is limited to 2*10^9 number of characters. Perhaps that's the issue (although it shouldn't be, if the file is only 346MB)?

 

[Photomofo]

It's ok... Thanks for your help but I managed to get things working. I'm still getting out of memory errors but they're coming up much later in the sub. EBA means Electricity by Balancing Area. It's a file that describes the hourly flow of electricity between all the Balancing Areas in the US. Here's the file (http://api.eia.gov/bulk/EBA.zip).

InputFile = Application.GetOpenFilename()


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set ObjTextFile = objFSO.OpenTextFile(InputFile, 1)
 
Do Until ObjTextFile.AtEndOfStream


    Line_Count = Line_Count + 1
    String_Index(Line_Count) = ObjTextFile.Readline
    
Loop


ObjTextFile.Close

 

[iliace]

It's ok... Thanks for your help but I managed to get things working. I'm still getting out of memory errors but they're coming up much later in the sub.

OK, progress. Again, this is not really my area of expertise, but I would suggest, as the first remedy, to try to limit the number of lines you read into your array (break it up into chunks like you were before), and dump it wherever it is going. Then start with a clean slate.

Is your Office/Excel 32-bit or 64-bit? If it's the former, then you could have memory limitation problems with a file that size.

There are probably workarounds, but it depends on what your end goal is whether or not they will work in your situation.