Open in Excel Numbers to String

dkusleika

When I right-click in the grid and choose Open in Excel, numbers are no longer converted to numerics in Excel. Now they are left-justified strings with the little green triangle warning that numbers are strings. This just started happening. I'm not sure exactly when, but I updated SQL Prompt last Friday. I'm running 10.12.3.28623 in SSMS 18.12.1

Jon_Kirkwood

Just a quick update - this is active in our developers current sprint. 

There isn't a confirmed patch release version just yet; but it is underway and look forward to having some more concrete details shortly

Jon_Kirkwood

Hi @dkusleika  

 Thank you for reaching out on the Redgate forums regarding this export to Excel problem in SQL Prompt.

 

I can certainly see there were some changes made in v10.12.3 and sorry if this has caused an issue with your usage of the functionality.

 10.12.3.28623 - Released on 29 July 2022
Fixes
Fixed an issue where BIGINT data types were being exported to Excel in scientific notation.
Fixed a performance issue where typing in SSMS was laggy when large snippets are present.
Fixed an issue with an incorrect execution warning when using the ALTER TABLE syntax.
Added support for some SQL Server 2022 syntax, including LEDGER.
Fixed a crash that could occur when running the command line formatter.

 

Are you able to please provide a screenshot of the output and confirm the data type you are using for the number stored in SQL Server.
This will assist in replicating your issue closely and then provided to our development team to look into a solution.

 

In the interim I would suggest downgrading to v10.12.2 to see if the output is coming out as expected

https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.12.2.28458.exe

dkusleika

Interesting. Most of the non-integer numbers in my ERP are DECIMAL(15,2), but I thought I'd simplify it for testing purposes.

SELECT 1



Shows as numeric in Excel. A larger integer

SELECT 1234



Same. A decimal

SELECT 1.2



Comes in as text (left justified, green triangle in the top left). Maybe the decimal point is triggering something.

Without the decimal, it appears that 10-digit integers come over as numeric and 11 and up come over as text.

dkusleika

FYI, downgrading worked. Thanks.

Jon_Kirkwood

Thank you for your testing and confirming that downgrading does work, I shall replicate it and handover to our developers to address as a potential bug.

I am unable to confirm any resolution timeframe at this point but I would suggest monitoring our release note page for changes in v10.12.4 onwards
https://documentation.red-gate.com/sp/release-notes-and-other-versions/sql-prompt-10-12-release-notes

Pud

Hey Jon
Unfortunately I have the same issue and now downgraded SQL Prompt.  Sounds like someone's forgotten a semicolon

Beege

I had the same issue with version 10.12.3.28623. Downgrading to version 10.12.2.28458 allows me to export to excel correctly again. 

Version 10.12.3.28623 noted that a fix with exporting BIGINT data types was changed with that version. I suspect something done with that had broken the export of decimal and int to be treated as a string in excel.

For those not wanting to downgrade, exporting the results as csv still works, just adds a little more of a hassle.

Jon_Kirkwood

Thank you to everyone who has advised they are having issues with the output to Excel functionality. 

Our development team have confirmed they are working on a few reported problems in this functionality in v10.12.3. 

I shall endeavor to update this post when we have a confirmed release that resolves these issues.

Otherwise, please keep an eye out for changes in v10.12.4 and onwards:

https://documentation.red-gate.com/sp/release-notes-and-other-versions/sql-prompt-10-12-release-notes

 

 

 

Stalker

Same problem. Exporting multiple decimal(18, 2) and int. INT comes out as number, decimal - as text.

Alberto

Same issue here, solved by downgrading to the 10.12.2.28458 version.

Was the "Fixed an issue where BIGINT data types were being exported to Excel in scientific notation." a real issue? It seems to me that the scientific notation was only a display issue, for long BIGINT numbers with the default Excel font and cell width. Enlarging the column showed the whole number. This works in 10.12.2.28458

Jon_Kirkwood

Thank you for your ongoing patience, this is still an open and known issue with SQL Prompt v10.12.3 

Our developers are working on a larger release of SQL Prompt which has delayed the resolution of this concern but I have been advised that it is an active item to be resolved.

Workaround continues to be downgrading to v10.12.2 whilst this is being worked on.

alphonseg

I'm having the same issue, though I only noticed this recently because my automatic updates have been failing. I just updated to 10.12.4.29949 and the issue still exists.

In Excel one can use Data tab > Text to Columns to correct the formatting.

jsirg

I updated to the new version today and the release notes make it sound like its fixed, but I'm still having the issue with money fields.

Pud

As @jsirg stated "I'm still having the issue with money fields", I too have the same problem.

Alberto

Same issue here with version 10.12.5.30313 and DECIMAL(28, 12) fields. See attached screenshot

alphonseg

I just updated to the latest version, and yes, the issue is still there. However, the release notes do not indicate that it is fixed. It states 'Fixed an issue where Export to Excel was always using a dot as the decimal separator instead of the using the one set by the current locale'.

