excel adodb query datatype issues

[RAYLWARD102]

I have a query that's been working great, until we challenged it, by providing different value types in one column.

lets say I have 5 rows of data in column A (each cell value contains: 22005) that I want to capture to recordset.
Query: SELECT F1 AS [instructions] FROM [Worksheet$]
The above query works returning 5 rows of 22005.

Now, if I have 5 rows of data, and the first row is equal to 22005 and the second row equal to apple and the rows afterwards, are 22005, the recordset will return all values except for the apple; apple will come back NULL.
If I put apple in the first row, same thing; will happen; Null is returned, but the other rows with 22005 are returned.
I'm not sure what the problem is, since I have other columns with apple, that all come back apple.
Any idea's why I'm getting some data back as NULL?

 

[RoryA]

ADO doesn't really like mixed data types (databases don't generally allow it). You can try and work round it by adding IMEX=1 to your connection string, but it's not foolproof by any means.

 

[RAYLWARD102]

ADO doesn't really like mixed data types (databases don't generally allow it). You can try and work round it by adding IMEX=1 to your connection string, but it's not foolproof by any means.

your right; seeing some other posts now, saying similarly. It's too bad you cant use cast in the select statement, to determine the column data type; in my case, I just want it return all as varchar

 

[RAYLWARD102]

Actually, adding IMEX=1, solved my issue. Any known issues with adding imex=1?

 

[RoryA]

It doesn't always work if the first instance of your mixed data type is not near the top of the data set.