Hi ,

I need to load an excel sheet data into dataset.

i am able to load complete excel file into the dataset, what i require is to exclude some rows loading into the dataset.

but the row indexs varies from one excel to other.is it posiible ?

thanks in advance

Hide the unwanted columns, either programmatically or manually, prior to importing.

string ExcelPath = " D:\\ExcelFiles";
                        if (Directory.Exists(ExcelPath))
                        {
                            DirectoryInfo GetExeclFiles = new DirectoryInfo(ExcelPath);
                            FileInfo[] Files = GetExeclFiles.GetFiles("*.xls");
                            if (Files.Length > 0)
                            {
                                DataTable dtnewtable = new DataTable();
                                DataTable dttable = new DataTable();

                                string FilePath, excelConnectionString, sqlConnectionString;
                                for (int k = 0; k < Files.Length; k++)
                                {
                                    FilePath = Files[k].FullName.ToString();
                                    excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0;";
                                    // Create Connection to Excel Workbook
                                    using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
                                    {
                                        if (connection.State == ConnectionState.Closed)
                                            connection.Open();
                                        OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
                                        // Create DbDataReader to Data Worksheet
                                        using (System.Data.Common.DbDataReader dr = command.ExecuteReader())
                                        {
                                            dttable.Load(dr);

                          //you can delete rows form datatable dttable here
                                             
                                            // SQL Server Connection String
                                            sqlConnectionString = ConfigurationSettings.AppSettings["conString"].ToString();

                                            // Bulk Copy to SQL Server
                                            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                                            {
                                                bulkCopy.DestinationTableName = "tbl_DataEntry";
                                                bulkCopy.WriteToServer(dtnewtable);
                                            }
                                         
                                            connection.Close();
                                            connection.Dispose();
                                            File.Delete(FilePath);
                                        }//end of USING dataReader
                                    }//end of USING sql connection

                                }//end of for loop
                              
                                MessageBox.Show("Successfully Uploaded..");
                            }//end of if(Files.Length > 0)

Hi, i need to exclude rows of excel before loading data into data reader object

 Hi rprashanthkumar,

              The code which I wrote in previous post,before loading the into data reader,we can manipulate the datatable and insert into database