Jon_Kirkwood

Hi all, thank you for the comments & updates on your testing of SQL Prompt patch releases.

 I have also reviewed with our developers and the fix included in v10.12.5 was around the usage of a decimal separator. It was defaulting to a US standard and has been changed to match the users current locale

• Fixed an issue where Export to Excel was always using a dot as the decimal separator instead of the using the one set by the current locale.

 

Unfortunately I have to report that our development team have re-reviewed the decimal data type output to Excel concern and, with their current workload and available resources, have made the decision that this is not an item they are able to provide a fix for at this time.

As mentioned by @alphonseg there are workarounds within Excel are to use the Data > Text to Columns ribbon-menu option or right-click > Format Cells... context-menu options to change the formatting on these cells.

 

Apologies for any inconvenience this may cause with exporting to Excel; and whilst we cannot commit to a fix at this time it is possible that changes may occur in future releases.

alphonseg

While the Text to columns is a workaround, it can be time consuming. It needs to be applied to each column separately and if there are many number columns, it can be a real PITA. As a developer, I don't see why it should be so difficult to compare the Excel output section of the current version with the version from several months ago, see what changed and fix it.

jsirg

I could see leaving a known bug in a free tool, but as a tool we pay for (and worked 3 releases ago), I think this is a pretty sh#tty take.

ATurner

Hi All

It is not a bug in the current release, it was a deliberate change made to be consistent with how it looks within SSMS. As mentioned you can change the field to be a number if that is required by selecting the column and changing the type.  

alphonseg

That doesn't make sense. Why would I want Excel to look like SSMS? We output to Excel to be able to work with the data and/or format it for reporting, etc. Exporting numbers as non-numbers makes that more difficult. Whoever thought that was a good idea should rethink it.

dannyvpa

I agree with Alphonseg and Jsir that the export to Excel functionality has worked as expected for many years and it is one of the primary functions we utilize SQL prompt everyday.  I understand that you tried to fix it so that it worked with better with different locale settings but this broke working functionality in paid product. 

I am disappointed that this isn't a priority to fix a bug that was introduced in the August release.  The workaround isn't really feasible for many.

Alberto

I definitely agree with @alphonseg: a number-typed column returned by a query should be exported in Excel as a column containing a number, especially when any version older than a month works perfectly. As I wrote on September 15, it seems to me that the original "bug" (BIGINT columns shown in exponential notation) was a simple matter of how Excel treats contents wider than the cell's width: a long text gets truncated, a "long" number gets expressed in a more compact notation

Beege

Just to chime in here too, I do also use this ability on a daily basis. It's disappointing to hear that there are no plans to fix this in the near future soon after switching my license from the old license method to the new subscription model license. 

I won't be updating SQL Prompt until I've heard that this has been fixed. There is no feature or change that would make it worth it to do the 'workaround' to export. I love this product, have used it for many years, and have been an avid champion and fan for RedGate to all those I interact with. 

I would beg and plea with you to reconsider the priority for addressing fixing this bug that was introduced after 10.12.2.28458. The ability to simply export to excel easily is a critical feature for me.

Please reconsider addressing the issue.

dkusleika

I am also disappointment that this obvious bug will not be fixed. Like Beege, I will be forever on version 10.12.2, which limits the value of the product.

ATurner

Hi All

To confirm it isn't a bug in the current version it was a deliberate change to have the information in the excel look as it does within SSMS. If you require them to be numbers they can be multi selected and changed. Would recommend adding your name to the uservoice to change this https://redgate.uservoice.com/forums/94413-sql-prompt/suggestions/45753061-restore-the-working-open-in-excel-functionality

dannyvpa

ATurner if that was the goal of the change, then there is still a bug because the Excel output doesn't look like it does in SSMS.  The money fields which are display two decimals in SSMS are change to 4 decimals in the Excel output



I think we all understand that Redgate did this intentionally but making such a change on a minor update seems risky and to many of the users not well documented.  I think the change document stated is respected the locale setting for the decimal system.  The change actually changed a number field with decimals in the SSMS output into a text field in the Excel output.  I can only speak for myself but that isn't this.

From this feedback you can see it's a change most of us didn't ask for, expect or find useful.  In fact, this change has made our job harder not easier, which is why we are using SQL prompt.    

alphonseg

I'm still at a loss to understand how anyone could think that this change made sense.

Why would anyone want Excel to handle numbers as text???

jsirg

@Aphonseg only thing i can think of is they didnt want large numbers to show as scientific notation until you widen the field, still doesnt make sense to break all numbers to fix one little issue.

dkusleika

I don't see in the release notes where this is a deliberate change. They made a change regarding BIGINT and scientific notation, but there's nothing in there about decimals turning to text. So if you don't think it's a bug, you must have some information or access that I don't.

But regardless if it's a feature or a bug, I don't like it and it will prevent me from upgrading until they add some feature that trumps the loss of this one.