OleDBDataAdapter UNPIVOT Query not working with Microsoft.ACE.OLEDB.12.0 DataSource
- by JayT
I am reading in an excel file with an OleDBDataAdapter. I am using a select statement to UNPIVOT the data and insert into DataSet. However, the compiler is genereating this error:
{"Syntax error in FROM clause."}
But the SQL Statement is correct as I have used it in other DB's
Here is the code:
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=" + HDR + ";IMEX=1\"";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string SQL = "select Packhouse, Rm, Quantity , Product " +
" FROM " +
" ( " +
" SELECT Date,Packhouse, Rm,[FG XL], [FG L] " +
" FROM [" + xlSheet + "] " +
" ) Main " +
" UNPIVOT " +
" ( " +
" Quantity FOR Product in ([FG XL], [FG L]) " +
" ) Sub " +
" WHERE (Date = '2010/03/08') and Quantity <> '0' and Packhouse = 'A' and Rm = '1' ";
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(SQL, conn);
ds[sequencecounter] = new DataSet();
adapter.Fill(ds[sequencecounter], xlSheet);
If I copy and paste the excel data into a DB, then the select query works, but the data presented to me is in excel spreadsheets.
If anyone could provide help on this it will be much appreciated.
Regards,
J
