Read large excel file data in small batches

Question

0.00/5 (No votes)

See more:

I have an excel file that contains some 15 lakh records. My intention is to read first 50000 records from the excel file into my datatable (here c# datatable i.e dt) do some processing on these records ,then read the next 50000 records and so on until i fetch all records from file.(each time only 50000 rows)
Because if i read all the data (15 lakh records) and hold in a datatable or dataset my app will definately get slow and crash during processing these rows .

Currently i am fetching all records using below code

C#

using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheetName + "]", conn))
                    {

                        using (OleDbDataAdapter da = new OleDbDataAdapter())
                        {
                            da.SelectCommand = cmd;
                            try
                            {

                                da.Fill(dt);
                                dt.TableName = sheetName.Replace("$", "");

                            }
                            catch (Exception ex)
                            {
                                ErrSheetName = ErrSheetName + "," + sheetName.Replace("$", "");
                            }
                        }

                    }

but i want the data in small batches of 50000.any ideas,any suggestions?
Please share.

Posted 13-May-15 20:08pm

PWAVEL

Add a Solution

2 solutions

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)

_Asif_ · Answer 1 · 2015-05-13T21:02:00

Solution 2

You can provide cell range to the query and manipulate range in your while loop. An example could be like.

C#

cmdExcel.CommandText = "SELECT * From [" + SheetName + "A3:B5]";
da.SelectCommand = cmdExcel;
da.Fill(ds);
connExcel.Close();

For further details look here[^]

Posted 13-May-15 21:02pm

_Asif_

Mycroft Holmes · Answer 2 · 2015-05-13T20:29:00

Solution 1

I found the google [^]search has some fairly specific results that answer you precise question.

Personally I would load all the data into a database and do the processing in the database rather than memory, it will be a lot faster and more robust.

Posted 13-May-15 20:29pm

Mycroft Holmes