Click here to Skip to main content
14,637,025 members
Rate this:
Please Sign up or sign in to vote.
See more:
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 Oledb Data reader is using which is extracting successfully. Even its extracting more than 1 M data as well in to multiple sheets in the same excel like Sheet1,Sheet2 etc. I need to read the entire excel data and store it in Data table. its working fine for below 6 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 ther and its 64 GB ram as well.

Find the code snap below.

tried with both Data Adapter and Data reader.


using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM [" + sheet + "]";
//var adapter = new OleDbDataAdapter();
//adapter.SelectCommand = cmd;
OleDbDataReader reader = cmd.ExecuteReader();

if (sheet.Contains(DocSheet))
{
//adapter.Fill(dtDocExcelData);
dtDocExcelData.Load(reader);

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

else if (sheet.Contains(FileSheet))
{
//adapter.Fill(dtFileExcelData);
dtFileExcelData.Load(reader);

LogWriter.LogWrite("No of Files/docs to process for the File : " + inputFilePath + " Sheet Rows Count : " + dtFileExcelData.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.?

When i googled it, one option is patch update, but client may not be ready for patch update. 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,
Venkat

What I have tried:

Need to read the bulk data from Excel to Data table.
Posted
Updated 11-Nov-18 22:34pm
Comments
CHill60 12-Nov-18 3:57am
   
Why are you trying to read that much data into your DataTable?
venky_CodeProject 12-Nov-18 6:13am
   
Hi,

I got your point. But in my case i have to segregate the Excel data with parent Folder and their corresponding records as child and prepare XML for that. XML have Parent node of folder and child nodes have docs which are associated for that Folder.

So i am reading the sheets and loading the data in Data table. Extraction utility is working and fetching data from Oracle DB and creating excel with that much data without any issues.

Is there any limitation for Data Adapter/Reader of Oledb?. Oracle reader is working for that bulk load.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Think about trying to write that much data onto two pieces of paper. You will soon run out of space.
   
Comments
venky_CodeProject 12-Nov-18 6:15am
   
Hi,

I got your point. But in my case i have to segregate the Excel data with parent Folder and their corresponding records as child and prepare XML for that. XML have Parent node of folder and child nodes have docs which are associated for that Folder.

So i am reading the sheets and loading the data in Data table. Extraction utility is working and fetching data from Oracle DB and creating excel with that much data without any issues.

Is there any limitation for Data Adapter/Reader of Oledb?. Oracle reader is working for that bulk load. Its not failing. Only my tool is failing with Oledb data reader.
Richard MacCutchan 12-Nov-18 7:57am
   
No, but there is a limit to the amount of available space in memory. You need to process the data in manageable portions.

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100