import excel with for loop in table

Hi Apurva,

It seems that "can not find column no" means data is not loaded in DataTable or DataSet. And the error comes due to data is not there. Use below code to load the data

var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\""; ;
var ds = new DataSet();
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";
var adapter = new OleDbDataAdapter(cmd);
adapter.Fill(ds);
}
}

For above use

using System.Data.OleDb;

Then read the data from dataset. I think it will work for you.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Code Alone8y ago
Hi Apurva,

It seems that "can not find column no" means data is not loaded in DataTable or DataSet. And the error comes due to data is not there. Use below code to load the data
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\""; ;

        var ds = new DataSet();

        using (var conn = new OleDbConnection(connectionString))

        {

            conn.Open();

            var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            using (var cmd = conn.CreateCommand())

            {

                cmd.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "]  ";

                var adapter = new OleDbDataAdapter(cmd);

                adapter.Fill(ds);

            }

        }

For above use

using System.Data.OleDb;
Then read the data from dataset. I think it will work for you.