Copy information from SQL to Excel without VB (just plain copy/paste) issue...

Question

Copy information from SQL to Excel without VB (just plain copy/paste) issue...

AdrSHL

SSC Enthusiast

Points: 147
More actions
March 30, 2015 at 7:41 pm

#296522

Hi there,
I am doing some normal Selects on a database (SQL 2008), and I want to copy using copy/paste the result of that information onto a spreadsheet. All normal so far.
The problem is that some fields contain the Chr(13), and I want to keep them, meaning, when I paste the value of that field onto a cell on Excel (2013) it is like Excel is ignoring the return character, and pastes everything into one line, when I want something on that cell to be like after pasting:
Line 1 of text
Line 2 of some more text
Line 3
and not what I have now
Line 1 of text Line 2 of some more text Line 3
When I do a charindex(char(13),Field) on the Select command, it comes up with results, so the Chr(13) is there... multiple times in some cases.
Can anyone point me in the right direction? I don't want to VB code on excel because of shortage of time, and I'm saying this without even knowing if with VB I would be able to overcome this issue... 🙂
Thanks.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

There's an add-in for SSMS that will script out grid results to an excel file in a couple of cliks. You can find it here: http://www.ssmsboost.com

-- Gianluca Sartori

coetzeehuman SSC Veteran Points: 217 More actions · Answer 2

Hi AdrSHL

When you paste the data into Excel, select all the data and go to Data->Text To Column. Select Delimited, click Next. Select the correct delimiter, if it is a special one type it in the box, and make sure you unpick everything else.

Hope this helps you.

AdrSHL SSC Enthusiast Points: 147 More actions · Answer 3

spaghettidba (3/31/2015)
There's an add-in for SSMS that will script out grid results to an excel file in a couple of cliks. You can find it here: http://www.ssmsboost.com

Thx spaghettidba, but I can do it with VB and SQL coding, I just wanted an easy copy-paste "fix" of this issue, for the situations I just want a quick paste of a cell onto a spreadsheet. 🙂

AdrSHL SSC Enthusiast Points: 147 More actions · Answer 4

coetzeehuman (3/31/2015)
Hi AdrSHL
When you paste the data into Excel, select all the data and go to Data->Text To Column. Select Delimited, click Next. Select the correct delimiter, if it is a special one type it in the box, and make sure you unpick everything else.
Hope this helps you.

Thanks for the tip coetzeehuman, but now I'm stuck as what do I type in the "other" box on the Convert Text to Columns wizard, since the delimiter is a return (Chr13)?

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

If this is copying and pasting one cell from SSMS to one cell in Excel, then make sure you are in edit mode in Excel before pasting, either by pressing F2 after clicking on the cell or by clicking twice, slowly - don't double-click.

You will know you are in edit mode by the flashing cursor inside the cell in Excel.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

AdrSHL SSC Enthusiast Points: 147 More actions · Answer 6

mister.magoo (3/31/2015)
If this is copying and pasting one cell from SSMS to one cell in Excel, then make sure you are in edit mode in Excel before pasting, either by pressing F2 after clicking on the cell or by clicking twice, slowly - don't double-click.
You will know you are in edit mode by the flashing cursor inside the cell in Excel.

Thank you mister.magoo (nice nickname btw) but still same result.

Tried pasting it into Word, and same thing, so I'm getting the impression it's a SQL 2008 issue on the "copy" side of things...

If there is VB code executing the select and pasting of the resulting recordset, then the Chr13 are "respected"... just when Ctrl-C & Ctrl-V are used, it doesn't work... 🙁

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

AdrSHL (3/31/2015)
mister.magoo (3/31/2015)
If this is copying and pasting one cell from SSMS to one cell in Excel, then make sure you are in edit mode in Excel before pasting, either by pressing F2 after clicking on the cell or by clicking twice, slowly - don't double-click.
You will know you are in edit mode by the flashing cursor inside the cell in Excel.
Thank you mister.magoo (nice nickname btw) but still same result.
Tried pasting it into Word, and same thing, so I'm getting the impression it's a SQL 2008 issue on the "copy" side of things...
If there is VB code executing the select and pasting of the resulting recordset, then the Chr13 are "respected"... just when Ctrl-C & Ctrl-V are used, it doesn't work... 🙁

It definitely works for me...

I did this:

select 'hello

world';

Then Ctrl-C on the cell in the SSMS grid and paste into Excel I get either two cells populated or (if I enable edit mode first) one cell with two lines.

Does it paste correctly in Notepad?

If not does it output correctly in SSMS with the query results output to text rather than grid?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

AdrSHL SSC Enthusiast Points: 147 More actions · Answer 8

Nope, pasting into Notepad, Word or Excel all comes out with the same undesired result.

I just got sick of it, wrote VB on Excel to pull the information straight from the database with whatever query I want, and voilá :), it populates the spreadsheet with all the correct Chr(13).

Didn't want to waste time doing it, but ended up doing it... :/

SDG1 Say Hey Kid Points: 691 More actions · Answer 9

I had the same issue with one of my tables. My problem was caused by line breaks and tabs embedded in the cell. I found this after pasting it to word and turning on the formatting marks. I had to adjust my query using REPLACE to take those out. After that it pasted beautifully.

spaghettidba SSC Guru Points: 105732 More actions · Answer 10

Really, I can't understand why you feel that fiddling with the data is better than using a simple add-in that does all the work for you.

-- Gianluca Sartori