Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Output a query as an Excel file Expand / Collapse
Author
Message
Posted Saturday, November 16, 2013 3:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 9:14 AM
Points: 27, Visits: 59
Hi all,

I'm a newbie to SQL. I'm using SQL Server 2012 on my local machine and I need to find a way to output my queries as Excel files. I've searched the web for a script but can't find one.
I came across these codes for Interactive SQL (what is intercative SQL by the way?) but they don't work in the SQL query window:

SELECT * FROM SalesOrders;
OUTPUT USING 'Driver=Microsoft Excel Driver (*.xls);
DBQ=c:\\test\\sales.xls;
READONLY=0' INTO "newSalesData";

It seems the "OUTPUT" command is not a valid command. I really liked that piece of code (may be because it is so simple and carries over very few lines)! Do we have something similar for SQL Server 2012 that can do the job?
Post #1514901
Posted Saturday, November 16, 2013 10:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:17 AM
Points: 1,816, Visits: 5,913
Nothing like that for SQL Server, so it depends whether you want to export ad-hoc query results or you want to schedule the extract?

For ad-hoc export to Excel, I would recommend an addin for SSMS called SSMSBoost.

For regular/scheduled exports, you could use SSRS with a subscription or SSIS and a SQL Agent job.


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1514932
    Posted Saturday, November 16, 2013 12:03 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Thursday, November 6, 2014 1:00 PM
    Points: 5,333, Visits: 25,277
    Here is an adhoc method, to run when you want ... uses just the basics

    http://www.mssqltips.com/sqlservertip/1202/export-data-from-sql-server-to-excel/


    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read

    Before posting a performance problem please read
    Post #1514935
    Posted Sunday, November 17, 2013 3:06 AM
    SSC Rookie

    SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

    Group: General Forum Members
    Last Login: Tuesday, October 14, 2014 9:14 AM
    Points: 27, Visits: 59
    Thanks mister.magoo and bitbucket! Wow, I didn't know it was so complex to do that! SQL Server being a Microsoft product (same as Excel), I believe there should have been an built-in procedure to do the export!
    I've tried a copy/paste of the query result and it seems to work till now. I will need to go through the excel file to see if it evrything's ok.
    Post #1514960
    Posted Sunday, November 17, 2013 1:24 PM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: 2 days ago @ 12:52 PM
    Points: 13,636, Visits: 11,509
    hoolash (11/17/2013)
    Thanks mister.magoo and bitbucket! Wow, I didn't know it was so complex to do that! SQL Server being a Microsoft product (same as Excel), I believe there should have been an built-in procedure to do the export!
    I've tried a copy/paste of the query result and it seems to work till now. I will need to go through the excel file to see if it evrything's ok.


    The built-in procedure is called the import/export wizard. (which is basically SSIS behind the scenes)




    How to post forum questions.
    Need an answer? No, you need a question.
    What’s the deal with Excel & SSIS?

    Member of LinkedIn. My blog at LessThanDot.

    MCSA SQL Server 2012 - MCSE Business Intelligence
    Post #1515020
    Posted Monday, November 18, 2013 3:10 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Yesterday @ 12:31 AM
    Points: 10, Visits: 239
    I don't know whether the following might meet your needs: Create an ODBC connection to Sql Server using ODBC manager within Excel, then use the Data Import menu in Excel to create the sql query and retrieve results. I prefer ODBC because it allows you to use parameters. The process is quite straightforward.
    Post #1515143
    Posted Monday, November 18, 2013 8:01 AM
    SSC-Addicted

    SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

    Group: General Forum Members
    Last Login: 2 days ago @ 12:16 AM
    Points: 461, Visits: 693
    From 2003, Excel files are just XML documents (as are all office documents)
    so you could generate the xls or xlsm file dynamically - its quite a bit of work to set up .xls headers and footers but may be worth it if you need to do a lot of reports. One nice thing about this solution is that you can embed Excel formulas and conditional formatting in the spreadsheet output.1


    SSIS is another alternative, but in my experience Excel and SSIS do not play nicely together, especially if you are developing in 32 bit and deploying on 64 bit. There are so many ways it can go wrong and the error messages are even more unhelpful than ususal (which you would have through would be pretty hard )

    Pulling data from SQL using the Excel application is probably the simplest and safest solution, provided your network permissions allow it. SSRS reports saved as Excel is another option - and probably the best if you have sharepoint installed.

    Perhaps the most pragmatic approach would be to output the SQL results set FOR XML and then import the XML into Excel. If you provide a DTD then Excel is pretty good at parsing XML and producing decent looking reports.

    Whatever option you choose, it will require an intermediate-advanced knowlege of the technology stack but is definately worth the investment in time and effort.


    Post #1515243
    Posted Monday, November 18, 2013 12:49 PM


    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Yesterday @ 9:23 AM
    Points: 1,422, Visits: 2,444
    For one-off stuff I do for myself, I cheat and use sp_Send_DBMail to create a tab delimited file named like it is an Excel spreadsheet.

    DECLARE @Delimiter Char(1)

    SET @Delimiter = CHAR(9)

    EXEC MSDB.dbo.sp_Send_DBMail
    @Recipients='My.Email.Address@company.com',
    @Subject='Some Audit',
    @Body='Attached is some audit information.',
    @Query='SELECT Left(Name, 50) AS Name, Left(Description, 50) AS Description FROM MSDB.dbo.SysJobs',
    @Attach_Query_Result_As_File = 1,
    @Query_Result_Header = 1,
    @Query_Attachment_Filename = 'Audit.xls',
    @Query_Result_Separator = @Delimiter

    Post #1515332
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse