Maximize the power of SQL Prompt with SQL Toolbelt Essentials. Watch now.

Open in Excel Numbers to String

dkusleikadkusleika Posts: 4 New member
in SQL Prompt
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
Tagged:
· Share on Twitter

Answers

  • Jon_KirkwoodJon_Kirkwood Brisbane, Australia Posts: 97 Bronze 2

    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

    Jon Kirkwood | Technical Support Engineer | Redgate Software
    · Share on Twitter
  • dkusleikadkusleika Posts: 4 New member
    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.

    · Share on Twitter
  • dkusleikadkusleika Posts: 4 New member
    FYI, downgrading worked. Thanks.
    · Share on Twitter
  • Jon_KirkwoodJon_Kirkwood Brisbane, Australia Posts: 97 Bronze 2
    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
    Jon Kirkwood | Technical Support Engineer | Redgate Software
    · Share on Twitter
  • PudPud Posts: 2 Bronze 1
    Hey Jon
    Unfortunately I have the same issue and now downgraded SQL Prompt.  Sounds like someone's forgotten a semicolon :smile:
    · Share on Twitter
  • BeegeBeege Posts: 3 Bronze 1
    edited August 4, 2022 5:00PM
    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.
    · Share on Twitter
  • Jon_KirkwoodJon_Kirkwood Brisbane, Australia Posts: 97 Bronze 2

    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

     

     

     

    Jon Kirkwood | Technical Support Engineer | Redgate Software
    · Share on Twitter
Sign In or Register to comment.