Get column names from excel file of a specific sheet using c# with OleDbConnection

Question

So far I have managed to get the column names of the whole excel file, but what I would like to do is to get the column names of the excel file of a given table (sheet). How could I modify the code to achieve this. I have been trying for a while now with no positive results, any help much appreciated.

public static List<String> ReadSpecificTableColumns(string filePath, string sheetName)
    {
        var columnList = new List<string>();
        try
        {
            var excelConnection = new OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filePath + "';Extended Properties='Excel 12.0;IMEX=1'");
            excelConnection.Open();
            var columns = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null);
            excelConnection.Close();

            if (columns != null)
            {
                columnList.AddRange(from DataRow column in columns.Rows select column["Column_name"].ToString());
            }

        }
        catch (Exception exception)
        {
            Console.WriteLine(exception.Message);
        }

        return columnList;
    }

score 3 · Answer 1 · answered Oct 10 '14 at 09:24

3

You did not include sheet name in your code.
You can try below code:

var adapter = new OleDbDataAdapter("SELECT * FROM [" +sheetName + "$]", excelConnection);
var ds = new DataSet();
adapter.Fill(ds, "myTable");
DataTable data = ds.Tables["myTable"];

foreach(DataColumn  dc in data.Columns){
...
}

answered Oct 10 '14 at 09:24

asdf_enel_hak

7,474
5
42
84

I have been using similar code to yours (which I have not posted here), but in this way the code reads all the rows of the specified sheet and would like to avoid that. The ideas is having a file with i.e 5 sheets to get 5 sets of column names without reading all the data, then when i find a keyword in one of the sets I read the contents of the sheet where the keyword was found. – user2881691 Oct 10 '14 at 09:42
You ar right but I googled your approach but did not find any similar result – asdf_enel_hak Oct 10 '14 at 09:46
Yes, I have been looking for a while now. Maybe is not applicable. Thanks for your time and effort :) – user2881691 Oct 10 '14 at 09:53
You are welcome, I am curios about solution if you find then you can share here. – asdf_enel_hak Oct 10 '14 at 10:39

score 2 · Answer 2 · answered Feb 09 '21 at 12:41

What about using such snippet:

var adapter = new OleDbDataAdapter("SELECT * FROM [" +sheetName + "$A1:Z1]", excelConnection);
var table = new DataTable();
adapter.Fill(table);

For connection string with "HDR=Yes" it will read first row and the destination table will have all columns but no data.

score 0 · Answer 3 · answered Sep 09 '15 at 10:30

You can do something like this:

    private void ValidateExcelColumns(string filePath)
    {            
        var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=Yes;TypeGuessRows=0;ImportMixedTypes=Text\""; ;
        using (var conn = new OleDbConnection(connectionString))
        {
            conn.Open();

            DataTable dt = new DataTable();
            var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = "SELECT TOP 1 * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";
                var adapter = new OleDbDataAdapter(cmd);
                adapter.Fill(dt);
            }

            foreach(DataColumn column in dt.Columns)
            {
                //Do something with your columns
            }
        }
    }

Get column names from excel file of a specific sheet using c# with OleDbConnection

3 Answers3

Linked