Click here to Skip to main content
14,640,678 members
Rate this:
Please Sign up or sign in to vote.
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
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
Rate this:
Please Sign up or sign in to vote.

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.
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

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

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


For further details look here[^]
   

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