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

 

[RoryA]

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

By which definition would you say that narrowing down the source of the problem is moot?

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

Now that is a moot statement.

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.

That is intriguing - I don't think I've ever seen that - only the other way round. Any data that doesn't match the determined data type should be coerced if possible and only set to null if the coercion fails. I can't see how numeric data would not coerce to text.

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

Other than the help file, God only knows. Trial and error is usually better.

 

[Brave_Lee_Flea]

That is intriguing - I don't think I've ever seen that - only the other way round. Any data that doesn't match the determined data type should be coerced if possible and only set to null if the coercion fails. I can't see how numeric data would not coerce to text.

I can't see why it wouldn't coerce either but it's clear that the query engine has determined that the field should be treated as null otherwise the where clause of "A is not null" would not work.

My first thought was that I should try to forcefully convert/cast/coerce the field myself but I don't know what functions are available in the Query engine.

Other than the help file, God only knows. Trial and error is usually better.

Trial and error is the last resort of the desperate!!!

I wonder for example does the Query engine support in-line conditional expressions e.g. the CASE statement;

select
case
when Column_A is not null then Column_A
else CSTR(Column_A)
end
from Worksheet

Is CSTR() a valid function? If not, what are the functions for casting data types?

I have so many questions, many of which I can't just guess the answers to, which is why I was hoping for some documentation. The best I can find so far are some suggestions on the net that this book is useful;

http://www.amazon.co.uk/Excel-ADVAN...=sr_1_2?s=books&ie=UTF8&qid=1310996075&sr=1-2

 

[RoryA]

As best I recall, the available functions vary depending on what the back end data source is.
I have no experience with that book, so can't comment on it.

 

[Brave_Lee_Flea]

As best I recall, the available functions vary depending on what the back end data source is.
I have no experience with that book, so can't comment on it.

Ok, no problem. My time is worth more than the £15 it costs to buy the book. I've ordered it (secondhand, of course) and hopefully it can shed some light on the situation.

Actually there are many features I am be interested to know about as the application I have written is a C++ program which allows the user to browse and manipulate data from any ODBC capable data source (theoretically) so there's bound to be some useful stuff in there.

Unfortunately neither ODBC nor SQL are terribly standard standards and a large part of what I have to do is "teach" my program about the different nuances of each data source.

Anyway, thank you for your help. If I have any success I'll report back, I'll also let you know what I thought of the book once it arrives.

 

[RoryA]

Thanks - I'd be very interested in your thoughts on the book as I've looked at it before but never got around to buying it.

Almost forgot - you can definitely use Case When statements if the backend is SQL server. Not sure about other sources.

 

[xenou]

Just to confirm, I can use MSQuery to query an xls file with Text and Numbers (I put text values in the first ten rows to ensure the correct "guess"). The numbers are returned as text. Access behaves the same when importing data from Excel.

ξ

 

[Brave_Lee_Flea]

I have only picked up the terminology "MSQuery" from this thread so I may be be using it incorrectly; I am using a C++ program to open an ODBC connection to an xls spreadhseet via the Microsoft ODBC for Excel driver.

I will take some screenshots but it's clear from Fazza's link that I am not alone in experiencing this problem.

 

[Brave_Lee_Flea]

Almost forgot - you can definitely use Case When statements if the backend is SQL server. Not sure about other sources.

I'm pretty good these days ay knowing the nuances of different flavours of SQL, I have made my software work with Oracle, SqlServer, Postgres and other relational databases, and each has idiosyncrasies in the implementation of their SQL.

In this case the back-end is an Excel spreadsheet ... I don't know what SQL engine is being used, whether it is built into Excel or it is this "MSQuery" thing (I had assumed the latter - but only from the contents of this thread, I'm afraid know virtually nothing about Excel, great tool though it is).

Normally one would expect to find a SQL language reference guide, but since I don't know what it is that is parsing the SQL I don't know quite what search for ......

 

[RoryA]

Hard to say without knowing how you are opening a connection. Are you using ADO/DAO other? It doesn't sound like MSQuery is involved here (or even Excel, really).
(I doubt you can use Case When against Excel workbooks)

 

[Norie]

Lee

Where are you actually doing all this?