1

So I'm trying to copy and paste some query results into Excel, and for some reason it seems to be not properly completing the first value.

Let me explain what I mean by this.

In Excel the cells I am copying into are formatted as Currency. Normally when you copy in value(s) or type it in and then press enter, it formats it properly with commas, dollar signs, decimals, etc, and then any formulas that use this cell are recalculated instantly.

However, when I copy in a set of values, the very first one does not get formatted, and it does not get calculated in a formula that uses that cell. The next 4 values work properly though, and if I try doing just one value at a time the problem still occurs.

At first I tried casting and converting the values to different SQL types like decimal, money, float, as I thought it was an issue with that, but it makes no difference.

In my actual query these values are SUMs of decimal fields, but here is an example query that does the same thing:

SELECT 123456.78, 9012.34, 567890.12, 345678.90, 123456.78;

Does anyone know what is causing this?

I'm pretty sure this is something to do with SSMS 2012 and not Excel, as it never happened to me in 2008. Not to mention there was another copy and paste issue I ran into earlier with SSMS 2012 involving carriage returns and line feeds that caused new rows when pasted into Excel that never happened in 2008.

Thanks

  • 1
    Try Paste Special as values in Excel. – Jon Seigel Apr 16 '14 at 3:20
  • 1
    Don't copy/paste, export to excel in one click spaghettidba.com/2014/01/16/… – spaghettidba Apr 16 '14 at 8:07
  • @JonSeigel the only options I get are Text and Unicode, unicode does the same thing, but text seems to work! – Mark Apr 16 '14 at 14:36
  • @spaghettidba I do have ssms boost installed, however I don't have office installed in my virtual box that has ssms on it, so it's too much of a hassle because I need to do this manually for many rows. I tried copying the values as excel, but when I go to paste it into excel it just puts in the xml as text. – Mark Apr 16 '14 at 14:39
1

If I do a Paste Special, I get two options, Text and Unicode, and selecting Text seems to work properly, where as Unicode does not. as per comment by @JonSeigel

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.