March 30, 2015 at 7:41 pm
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.
March 31, 2015 at 1:36 am
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
March 31, 2015 at 2:54 am
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.
March 31, 2015 at 4:23 pm
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. 🙂
March 31, 2015 at 4:24 pm
coetzeehuman (3/31/2015)
Hi AdrSHLWhen 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)?
March 31, 2015 at 4:58 pm
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);March 31, 2015 at 5:33 pm
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... 🙁
March 31, 2015 at 5:41 pm
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);May 4, 2015 at 4:39 pm
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... :/
May 5, 2015 at 5:30 am
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.
May 5, 2015 at 5:37 am
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
July 25, 2017 at 6:33 am
This was removed by the editor as SPAM
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply