Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Output a query as an Excel file


Output a query as an Excel file

Author
Message
hoolash
hoolash
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
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?
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2268 Visits: 7824
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • bitbucket-25253
    bitbucket-25253
    SSCertifiable
    SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

    Group: General Forum Members
    Points: 5689 Visits: 25280
    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
    hoolash
    hoolash
    SSC Rookie
    SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

    Group: General Forum Members
    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.
    Koen Verbeeck
    Koen Verbeeck
    SSCoach
    SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

    Group: General Forum Members
    Points: 16447 Visits: 13202
    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 SQLKover.

    MCSA SQL Server 2012 - MCSE Business Intelligence
    gand 29575
    gand 29575
    SSC Rookie
    SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

    Group: General Forum Members
    Points: 28 Visits: 406
    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.
    aaron.reese
    aaron.reese
    Mr or Mrs. 500
    Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

    Group: General Forum Members
    Points: 589 Visits: 898
    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 :-D)

    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.
    sestell1
    sestell1
    SSCrazy
    SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

    Group: General Forum Members
    Points: 2192 Visits: 3415
    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


    Caruncles
    Caruncles
    SSC Journeyman
    SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

    Group: General Forum Members
    Points: 98 Visits: 240
    I know this is old, but I needed a refresher and came across this. I've found it's better not to do the query in the sendmail. Export the file like this:

    Execute xp_cmdshell 'SQLCMD -S VSQL2 -d urdatabase -U urname -P urpassword -Q "Select * from urfilename" -o C:\urexportedfile.csv -W -w 1500 -s","'

    then just do the sendmail with an attachment- no query needed:
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'urSQLemailprofile',
    @recipients = 'anyone@anywhere',
    @subject = 'blah blah blah',
    @body = 'more blah ',
    @file_attachments= 'C:\urexportedfile.csv'
    End

    If your Windows Default Programs are set for Excel to open .csv files, it will open purty as you please.

    Wallace Houston
    Sunnyland Farms, Inc.

    "We must endeavor to persevere."
    bgalway
    bgalway
    SSC Rookie
    SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

    Group: General Forum Members
    Points: 28 Visits: 853
    A possible, easy and repeatable option: connect to SQL via Access (ODBC, for example).In SQL, create a View that has your query results.Access attaches to Views and the results can be copied/pasted or exported (via Access) to Excel.
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search