Data type guessing using OLEDB for Excel

[ShreyansNahar1]

Hi, I am trying to read data from an excel file using OLEDB connection. My query is fetching the data, but the problem is that it is guessing the datatype of the column on the basis of the content of the data. For example, I have a column named PinCode where some values are pure numerics and other values are strings. When I use OLEDB to extract that, the default datatype of the column is being set as Number. I have changed the registry value of TypeGuessRows to 0, but it is not helping. Please suggest ways to solve the issue.

[John__Carter]

Try a query that casts the data as text. You'll need to look up the exact syntax online but ot will ne something like SELECT CAST([My Number] AS varchar(10)) AS [My Text]

[Anonymous]

Hi John Carter, cast is not working... Internal : Could not execute code stage because exception thrown by code stage: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

[Anonymous]

try this one SELECT str([PinCode]) AS [New_Column_name] FROM [Sheet_name$] hope this helps 🙂

[ShreyansNahar1]

Hi John, Cast does not work for converting datatype. Also, Mayur's suggestion works when we have a limited number of columns. If we have a huge list of columns, we will have to write all of them in the query just to convert 1 column's datatype. For example, SELECT A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Str(Q) AS [ConvertedQ], R, T FROM [Sheet1$]. These are just temporary column names (A, B, C, etc.) for example. Is there any other possibility?

[John__Carter]

As I said, you needed to look up the syntax and Mayur kindly provided it. Yes you will have to explicitly state which columns are to be converted, that's just how queries work.

[Ansar_AhmedAtta]

Hi  str([columnName]) makes the incoming value as text, is there a way to make the incoming value as date? Thanks.