Using SQL command to access Excel
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? |
Using SQL command to access Excel
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" wrote in message ... 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? |
Using SQL command to access Excel
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? "RB Smissaert" wrote: 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" wrote in message ... 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? |
Using SQL command to access Excel
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 |
Using SQL command to access Excel
Ed;
Thanks for the feedback. Is there a different set of SQL commands I can reference for the Jet.OleDB engine? "Ed Ferrero" wrote: 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 |
All times are GMT +1. The time now is 03:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com