OLEDB - Data type mismatch in criteria expression

[DipinDevP]

Hi,

I was trying to fetch data from an Excel using OLEDB with below query but getting an error - Could not execute code stage because exception thrown by code stage: Data type mismatch in criteria expression.

SELECT [No],[Resp (3&4)] FROM [Call Sheet$] WHERE [Resp (3&4)] <> ''

Columns: No, Resp (3&4)

Sheet Name: Call Sheet

Can anyone help with possible reasons of this error and suggestions to resolve it.

Note: Similar query with other column working fine.

Eg: SELECT [No],[Resp 2] FROM [Call Sheet$] WHERE [Resp 2] <> ''

Thanks,

Dipin Dev P

[John__Carter]

By default OLEDB determines the datatype of each column based on the first few (10 I think) rows. However this guesswork can be a problem if the first rows are not representative of the whole column. For example, if the file has 100 rows and in column A rows 1-10 are numeric and 11-100 are alphanumeric, OLEDB will assume the column contains only numeric data. This isn't a BP thing, it's how OLEDB works and it's a common frustration for developers.

Your query assumes [Resp (3&4)] is text, and the error is saying it can't compare the data type it has assumed for that column with an empty string (<> ''). Have a look at the data in the top rows of that that column.