cancel
Showing results for 
Search instead for 
Did you mean: 

Data type guessing using OLEDB for Excel

ShreyansNahar1
Level 4
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.
6 REPLIES 6

John__Carter
Staff
Staff
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
Not applicable
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
Not applicable
try this one SELECT str([PinCode]) AS [New_Column_name] FROM [Sheet_name$] hope this helps 🙂

ShreyansNahar1
Level 4
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
Staff
Staff
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
Level 4
Hi  str([columnName]) makes the incoming value as text, is there a way to make the incoming value as date? Thanks.