0

The title is a little confusing but essentially I know what is happening just don't know how to prevent it from happening.

When I copy the following column from SQL Server: (the data is all in one column)

Rows | Column A
Row1 | 1. Description: Customer 2. Replicate issue: recreated 3. Other issues: no other

and paste it into Excel it looks like this:

Rows | Column A
Row1 | 1. Description: Customer 
Row2 | 2. Replicate issue: recreated 
Row3 | 3. Other issues: no other

I would like the paste to be identical to the column from SQL Server, however I do not know how to remedy this...I have toyed with some text to columns stuff but 1) I don't know what I'm doing there and 2) I don't think that is it....

I have also tried saving as a .txt file and import it that way and no dice.

Thanks in advance!

Ridiculoushavoc
  • 13
  • 1
  • 5
  • 1
    There is no SQL Server **2013** - we have **2012** or **2014** - take your pick – marc_s Apr 15 '14 at 20:24
  • Possible duplicate of [Copy And Paste From SQL Server Management Studio 2012 New Line Issue Into Excel](http://stackoverflow.com/q/13380779/74124) – THelper Sep 01 '15 at 08:07

2 Answers2

0

Try like this.

This is working for me.

select top 10 replace([Column], CHAR(10),', ') as [AliasName] FROM TableName

sekar perumal
  • 41
  • 3
  • that worked....i know it took me awhile to comment back :) Thanks!!! I had just dealt with it until I figured it out, and came back here to update and saw this. Thanks – Ridiculoushavoc Mar 11 '15 at 13:55
0

As Sekar mentioned above, that works. In addition I have found that by using: replace(replace(replace([Column],CHAR(9),'',''),CHAR(10),'',''),CHAR(13),'','')

Replaces all of the carriage returns (which is what was causing my issue) with a single space. Thus when I paste into Excel, issue resolved. Originally I had figured out the CHAR(10) and CHAR(13) workaround when I start seeing it again :(

That's when I cam across the CHAR(9) workaround as well as the following information posted the other day on Microsoft Connect

Posted by JayakumarPanasonic on 3/7/2015 at 3:14 AM

IT IS A BUG AND MS IS STILL SILENT.

Yesterday whole day I spent to extract some data form SQL2012, where I faced same issue. Used CHAR(9), CHAR(10), and CHAR(13) to resolve it temporarily. Really surprised, why MS is taking this so lite and not fixed yet. It used to work fine with SQL2008. Give option SQL2012 configurable through: Tools | Options... or provide additional options, while pasting data in Excel.

Community
  • 1
  • 1
Ridiculoushavoc
  • 13
  • 1
  • 5