Solved

VBA - Large StreamWriter Memory Exception

Posted on 2014-03-04
4
296 Views
Last Modified: 2014-03-04
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?
0
Comment
Question by:bkienzle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 18

Assisted Solution

by:UnifiedIS
UnifiedIS earned 250 total points
ID: 39903593
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39903674
What happens if you remove

 Debug.Print(sbInsertStatement.Length)
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 250 total points
ID: 39903738
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
 

Author Closing Comment

by:bkienzle
ID: 39903940
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question