Excel SQL bug?

[Brave_Lee_Flea]

Hi,

I hope somebody can help me. I am using ODBC to connect to a Excel 2003 worksheet and select the data. For the most part this is working just fine, however in one particular column of data has values which are both text and numbers stored as text (In different rows, obviously!)

When I select the data the numeric values are returned as null values, and if I restrict the query to where "column is null" then the only rows returned are where the column has a numeric value rather than a string value.

This means that the bug is within the EXCEL SQL engine, so I need a workaround. However I cannot find any documentation of the SQL implementation used in EXCEL.

I don't know for example if it is possible to CAST or CONVERT the field or to use an IF .. THEN .. ELSE construct or if I can test the field with an IsNumeric-like function.

Can anybody either point me in the direction of some usable documentation or tell me if this is a known bug and if there is a work around?

Many Thanks

Lee

 

[xenou]

Whatever it is it's not built into Excel. Possibly the ODBC driver for Excel is the same as the one used by MSQuery. Not sure though. ADO is a better way to query Excel (IMHO) - at least insofar as working in code. I guess though truthfully I can't really say I know exactly how you are using ODBC connections in this case - I've mainly worked with ADO in VBA (and a little with ADO.NET).

 

[energman58]

But ADO is fairly picky about the types of SQL it "speaks" - if you are in a MS environment it is fine but you need to check it works properly with other DB providers.

 

[RoryA]

ADO does not speak SQL at all - it leaves that up to the provider.

 

[energman58]

Sorry - more precisely ADO can be used to execute SQL commands which is what is being done here. The disadvantage of this is that it introduces a dependency upon the type of database used. There is documentation of this on the MS website.

 

[RoryA]

Well yes, but if you are querying a database you are pretty much dependent on its SQL syntax are you not?