VBA - Large StreamWriter Memory Exception

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?
bkienzleAsked:
Who is Participating?
 
Jacques Bourgeois (James Burger)PresidentCommented:
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.
0
 
UnifiedISCommented:
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
0
 
CodeCruiserCommented:
What happens if you remove

 Debug.Print(sbInsertStatement.Length)
0
 
bkienzleAuthor 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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.