none
How to get Column names from Excel using OLEDB query if the column name doesnot start from first row RRS feed

  • Question

  • Hi All

    I am facing 2 issues in OLEDB query

    (Problem 1)

    I need to get the column names of user uploaded excel sheet.

    My connection string for excel version 97-2003 is 

     connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileNameAndPath +

                                            ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";

     

    Then I will use this command

    connection.Open();

     DataTable schemaTable = connection.GetSchema("Columns");

    Taking column name from each datarow like this

    string name = (string)row["COLUMN_NAME"];

    It is working fine, if the column name is present in first row of excel. But it will give the column name as "F1,F2,....." if the column name row doesn't start from first row. How can I solve this? is there any way to tell in query, from which row to take the column names?

    (Problem 2)

    Like the column header I need to select data from a particular range from excel. say like take data from row 3 to row 12.

    So I used the query like this,

    Select RepName, Country from [Sheet1$3:12].

    But it is throwing the exception,message like this "No value given for one or more required parameters."

    Is any syntax error in my query? if so can any one please tell how to correct the query?

    Thanks

    Swapna

     

     

     

    Monday, October 18, 2010 6:59 PM

Answers

  • You need to include 'Range' value in select command.

    Range value should be in this format A1:BP23

    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
              "Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;IMEX=1;\"";
    
           
            using (OleDbConnection oleConnection = new OleDbConnection(connectionString))
            {
              //oleConnection.Open();
              setJobName(connection.JobName);
              OleDbCommand oldCmd = new OleDbCommand("SELECT * FROM [" + SHEET
                + "$" + Range + "]", oleConnection);
    
      
    
              OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
              oleAdapter.SelectCommand = oldCmd;
              oleAdapter.Fill(ds);
              
    
            }
    
    • Marked as answer by liurong luo Tuesday, October 26, 2010 2:53 AM
    Thursday, October 21, 2010 10:34 AM

All replies

  • Hello Swapna,

    #1. If you specified HDR=YES in connection string, that indicates the result contains header rows. To get the column name, you could use the ColumnName property. For example,
    /////////////////////////////////////////////////////////////////////////////////////////////////////////////
    String connStr = @"connection string";
    using (OleDbConnection conn = new OleDbConnection(connStr))
    {
        OleDbDataAdapter adp = new OleDbDataAdapter("Select * from [Sheet1$]", conn);
        DataTable dt = new DataTable();
        adp.Fill(dt);
        // fetch column name.
        Console.WriteLine(dt.Columns[0].ColumnName);
        // get data of a particular row.
        Console.WriteLine(dt.Rows[2][0].ToString());
    }

    #2. To get data from a particular row, you could use DataAdapter as code snippet above or a DataReader. Here is a blog entry for reading and writing Excel in ADO.NET, I believe it could help you:
    http://www.davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx

    Best regards


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Wednesday, October 20, 2010 3:18 AM
  • Hi Roahn

    Thanks for your reply

    1) I am getting column names as "F1,F2,F3,...." only if the header row is not present in the first row of excel sheet.

    If it is  present in first row, I am getting it properly.

    2) I could not select a particular range of record using the query.

    Thanks

    Swapna

    Wednesday, October 20, 2010 3:31 PM
  • 1) I am getting column names as "F1,F2,F3,...." only if the header row is not present in the first row of excel sheet.

    If it is  present in first row, I am getting it properly.

     That's normal behavior,  if the header row is nor present, and you attempt to get column name using the ColumnName property, it will display F1, F2, F3 and so on. I think they are default values.

    2) I could not select a particular range of record using the query.

    Would you please post a code snippet?
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Thursday, October 21, 2010 9:38 AM
  • You need to include 'Range' value in select command.

    Range value should be in this format A1:BP23

    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
              "Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;IMEX=1;\"";
    
           
            using (OleDbConnection oleConnection = new OleDbConnection(connectionString))
            {
              //oleConnection.Open();
              setJobName(connection.JobName);
              OleDbCommand oldCmd = new OleDbCommand("SELECT * FROM [" + SHEET
                + "$" + Range + "]", oleConnection);
    
      
    
              OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
              oleAdapter.SelectCommand = oldCmd;
              oleAdapter.Fill(ds);
              
    
            }
    
    • Marked as answer by liurong luo Tuesday, October 26, 2010 2:53 AM
    Thursday, October 21, 2010 10:34 AM
  • Thanks Mike. It works!

    Regards

    Swapna

    Friday, October 22, 2010 8:25 PM