how to get data from datatable using column name

Question

i have a situation where i load a dataset with excel file. All the worksheet are loaded as datatable with the appropriate worksheet name as datatable name. What i am trying to do is get this datatable value using column name. But i am not get error saying

"Column 'Execute' does not belong to table Sheet1".

While loaded excel to datatabel i have used HDR=YES and IMEX=1. I also tried with HDR=NO. nothing is working.

below code is to write excel to datatable

foreach (Microsoft.Office.Interop.Excel.Worksheet wsheet in workbook.Worksheets)
{

    string sql1 = "SELECT * FROM [" + wsheet.Name + "$]";
    OleDbCommand selectCMD1 = new OleDbCommand(sql1, SQLConn);
    SQLAdapter.SelectCommand = selectCMD1;

    SQLAdapter.Fill(dataset.Tables.Add(wsheet.Name));

 }

Data from excel loads to each sheet perfectly. but fetching it by column name is the problem.

any suggestions please

I'm not sure how much it has to do with it, but I wonder why you are using the SQLDataAdapter and not the OleDbDataAdapter? I just tried your code but with the OleDbDataAdapter and it worked fine (i.e. I got column names). — Sid Holland
– Sid Holland, Commented Feb 20, 2013 at 16:55
actually my code is for oledb, the object i create is like this OleDbDataAdapter SQLAdapter = new OleDbDataAdapter(); it is crazy i know... but thanks, u gave an answer and it worked. — Vincent
– Vincent, Commented Feb 21, 2013 at 12:11
Right, I see. I thought you might be using this. Anyway, glad I could help. — Sid Holland
– Sid Holland, Commented Feb 21, 2013 at 16:54

Sid Holland · Accepted Answer · 2013-02-20 17:06:23Z

For what it's worth, this is the code I used, which works fine. I assume you are using Interop to loop through the worksheets to make sure you get them in order.

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename +
    "; Extended Properties=\"Excel 12.0 XML;HDR=YES\"";
DataSet dsValues = new DataSet();

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    conn.Open();

    using (OleDbCommand cmd = conn.CreateCommand())
    {
        using (OleDbDataAdapter adapter = new OleDbDataAdapter())
        {
            foreach (Excel.Worksheet wsheet in workbook.Worksheets)
            {
                cmd.CommandText = "SELECT * FROM [" + wsheet.Name + "$]";
                adapter.SelectCommand = cmd;
                adapter.Fill(dsValues.Tables.Add(wsheet.Name));
            }
        }
    }
}

If the OleDbDataAdapter cannot find text in the top cells of each column, then it reverts to the F1, F2, F3... notation for the missing header names. So, for example, if my Excel worksheet looks like this:

Header1           Header3
Value1   Value3   Value5
Value2   Value4   Value6

Then in the DataTable I will get columns called Header1, F2, Header3.

You also need to make sure that the row you designate as the header row has no text in any row above it, otherwise you'll get a bunch of Fn-type headers and then other unexpected text as header names. For example:

      This is my table
Header1  Header2  Header3
Value1   Value3   Value5
Value2   Value4   Value6

Will end up in the DataTable with headers of This is my table, F2, F3, etc.

Yaakov Ellis · Accepted Answer · 2013-02-20 11:02:21Z

1

Try enumerating (in debug mode and/or console) through wsheet.Name.Columns and extract the column names for the table - make sure that it contains the column names that you expect (and in the format that you expect)

answered Feb 20, 2013 at 11:02

Yaakov Ellis

41.6k27 gold badges135 silver badges185 bronze badges

Collectives™ on Stack Overflow

how to get data from datatable using column name

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related