Invalid Sql statement error on when connecting Excel file with C# code

Question

I am trying to convert excel file rows into columns using C# code

string command=";WITH CTE AS(SELECT * FROM (SELECT IPAddress, Slot1,Slot2,Slot3,Slot4 FROM ["+ sheet1 +"] ) T UNPIVOT ( SlotPort FOR N IN (Slot1 ,Slot2 ,Slot3 , Slot4))P )SELECT SlotPort, IPAddress FROM CTE";

Am using above code to achieve the mentioned process. But I am getting the below error.

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Please anyone help to resolve this issue. Thanks in advance

Which connection do you use? Is excel linked to your sql server? — Hamlet Hakobyan
– Hamlet Hakobyan, Commented Jan 19, 2015 at 14:40
Yes. I am connecting excel with oledb connection using this sql query. — Kalaivani
– Kalaivani, Commented Jan 19, 2015 at 14:49

Hamlet Hakobyan · Accepted Answer · 2015-01-19 14:51:23Z

1

You should specify unpivoted column.

;WITH CTE AS
(
    SELECT IPAddress, N, SlotPort
    FROM
    (SELECT IPAddress, Slot1,Slot2,Slot3,Slot4 FROM ["+ sheet1 +"]) T
    UNPIVOT (SlotPort FOR N IN (Slot1 ,Slot2 ,Slot3 , Slot4)) P
)
SELECT SlotPort, IPAddress FROM CTE

answered Jan 19, 2015 at 14:51

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Invalid Sql statement error on when connecting Excel file with C# code

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related