How is SQL Prompt helping your team? Share your experience.

Export To Excel Functionality

Surya456Surya456 Posts: 1 New member
Hi Experts,

Looking to resolve an issue on an export to excel where all columns are exported as text.
This started when i upgraded to Version 10.12.3.28623.
Tagged:

Answers

  • Hi Surya,

     There were recent changes to the way information is exported to excel during the latest release but it looks to be just the way BIGINT is handled.


    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.

     

    What is the DataType that is being exported as text to excel so we can attempt to recreate it on our end?

    Best,

    Dustin 

  • jsirgjsirg Posts: 6 Bronze 2
    edited September 12, 2022 9:38PM
    I'm also having this issue, i've noticed it mostly with money fields (as those are the ones I want to add up in excel).  I'm also using the same release, I thought it was only happening to me, glad to not be alone.
  • jsirgjsirg Posts: 6 Bronze 2
    I believe you may have tried to fix this issue with the latest version: 

    10.12.5.30313 - Released on 28 September 2022

    Fixes

    • Fixed an issue where the SQL Prompt View in the SQL Compare diff window was not working.
    • 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.

    But I just updated and when i export to excel, its still sending my money fields out as Text in excel
  • Same issue, and the update did not correct the issue of money fields being exported to text fields in Excel.  I just updated to 10.12.5.30313.
    Dan V, P20 Global
  • BeegeBeege Posts: 6 Bronze 2
    There was another thread similar to this. As of release 10.12.6.30951 this should now be fixed.
  • pellstarpellstar Posts: 1 New member
    edited October 27, 2022 11:22AM
    Hi, I am experiencing the issue of numeric data outputting as text as well even after updating to version 10.12.6.30951 also a new problem has cropped up in the latest patch where each export reports there is a problem in the content. "We found a problem with some content in the Exported Results yyyy-mm-dd" 

    This appears to be an excel message but only occurs on opening an exported file from SQL prompt. 
    Please advise   

    Further information from xml error file 
    <recoveryLog>
     <logFileName>error205840_01.xml</logFileName>
     <summary>Errors were detected in file 'C:\Users\pells\AppData\Local\Temp\xrvgmqno.ld0\Exported results 2022-10-27_12-11-50.xlsx'</summary>
    <repairedParts>
     <repairedPart>Repaired Part: /xl/worksheets/sheet1.xml part with XML error. Catastrophic failure Line 1, column 0.</repairedPart>
     </repairedParts>
     </recoveryLog>
  • SBuckVRTSBuckVRT Posts: 4 New member
    Hello - I am still experiencing this issue on SQL Prompt version 10.13.31417.  SSMS version 18.12.1.    Fields are all varchar.  

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <!-- Created with Liquid Studio 2021 (https://www.liquid-technologies.com) -->
        <logFileName>error061400_01.xml</logFileName>
        <summary>Errors were detected in file 'C:\Users\SBUCK\AppData\Local\Temp\qajqgaai.rrl\Exported results 2022-11-29_11-45-46.xlsx'</summary>
        <repairedParts>
            <repairedPart>Repaired Part: /xl/worksheets/sheet1.xml part with XML error.  Catastrophic failure Line 1, column 0.</repairedPart>
        </repairedParts>
    </recoveryLog>
  • ndmobilndmobil Posts: 2 New member
    SBuckVRT said:
    Hello - I am still experiencing this issue on SQL Prompt version 10.13.31417.  SSMS version 18.12.1.    Fields are all varchar.  

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <!-- Created with Liquid Studio 2021 (https://www.liquid-technologies.com) -->
        <logFileName>error061400_01.xml</logFileName>
        <summary>Errors were detected in file 'C:\Users\SBUCK\AppData\Local\Temp\qajqgaai.rrl\Exported results 2022-11-29_11-45-46.xlsx'</summary>
        <repairedParts>
            <repairedPart>Repaired Part: /xl/worksheets/sheet1.xml part with XML error.  Catastrophic failure Line 1, column 0.</repairedPart>
        </repairedParts>
    </recoveryLog>
    Same here --- exporting to excel when the results include numerics/decimals yields the quoted error.
  • ndmobilndmobil Posts: 2 New member
    FYI,

    this seems to be related to the regional settings in windows -- more precisely the decimal separator and grouping symbol.
    Setting them to the US standard of . for decimal and , for digit grouping (for me in germany it would be the other way round) "fixes" the problem.

     
Sign In or Register to comment.