Oledb connection and query read only one column from excel spreadsheet

Question

Possible Duplicate:
How can i select specific columns from excel sheet in c#?

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|2.xls;Extended Properties='Excel 8.0;HDR=no;'";

string query = "SELECT * FROM [Sheet1$]";

DataSet excelDataSet = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(query, strConn);

da.Fill(excelDataSet);

GridView1.DataSource = excelDataSet;
GridView1.DataBind();
GridView1.HeaderRow.Cells[0].Text = "CheckNumber";

I have this code to read an Excel Spreadsheet being loaded from a website and being displayed in a gridview. I would like to simply just read column A on the spreadsheet. I think I should be able to change this string query = "SELECT * FROM [Sheet1$]"; but all my efforts have been futile. Can someone point me in the right direction, or is there a better way to do this.

This link might have some helpful stuff for you: http://stackoverflow.com/questions/3232281/oledb-mixed-excel-datatypes-missing-data/5721521#5721521 — user959729, Jan 18 '13 at 17:29
thank you, i have been searching for a while and i couldnt come up with it. — Miguel, Jan 18 '13 at 17:31

score 3 · Answer 1 · answered Jan 18 '13 at 17:32

3

it looks like the way to do this is simply string sql = "SELECT F1, F2, F3, F4, F5 FROM [sheet1$]; Thanks for the comments everyone.

answered Jan 18 '13 at 17:32

Miguel

2,019
4
29
53

score 0 · Answer 2 · answered Jan 18 '13 at 17:33

I believe your problem lies in the fact that a spreadsheet is not a database. A spreadsheet is under no obligation to be rectangular or have cells of the same type. So saying you want a column ASSUMES that that column exists for all rows and is of the same type. So before you issue SQL against it you need to convert to a vector of the same type.

Mark Kram · Answer 3 · 2013-01-18T18:20:03.113

Here is what I use to read an Excel Spreadsheet and return it as a DataTable and if you focus in on the following section where I am able to query all of the workbooks in the Spreadsheet by looping through the dtSchema DataTable object to find the names of the different worksheets:

public static DataTable GetExcelData(string connectionString)
{
    string sql = string.Empty;
    using (OleDbConnection cn = new OleDbConnection(connectionString))
    {
        using (OleDbDataAdapter adapter = new OleDbDataAdapter())
        {
            DataTable dt = new DataTable();
            using (OleDbCommand command = cn.CreateCommand())
            {
                cn.Open();
                DataTable dtSchema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                    foreach (DataRow dr in dtSchema.Rows)
                    {
                        //Will Loop through the name of each Worksheet
                        Console.WriteLine(dr["Table_Name"]);
                    }


                string firstSheetName = dtSchema.Rows[0].Field<string>("TABLE_NAME");

                sql = "SELECT * FROM [" + firstSheetName + "]";
                command.CommandText = sql;
                adapter.SelectCommand = command;
                adapter.Fill(dt);

                if (dt.Rows.Count == 0)
                {
                    OleDbDataReader reader = command.ExecuteReader();
                    dt.Load(reader);
                }
                cn.Close();
                return dt;
            }
        }
    }
}

it seems like i should be able to learn something from this but i cant really follow what is going on and it really dosent answer the question that i asked wich is how to get just one column from an excel spreadsheet. thanks for the reply though. — Miguel, Jan 18 '13 at 20:37

Oledb connection and query read only one column from excel spreadsheet

3 Answers3