Oledb not retriving complete data from excel into data set

Question

I am trying to get the data from excel into dataset using OLEDB but I am not getting all the rows in the excel into dataset. I have 243300 rows in the excel sheet but I am getting only 44401 rows as present in the dataset table.

Here is the code I am using:

public DataSet GetDataSet(string SheetName)  
{
        string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath.Text + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=0;TypeGuessRows=0'";
        OleDbConnection conExcel = new OleDbConnection(connectionString);
        conExcel.Open();
        OleDbCommand cmd1 = new OleDbCommand("Select * from [" + SheetName + "$]", conExcel);
        int cnt=  cmd1.ExecuteNonQuery();
        DataSet ds = new DataSet();
        OleDbDataAdapter da = new OleDbDataAdapter(cmd1);
        conExcel.Close();
        da.Fill(ds, "Table");
        return ds;
    } 

Answer 1

What is your code for using the dataset table?

Why not try something like this (not updated to match your variable names, this is an example from my own program):

            int worksheetNumber = 1;
            var cnnStr2 = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"", exExcel);
            var cnn2 = new OleDbConnection(cnnStr2);

            // get schema, then data
            var dt2 = new DataTable();
            try
            {
                cnn2.Open();
                var schemaTable2 = cnn2.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (schemaTable2.Rows.Count < worksheetNumber)
                {
                    throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
                }
                string worksheet = schemaTable2.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
                string sql2 = String.Format("select * from [{0}]", worksheet);
                var da2 = new OleDbDataAdapter(sql2, cnn2);
                da2.Fill(dt2);
            }
            catch (Exception ex)
            {
                // ???
                throw ex;
            }
            finally
            {
                // free resources
                cnn2.Close();
            }

Hopefully that will give you at least some reference to help you with your situation.