none
Export a SQL Server Result Set from SSMS to Excel

    질문

  • I am attempting to export a SQL Server result set to Excel and one of the data columns is a 26-character Tracking number. When I copy the data from SSMS and then paste it into Excel, it is treating the Tracking Number as a number and putting it into like scientific notation and making it unreadable.

    How can I get around this?

    Thanks for your review and am hoping for a reply.

    2018년 5월 18일 금요일 오후 3:51

모든 응답

  • Expand your excel column a little bit.

    Or you can convert your number as varchar in your query.

    2018년 5월 18일 금요일 오후 3:57
    중재자
  • Are you trying to export using wizard or SSIS OR just manual copy paste? 

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2018년 5월 18일 금요일 오후 3:59
  • I am attempting to export a SQL Server result set to Excel and one of the data columns is a 26-character Tracking number. When I copy the data from SSMS and then paste it into Excel, it is treating the Tracking Number as a number and putting it into like scientific notation and making it unreadable.

    How can I get around this?

    Thanks for your review and am hoping for a reply.

    Thats the standard way Excel converts big number

    you just need to right click on the cell and choose Format cells option

    Then in the available options choose Number with decimal places 0

    see below


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 18일 금요일 오후 4:30
  • Hi,

    Before you copy data from SSMS to Excel, First highlight all column by clicking on the left corner and then change the format to "Text". Then paste all records from SSMS to excel, entire data will be copied in the same format as you see in SSMS.

     

    Hope it helps!

    Thanks,

    Alambir


    Thanks, Alambir Please vote this as helpful or Mark as answered if this helps!

    2018년 5월 18일 금요일 오후 4:39
  • Adjust SSMS Settings

    1. Go to Tools->Options
    2. Query Results->SQL Server->Results to Grid
    3. Check “Include column headers when copying or saving results”
    4. Click OK.
    5. Note that the new settings won’t affect any existing Query tabs — you’ll need to open new ones and/or restart SSMS.

    Now next time you run a query, do this

    1. Make sure the results are displayed in a grid (CTRL+D or Query->Results To->Results to Grid)
    2. Right click in the grid, and click Select All
    3. Right click in the grid again & click Copy
    4. Open up a new Excel spreadsheet, and paste the data in
    5. Do a global search & replace, replacing “NULL” with an empty string.
    2018년 5월 18일 금요일 오후 6:05
  • That is Excel "helping" you by autotyping your column.

    After you paste the column, click the column and change it to Text, and paste again.

    2018년 5월 18일 금요일 오후 6:33
    중재자