Post reply

Copy query result to excel

  • July 3, 2012 at 3:15 pm

    #277385

    When I run a query in SQL management studio query window, I tried to copy the result to an excel file, but if I copy columns like a grade range, k-5, 09-12, 6-8, to an excel file, some data changed to month-day format, like 12-Sep, 8-Jun.

    I am using excel 2007.

    How can you remove the format and keep the orginal data like 09-12, 6-8?

    Thanks,

  • July 3, 2012 at 3:19 pm

    #1508507

    Change the format on the cells. If this is in a specific column, you can do it to the whole column by highlighting the column, right click the column, format cells, select the format you want.

  • July 3, 2012 at 3:20 pm

    #1508509

    it's an excel thing: simply highlight all the cells, right click and Choose format Cells...

    the popup window choose "Text" fromt e available options.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 3, 2012 at 3:30 pm

    #1508515

    I tried many times, that is not working as supposed to.

    For example the orginal is 3-5, then when I copied to Excel, it becomes 5-Mar, then if I right click and format to text, it changes to 40973.

    I know this is an excel question, but I think many SQl developers may come cross copy query result directly to an excel file, and may have experience of that, so ask here to see if anyone has any ideas.

  • July 3, 2012 at 3:35 pm

    #1508521

    In some cases I have actually had to manually correct data that I have cut and pasted to excel. Just something that comes with the territory.

  • July 4, 2012 at 10:58 pm

    #1508910

    Another, albeit awkward but likely method to achieve this is to convert the column containing 6-8 to a computed column like this:

    SELECT GradeRange='''' + GradeRange

    When you paste that result into Excel, because of the prefixed apostrophe, it should insert into the cell as a character string (hiding the apostrophe).

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • July 5, 2012 at 9:29 am

    #1509183

    Thanks, could I ask why it is 4 ', like ''''?

    Thanks

  • July 5, 2012 at 5:18 pm

    #1509429

    sqlfriends (7/5/2012)

    Thanks, could I ask why it is 4 ', like ''''?

    Thanks

    Two surrounding apostrophes to denote the character string. Because apostrophe is the character string definition operator, you must use two inside the apostrophes to signify one. This is basically the same in any programming language.

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • July 5, 2012 at 5:31 pm

    #1509433

    dwain.c (7/5/2012)

    sqlfriends (7/5/2012)

    Thanks, could I ask why it is 4 ', like ''''?

    Thanks

    Two surrounding apostrophes to denote the character string. Because apostrophe is the character string definition operator, you must use two inside the apostrophes to signify one. This is basically the same in any programming language.

    is that the 3rd ' works like an escape?

    Thanks

  • July 5, 2012 at 5:35 pm

    #1509434

    I just tried it, when I paste to excel, it still has the appostrope, like '9-12

    It is not hiding the ', also tried to paste special as text, same result.

  • July 5, 2012 at 6:15 pm

    #1509444

    sqlfriends (7/5/2012)

    I just tried it, when I paste to excel, it still has the appostrope, like '9-12

    It is not hiding the ', also tried to paste special as text, same result.

    You can then do a "replace all" apostrophes in that column with a null string.

    In truth I hadn't tested it so I wasn't sure it would work. Just a shot in the dark.

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • July 6, 2012 at 9:18 am

    #1509757

    Thanks, replace will work.

  • July 6, 2012 at 9:35 am

    #1509769

    The other thing you could do is to set the cell format for the appropriate column *before* pasting the data in. If Excel thinks the column is a text column it will paste the data as-is rather than trying to be clever and converting it to a date.

  • July 6, 2012 at 10:21 am

    #1509786

    Thanks, that is what I found out too - to format the columns before copy, paste.

    I am thinking more now into SSIS, to export sql result to excel, this may also have the problem.

    So maybe we need to make an excel template to format columns in advance. Then do the import

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply