Link to home
Create AccountLog in
Avatar of blossompark
blossomparkFlag for Ireland

asked on

using WHERE clause against a column of datatype FLOAT in sql server import wizard

i'm  using SQl Server Import wizard.
The data source is an xlsm file and i am using the Microsoft Office 10.0 Access Database Engine OLE DB Provider to connecet to this file from within the wizard.
This connects fine, the test connection succeeded.

I then use the option "Write a query to specify the data to transfer".

Using the following query , I get back the data i want beginning at the row I want (row 5 has the column names)
SELECT * FROM `tabname$A5:AJ`

I now want to limit the result set to return rows only where column Number contents are not null

The Number column is of data type float.

SELECT * FROM `tabname$A5:AJ`
WHERE 'Number' is not null

This statement parses ok and the Preview Data window returns a dataset but it does not filter out any rows.

I then changed the query to
SELECT * FROM `tabname$A5:AJ`
WHERE 'Number' = 1

as i know there is a value 1 in the column.
This parses ok
but i get an error returned when i try to preview , data type mismatch

 i then tried  to convert the column  Number to datatype integer but  it fails parsing

SELECT * FROM `Properties$A5:AJ`
WHERE CAST ( 'Number' as INT) = 1

any help appreciated, thanks
Avatar of mbizup
Flag of Kazakhstan image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of blossompark


How did I miss that????
Thanks a million...that worked perfectly!!!
You have no idea how much time i studied this!! :-)
Thanks again
You're welcome!

Just a note about comparing floats... in general, because of the inaccuaracy that is a given with that data type, you should compare based on a 'tolerance' versus a strict equality:

WHERE YourFloatField BETWEEN .9999 AND 1.0001

(or similar)
Great,,,thanks for the note