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

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

Mycroft Holmes · Answer 1 · 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

_Asif_ · Answer 2 · 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_

Read large excel file data in small batches

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Read large excel file data in small batches

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us