VBA - Large StreamWriter Memory Exception

bkienzle
bkienzle used Ask the Experts™
on
Selecting data from a database, building StringBuilder. Trying to write a file using StreamWriter.

Using objWriter As New StreamWriter(strScriptNameMulti, False)
                Debug.Print(sbInsertStatement.Length)
                objWriter.WriteLine(sbInsertStatement.ToString)

End Using


Get error: Exception of type 'System.OutOfMemoryException' was thrown.

Debug.Print result: sbInsertStatement.Length = 95567264

I can split it into two files. One ends up being 60 MB and the other is 32 MB. But I'd rather have one file.

Is there a limit to StreamWriter size?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I doubt it's a limitation of the streamwriter object, probably just too much for your memory to handle on the writeline statement.  

I would try building in a loop to write the  text in chunks.
Dim iChunkSize As Integer = 100000
Dim iNumberOfChunks As Integer
Dim sbInsertStatement As New System.Text.StringBuilder
iNumberOfChunks = Math.Ceiling(sbInsertStatement.Length / iChunkSize)
For x As Integer = 0 To iNumberOfChunks - 1
            objWriter.WriteLine(sbInsertStatement.ToString.Substring(x * iChunkSize, iChunkSize))

Next
Most Valuable Expert 2012
Top Expert 2014

Commented:
What happens if you remove

 Debug.Print(sbInsertStatement.Length)
Top Expert 2015
Commented:
Since the whole String is already built, you might try to use a FileStream instead of a StreamWriter. It usually requires less memory.

I have also read in a few places, although I never tested it, that setting the buffer size yourself, by using one of the constructors that has a buffer size parameter, can prevent a copy in memory, so that might also help.

Author

Commented:
Tried the FileStream. It blows up also.
Must be something in my environment.
Will give the Chunk approach a shot.
Thanks for the suggestions.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial