Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access command from Excel | Excel Programming | |||
Printing Access reports through Excel command button? | Excel Discussion (Misc queries) | |||
Open Access DB in Excel with Command Button | Excel Programming | |||
Open Access DB in Excel with Command Button | Excel Programming | |||
Open Access DB in Excel with Command Button | Excel Programming |