topic Hi … in Product Forum

Data type guessing using OLEDB for Excel

ShreyansNahar1 — Tue, 19 Sep 2017 18:22:00 GMT

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.

Try a query that casts the

John__Carter — Wed, 20 Sep 2017 13:20:00 GMT

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]

Hi John Carter,

Anonymous — Wed, 04 Oct 2017 14:47:00 GMT

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).

try this one

Anonymous — Wed, 04 Oct 2017 15:47:00 GMT

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

Hi John, Cast does not work

ShreyansNahar1 — Fri, 06 Oct 2017 14:03:00 GMT

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?

As I said, you needed to look

John__Carter — Fri, 06 Oct 2017 18:30:00 GMT

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.

Hi …

Ansar_AhmedAtta — Tue, 27 Feb 2018 10:44:00 GMT

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