Convert string column to numeric in Excel via connection string

Question

I'm having a big problem getting data from Excel files via connection String. I connect to xls file and execute this query:

SELECT CDbl(COLUMN_NAME) FROM [SHEETNAME$]

when COLUMN_NAME references a string column (or numeric column with empty cells), it fails. There is any solution? like "ISNULL(COLUMN_NAME, 0)" or someting like that???

Thanks!

Our Man in Bananas · Accepted Answer · 2013-06-26 09:06:09Z

1

Add the property IMEX=1 at the end of your connection string of the Excel connection manager.

Samples:

http://www.connectionstrings.com/excel

This will treat mixed data types as string. However, Excel will only scan the first 8 rows to determine if there are intermixed data types. In order to change that, you need to modify the TypeGuessRows registry setting for the JET provider. If you set it to 0, it will scan all rows.

Here are more references:

answered Jun 26, 2013 at 9:06

Our Man in Bananas

6,00525 gold badges104 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Agustin Seifert Over a year ago

Yes, I know the property but my problem is that I NEED to manage this columns like number (currency). So my idea is to force the column type to numeric or another one but NO string. Thank you anyway!

Collectives™ on Stack Overflow

Convert string column to numeric in Excel via connection string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related