CODEWITHSUNDEEP
c#sqlexcel
JJJulien
JJJulien

Reputation: 267

FROM clause when reading excel file using OLEDB Data Provider in c#

How do I modify the below clause to correctly read from an Excel worksheet in C#?

string queryString = "SELECT * FROM [#Ticker$]";

The pound sign in the FROM clause keeps getting converted to the temporary table "." symbol, giving me the following exception at runtime.

".Ticker$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long."

I need it to be a "#" as that is in the name of the worksheet. I have no control of the worksheet naming, so changing the name is not an option.

Upvotes: 3

Views: 231

Answers (3)

Mark McDonald
Mark McDonald

Reputation: 8190

Try escaping the # symbol, e.g. string queryString = "SELECT * FROM [\#Ticker$]"; (or double-escape with \\)

Upvotes: 0

JJJulien
JJJulien

Reputation: 267

I figured it out after some trial and error. The following works.

var queryString = "SELECT * FROM ['#Ticker$']";

The inclusion of the literal '#Ticker' seems to prevent the hashtag from being converted to the temporary table symbol.

Upvotes: 1

Suraj S
Suraj S

Reputation: 1019

Better way would be to use OLEDB parameters.

OleDbCommand cmd= new OleDbCommand("SELECT * FROM @TABLE");
cmd.Parameters.AddWithValue(“@TABLE”, “#Ticker$”);

More information on command parameters, Here

Upvotes: 0

Related Questions