I am grateful for your help, but this must just be "above my pay grade." I still don't understand and don't want to waste your time. Thank you so much for this! It's perfect and solved a problem I was struggeling with for hours. SQL did not like the replace:Īrgument data type ntext is invalid for argument 1 of replace function.Ĭast(replace(replace(cast( MyDataField as nvarchar(max)),char(10), ''), char(13), '') as nvarchar(max)) as MyDataFieldĪmazingly the first result of google search, it worked perfect
#Excel split cells copying values code#
There is too much code then to re-write years of code.Īt the least ms should have an opt out option, add great but have op out. reverted back to 2005 for the moment (on the client). << cast(replace(replace(cast( MyDataField as nvarchar(max)),char(10), ''), char(13), '') as nvarchar(max)) as MyDataField I am suprice that data export in SSMS doesnt do this, shame on Microsoft Hi, this one line of code worked the best, saved my day Thanks so much brother, and keep up the great work. This time I wanted to take a second to actually thank you, the author, for the valuable information in this post, and to say that over 3 years later (currently ), people like myself are still finding this information useful.
Usually when I am researching something for work, I just find the information I need and move on. Thursday, Septem11:23:29 AM - Tariq Abulaila
This works perfectly in SQL 2016 and saved me hours of work trying to get the data extracted when it kept splitting lines. Just wanted to say thank you so much! I have queries I have to run several times for my users and some of the larger fields would split up. Thursday, Octo4:32:24 PM - Jessica A Brucks but in my office with same sql version in my coworker's machine it copied fine but only in mine it happend like that. csv file, opening Excel, and opening csv file with csv import wizard. N.B.: for unknown reason, this solution doesn't worked for me when saving query result into a. Excel is interpreting double quotes as text delimiter. SELECT '"' + cast(Count圜ode as nvarchar(MAX)) + '"'ġ/ Execute query to get result into a gridģ/ Open new Excel worksheet and paste data in it. Specifying MAX as nvarchar size was necessary otherwise the result was truncated to 30 char only. It is all about embedding the ntext field between double-quotes.Īs the add function (+) can't be used with ntext, we have to cast ntext into nvarchar anyway. :)ĭon't ask me why but the following workaround is also working fine and PRESERVING ntext content format. relieved of this issue that I have been suffereing with for so long. This was really helpful thanks a lot buddy.
Unfortunately this doesn't help when you want to retain the carriage returns in excel so that the field in excel appears as multi-line text in 1 cell, not multiple columns in excel, replacing with a space or a blank string obviously won't fix this. I have the same problem and couldnt get any reasonable answer in the internet. Tuesday, Ap10:53:06 AM - Evgeny Ponamarev Thanks for the solution, I also had the same problem and you solution is perfect. Clear the checkbox for "Retain CR/LF on copy or save" to get a multi-line result as a single line. Select "Tools - Options" from the SSMS menu and navigate to "Query results - SQL Server - Results to Grid". It is NOT necessary to rewrite the query, this behavior is just a setting in newer versions of SSMS.
0 kommentar(er)
