Using SQL command to access Excel

[Parrot]

I am using the following SQL command in C# to fill a table from an Excel file.

string strSQL = "Select * FROM [Sheet1$F:G]";
daExcel = new System.Data.OleDb.OleDbDataAdapter(strSQL, urlconn);
table = new DataTable();
int ret = daExcel.Fill(table);

This works fine but I can't figure out how to use the WHERE statement for
selective processing. For example, if I use "Select * FROM [Sheet1$F:G]
WHERE [Sheet1$F] > ' '", if get an error saying that there is a missing
value. Does anyone know how to use a WHERE statement when accessing an Excel
file?

 

[RB Smissaert]

I don't use C#, but can't you give your table fields headers and use
these in the SQL? So for example:

Select * FROM [Sheet1$F:G]
WHERE Field1 > ' '"


RBS

 

[Parrot]

I finally figured out to use column heading names from Excel for my selection
testing but now I find that I am limited in my SQL commands. For example, if
I use the command ... WHERE SUBSTRING(field1, 1,1) = 'F' I get an error
complaining about the SUBSTRING. I guess if you use the Jet Engine to open
the file, you do not have the full capabiliites of the SQL commands. Is this
true?

I don't use C#, but can't you give your table fields headers and use
these in the SQL? So for example:

Select * FROM [Sheet1$F:G]
WHERE Field1 > ' '"


RBS

I am using the following SQL command in C# to fill a table from an Excel
file.

string strSQL = "Select * FROM [Sheet1$F:G]";
daExcel = new System.Data.OleDb.OleDbDataAdapter(strSQL,
urlconn);
table = new DataTable();
int ret = daExcel.Fill(table);

This works fine but I can't figure out how to use the WHERE statement for
selective processing. For example, if I use "Select * FROM [Sheet1$F:G]
WHERE [Sheet1$F] > ' '", if get an error saying that there is a missing
value. Does anyone know how to use a WHERE statement when accessing an
Excel
file?

 

[Ed Ferrero]

Hi Parrot,

I finally figured out to use column heading names from Excel for my
selection
testing but now I find that I am limited in my SQL commands. For example,
if
I use the command ... WHERE SUBSTRING(field1, 1,1) = 'F' I get an error
complaining about the SUBSTRING. I guess if you use the Jet Engine to
open
the file, you do not have the full capabiliites of the SQL commands. Is
this
true?

True. Use LEFT(field1,1)='F' instead.

Ed Ferrero
www.edferrero.com

 

[Parrot]

Ed;
Thanks for the feedback. Is there a different set of SQL commands I can
reference for the Jet.OleDB engine?