Oledb Data reader/Adapter is failing to read bulk data

Bug Finder

Hi,

I am working on some migration project where data is exported from Oracle DB to Excel sheets. Extraction tool is different which is able to extract data to Excel around 1 Million also. In extraction tool Oracle Data reader is used and its extracting 1 Million records successfully. Even its extracting more than 1 M data as well in to multiple sheets in the same excel with multiple sheets like Sheet1,Sheet2 etc. I need to read the entire excel data and store it in Data table for my Ingestion. its working fine for below 8 lac data. But when i am trying to read the data from Excel using OleDb Data Adapter or Data Reader its failing when records exceed 8 Lac and giving error like "System Resources exceeded." My server is free and nothing is running there and its 64 GB ram as well.

Extraction separate tool which is fetching data from Oracle DB using Oracle Data reader. My tool is different which needs to read the excel data at a time and store in Data table for xml preparation.

Find the code snap below.

tried with both Data Adapter and Data reader.

using (var cmd = conn.CreateCommand())

{

cmd.CommandText = "SELECT * FROM [" + sheet + "]";

OleDbDataReader reader = cmd.ExecuteReader();

dtDocExcelData.Load(reader);

LogWriter.LogWrite("No of Files/docs to process for the File : " + inputFilePath + " Sheet Rows Count : " + dtDocExcelData.Rows.Count);

//clearing the resources

cmd.ResetCommandTimeout();

cmd.Dispose();

reader.Dispose();

}

visual studio is 2013 version and code is in C#. Excel version is also latest as 2016 (.XLSX).

please check the above code part and let me know where it is failing to load the bulk data from excel. Is there any other way to resolve this.?

Provide me any other alternative solutions to over come this. Project is going to live by month end. UAT is phase is failing. please give me alternate solution at earliest.

Awaiting for your valuable suggestions. Thanks in advance.

Regards,

Venky

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Laxmidhar Sahoo7y ago
I thing the data retrive from file that am give may help and at run time you can modify
Bug Finder7y ago
Hi,

Thanks for your reply.

Actually i have to manipulate the Excel data. thats the reason copy to Data table and then iterate it and make the XML nodes like Parent folder and corresponding childs.

But its failing at Loading to Data table it self when records are more than 8 Lac.

Regards,
Venkat
Laxmidhar Sahoo7y ago
Read the data from row wise and send to database .like bellow example revide data from excel .
private static void TestExcel()
    {
        ApplicationClass app = new ApplicationClass();
        Workbook book = null;
        Range range = null;

        try
        {
            app.Visible = false;
            app.ScreenUpdating = false;
            app.DisplayAlerts = false;

            string execPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);

            book = app.Workbooks.Open(@"C:\data.xls", Missing.Value, Missing.Value, Missing.Value
                                              , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                             , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                            , Missing.Value, Missing.Value, Missing.Value);
            foreach (Worksheet sheet in book.Worksheets)
            {

                Console.WriteLine(@"Values for Sheet "+sheet.Index);

                // get a range to work with
                range = sheet.get_Range("A1", Missing.Value);
                // get the end of values to the right (will stop at the first empty cell)
                range = range.get_End(XlDirection.xlToRight);
                // get the end of values toward the bottom, looking in the last column (will stop at first empty cell)
                range = range.get_End(XlDirection.xlDown);

                // get the address of the bottom, right cell
                string downAddress = range.get_Address(
                    false, false, XlReferenceStyle.xlA1,
                    Type.Missing, Type.Missing);

                // Get the range, then values from a1
                range = sheet.get_Range("A1", downAddress);
                object[,] values = (object[,]) range.Value2;

                // View the values
                Console.Write("\t");
                Console.WriteLine();
                for (int i = 1; i <= values.GetLength(0); i++)
                {
                    for (int j = 1; j <= values.GetLength(1); j++)
                    {
                        Console.Write("{0}\t", values[i, j]);
                    }
                    Console.WriteLine();
                }
            }

        }
        catch (Exception e)
        {
            Console.WriteLine(e);
        }
        finally
        {
            range = null;
            if (book != null)
                book.Close(false, Missing.Value, Missing.Value);
            book = null;
            if (app != null)
                app.Quit();
            app = null;
        }
    }
Imran Munir7y ago
delete data from list and re post to the grid and refresh it.