Ssystem out of memory exception

Question

1.00/5 (2 votes)

See more:

I have write my code to convert Excel to other format with delimiters but its throwing System.outof memory Exception
My Code is Like below

Please Help me on this

What I have tried:

<pre>string excelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";Extended Properties=Excel 12.0;";
                        OleDbConnection excelConn = new OleDbConnection(excelConnStr);
                        excelConn.Open();
                        DataTable dbSchema = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        string strsheetName = "Sheet1$";
                        if (dbSchema != null && dbSchema.Rows.Count > 0)
                        {
                            strsheetName = Convert.ToString(dbSchema.Rows[0]["TABLE_NAME"]);
                        }

                        System.Data.DataTable dtPatterns = new System.Data.DataTable();
                        excelCommand = new OleDbCommand("SELECT * FROM [" + strsheetName + "]", excelConn);
                        excelDataAdapter.SelectCommand = excelCommand;
                        excelDataAdapter.Fill(dtPatterns);
                        
                        List<string> xcelData = new List<string>();
                        List<string> ColumnNames = new List<string>();
                        ds.Tables.Add(dtPatterns);
                        totalRowsCount = dtPatterns.Rows.Count;
                        totalColumnsCount = dtPatterns.Columns.Count;
                       
                        for (int c = 0; c < dtPatterns.Columns.Count; c++)
                        {
                            if (c == dtPatterns.Columns.Count - 1)
                            {
                                SB1.Append(cValue.ToString() + dtPatterns.Columns[c].ToString() + cValue.ToString());
                            }
                            else
                            {
                                SB1.Append(cValue.ToString() + dtPatterns.Columns[c].ToString() + cValue.ToString() + sValue.ToString());
                            }
                        }
                        SB1.Append("\r\n");
                        for (int d = 0; d < dtPatterns.Rows.Count; d++)
                        {
                            this.toolStripStatusLabel1.Text = string.Format("Processing Please Wait ...{0} of {1}", (count + 1), totalRowsCount);
                            for (int c = 0; c < dtPatterns.Columns.Count; c++)
                            {
                                int g = dtPatterns.Columns.Count - 1;
                                if (c == g)
                                {
                                    SB1.Append(cValue.ToString() + dtPatterns.Rows[d][c].ToString() + cValue.ToString());
                                }
                                else
                                {
                                    SB1.Append(cValue.ToString() + dtPatterns.Rows[d][c].ToString() + cValue.ToString() + sValue.ToString());
                                }
                            }
                            SB1.Append("\r\n");
                            count++;
                        }

Posted 19-May-17 23:34pm

SukirtiShetty

Updated 20-May-17 2:14am

v2

Add a Solution

Comments

CHill60 20-May-17 5:46am

You may have an infinite loop. Have you tried debugging to make sure you are incrementing the counter on all routes

SukirtiShetty 20-May-17 5:50am

Yes. and I have debugged the code. It works fine for 1000 rows in Excel. It throws error when rows more than 1,00,000 in Excel file

Richard MacCutchan 20-May-17 6:20am

Then don't read so many rows in one request. Learn how to work within the limits of your system.

sameer549 20-May-17 6:12am

when you are working with bulk data, need to read the rows in a chunks/loop, for example, 1000 rows each time

SukirtiShetty 20-May-17 6:28am

I wanted delimiters so i used for loop to get data from excel

sameer549 20-May-17 6:39am

yes but stringBuilder cant hold string for lakhs of records, definitely it throw memory out exception at some point.

SukirtiShetty 20-May-17 6:41am

can you please give me some idea how can i achieve the same results using my code

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-05-20T02:14:00

An alternative to using a StringBuilder to hold the entire spreadsheet is to write straight out to a textfile - see How to: Write to a Text File (C# Programming Guide) | Microsoft Docs[^]. You can use a StringBuilder for each row before you write it to the file (which will help to speed it up). You should use

sb.Clear();

for each loop.

If you don't want to make that much of a change to your existing code (which we can't run - there are too many declarations etc missing), then surround the dataread with a loop that only handles, say, 1000 rows...

C#

StringBuilder sb = new StringBuilder();
for (int i = 0; i <= rowcount; i += 1000)
{
    // in here handle only 1000 rows from the spreadsheet

    //After handling the 1000 rows ...

    // Write the contents of sb to the file
    // then do this sb.Clear(); or if you don't have that version of .NET...
    sb = new StringBuilder();
}