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

 

[Hermanito]

Can you post a sample of your data and describe how you create the selection (MS Query? pure VBA?).
Personally I doubt you found a bug in SQL as used in Excel; much more likely you are doing something wrong yourself

 

[Norie]

What should this field be?

Text? Numeric?

I agree with Hermanito, this isn't really a bug.

It's kind of standard behaviour, thoughof course it's annoying.

Even if you were importing the data into Access you would probably get the same problem.

 

[energman58]

I dont understand - you have data in Excel whihc you want to return as an SQL query and it is recognising numbers formatted as text as numbers (not text)?

 

[Fazza]

please refer http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/

 

[xenou]

A quick fix (which may or may not work in your situation) is to ensure that columns with numbers and text in them always have a text value in the first row.

 

[Brave_Lee_Flea]

please refer http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/

Thank you for this Fazza - and indeed to everybody who replied. My apologies for not replying sooner, I was away on holiday last week.

I'm still catching up with last week's accumulated issues so I haven't yet had a chance to try out the settings mentioned in the article but I'll report back with what I come up with. It's nice to know that I'm not the only person to come across this problem.

Does anybody know where I can find the documentation of Excel's SQL language features?

 

[Brave_Lee_Flea]

Can you post a sample of your data and describe how you create the selection (MS Query? pure VBA?).
Personally I doubt you found a bug in SQL as used in Excel; much more likely you are doing something wrong yourself

Personally I think it's a bug but let's settle for calling it a "different interpretation".

For me if you say "where A is not null" and A has a value in it then it would not be null .... Microsoft apparently disagree .....

Ironically in SqlServer where you have an empty field Microsoft would say this is not necessarily null as they choose to recognise the difference between an "empty" string and a null string.

Thus in SqlServer a field containing '' (i.e. empty) is not the same as NULL but in Excel a field containing '123' CAN be the same as NULL ..... go figure.

 

[RoryA]

It's not Excel per se that has the problem though - it's MSQuery, or rather the provider interfacing between MSQuery and the data source. Because Excel isn't really a database, its columns don't have a data type. That means the provider has to make a guess as to what that type is, and it's not always right.

 

[Brave_Lee_Flea]

It's not Excel per se that has the problem though - it's MSQuery, or rather the provider interfacing between MSQuery and the data source. Because Excel isn't really a database, its columns don't have a data type. That means the provider has to make a guess as to what that type is, and it's not always right.

Agreed, though it's a bit of a moot point.

As a holistic whole the package is not behaving as expected.

If the field has already been determined as a string data type why should it treat numeric values as null rather than returning them as "numeric strings" (if you see what I mean)? It doesn't really make much sense to do that imo.

The other question is; where can I find documentation about MSQuery?