Question AdHoc - Excel File Column Type.

[JaedenRuiner]

If anyone has ever utilized the AdHoc query notation to view an Excel Document (or more likely to import one into a DataBase table) there is often a notice of distinct annoyance with regards to how the AdHoc services treat the columns in the Excel workbook when correlating them to "Database" columns. Often times, the Adhoc services get a bit confused. For example, there is a column for Zip Codes in an Excel Spread Sheet, where the vast majority of these codes are a 5 digit numeric, (some of which start with 0), where as some for Canada, are a 7 character (3 spc 3) notation. the issue, is that for some reason the AdHoc command OPENROWSET() does not pre scan the excel "pseudo-table" first, and thus automatically assumes that the column is of Type Float.

This discrepancy then causes all of the Canadian Zip Codes to be returned as NULL, instead of all the numeric Zip code being treated as an NVarChar() as with other text based columns. I have tried highlighting the entire column, and altering the "format" in Excel, and then saving the document, but when i try to reload the document via an AdHoc query it sill believes that the Zip Code column is a Float.

Is there any way to manipulate the AdHoc request, or to internally flag the Excel document, to cause AdHoc queries to Treat ALL columns as Text in the Database side, allowing me the ability to use Convert() function on those columns that need to be treated as numerics, while allowing the columns that should be treated as Text remain as Text instead of returning a NULL for entries I need?

Thanks