sql-serverexcelexcel-import

Excel does not recognize number cell after import from SQL

I have an SQL table (well, stored procedure to be exact), which has values like this:

enter image description here

I import this data in Excel as:

Data -> From Other Sources -> From SQL -> ... do the trick with editing the SQL statement of the connection to look into the stored procedure instead of View/Table -> Import Data -> Table

This works fine. The data is imported as:

enter image description here

Issue is, I can't SUM or Average (or any other math function for that matter) on those values! Something is wrong with them. When I copy such cell into another sheet I see this:

enter image description here

Excel does not recognize the field as a number. I have naturally tried the Cell -> Format -> Number/Accounting/Whatever... no success.

Any ideas

Edit:

Why isn't the following working:

enter image description here

Solution

  • It seems the stored procedure returns a string field instead of a numeric field. You can use Power Query to convert this to a number, or apply any number of transformations like joining with other sources, splitting columns, formatting, pivoting etc.

    Power Query is included in Excel 2016 or as a free addin for Excel 2013