0

A lot of the front end user's don't give a rat's ass about data input quality. My SSMS query retrieves the data beautifully with the correct number of rows output, as expected. enter image description here

On pasting into Excel you get a different number of rows and the set needs playing around with. I believe it's because the data input in that field is weird / non-standard.

enter image description here

Is the only way to fix this editing the source data or making a big exception clause? I don't want to go via csv and text to columns either.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
Dasal Kalubowila
  • 103
  • 5
  • 22
  • 1
    You can't copy from SQL Server, it's a *server*. You can only copy from client applications. Are you talking about SSMS perhaps? Why *copy* when you can *export* to a CSV directly from the results, or use the Export wizard to actually produce Excel files? Or use a query in Excel to load the data instead of copying it? – Panagiotis Kanavos May 11 '18 at 10:08
  • 1
    As for this specific problem, the text probably contains newlines. Any method *except* copy/paste would put the entire content in a single cell. When copy/pasting though Excel has no idea that this newline should be ignored while that newline means there's a new line. Try "Paste Special > Text" to strip formatting characters while pasting – Panagiotis Kanavos May 11 '18 at 10:12
  • The way I work is get the data from management studio, paste into excel. Do some analysis and tell the client. I do this hundreds of times a day. My excel book is just called book1 with loads of sheets. I don't even save it. Secondly, using an excel sql query editor or doing it through Connections sql command is not a good solution to my problem – Dasal Kalubowila May 11 '18 at 10:14
  • Actually it is. The way you work is the actual problem. Why use *SSMS* to query? Why repeat the process each time when Excel has an excellent querying and cleanup mechanism with PowerQuery? Create the query *once* and just hit F9. Anyway, this question isn't about programming, it's about pasting into Excel. – Panagiotis Kanavos May 11 '18 at 10:18
  • 1
    If you don't want to do anything else, use [REPLACE](https://learn.microsoft.com/en-us/sql/t-sql/functions/replace-transact-sql?view=sql-server-2017) in your query to replace newlines with spaces or remove them altogether, [as shown in this question](https://stackoverflow.com/questions/951518/replace-a-newline-in-tsql) – Panagiotis Kanavos May 11 '18 at 10:19
  • It's not one query though. I write a whole load every day. Are you saying that I should do away with management studio to get around this problem? – Dasal Kalubowila May 11 '18 at 10:21
  • Many thanks Panagiotis! That REPLACE will work for me. – Dasal Kalubowila May 11 '18 at 10:26
  • also pal, do you know how I can find out which records have newlines? – Dasal Kalubowila May 11 '18 at 10:30

0 Answers0