none
Pivot query from OleDBConnection on Worksheet RRS feed

  • Question

  • I'm having a pivot query which runs perfectly on SQL Server. I want to use the same on a worksheet for re-arranging columns. While executing it with OleDBAdapter, an exception is thrown stating Incorrect FROM Clause.

    Here's the code:-

    DataTable

     

    excelSchema;

    excelSchema = connExcel.GetOleDbSchemaTable(

    OleDbSchemaGuid.Tables, null);

     

    string query = "Select * from [" + sheetName + "]  PIVOT (SUM(Cost) FOR TimePeriod IN (Jan2009, Feb2009)) as pivotTable";

     

    OleDbCommand oleCmd = new OleDbCommand(query, connExcel);

     

    OleDbDataAdapter oleAdptr = new OleDbDataAdapter(oleCmd);

    oleAdptr.Fill(some data table);

     

    Worksheet contents:-

    CostId   TimePeriod    Cost

    1           Jan2009       100

    2           Feb2009      120

     

    Thanks for help in advance.. :)

    • Moved by Bessie Zhao Tuesday, August 24, 2010 2:02 AM move for better support (From:Excel for Developers)
    Wednesday, August 18, 2010 10:31 AM

All replies

  • Hello,

    After reviewing your post, I have some general input and a couple of questions. I'm not sure that I can answer or address your issue, but I will try.

    First, the questions:

    1. What application are you using this sql statement in?

    2. Do you have the range in Excel labeled with a defined name? 

    Generally, when you attempt to use Excel files for the source of a sql style query from an external source, the range(s) that you are attempting to address need to be labeled and addressed with defined names, not the sheet name.

    So you should hilite the data range including the headers, go to the Formula ribbon, and click the Defined Name button, and give the range a defined name.

    Then, address the defined name in your sql statement, after setting up the proper driver to access the Excel file.

    My second thought is that maybe the Excel file does not correctly handle the "Pivot" clause in your sql statement.  I am no SQL expert, but that might be the issue.  Does a simple "Select * From <table name>" work?

    Please reply with your findings based on the items mentioned above.  If these do not assist you, you will need to contact us and open a support incident, as this largely falls outside of the scope of these forums.

    Thanks,

    Wendal Dorsey, MSFT

    • Marked as answer by Wendal Dorsey Friday, August 20, 2010 5:14 PM
    • Unmarked as answer by RJ_DarkShade Monday, August 23, 2010 8:55 AM
    Friday, August 20, 2010 5:13 PM
  • Hi Wendal,

    1. I'm using this query in a .NET 2.0 Win Forms Application with Excel 2007 using C#.

    2. Also, I missed a statement in the above code..

    //Get the first sheet of the workbook.

     

    string sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();

    This returns the 1st sheet name. There is no need of defining a fixed range for this, as a simple "select * from Sheet1$" works (It returns all records in Sheet 1). Regarding the defining of column headers, it can be specified in the connection string which I already did.

    I think that Excel has some problem with PIVOT keyword, but could not find any specific proof when searched this on the Internet.

    Thanks,

    Rajat

    Monday, August 23, 2010 7:39 AM
  • Hi Rajat,

    The syntax for PIVOT in SQL Server is different from that in Jet.  The same for Jet would be something like this:

    TRANSFORM Sum(Cost)
    SELECT CostID
    FROM <sheetName>
    GROUP BY CostID
    PIVOT TimePeriod IN ("Jan2009","Feb2009");

    I haven't tried this yet on my end with Excel and ADO.Net code; but it works in Access. Let me know your result plugging it into your code.

    Thanks,

    Cathy Miller

    Friday, August 27, 2010 7:01 PM
    Moderator