Post reply

Export SQL Server 2005 Table to Excel 2007 using T-SQL

  • May 3, 2010 at 3:18 am

    #142801

    Hi guys,

    I ve got a situation where one of my colleagues wanted to export data in his table (He uses SQL Server 2005 Dev Edition) to an Excel 2007 (.xlsx) file. And the constraint he posted was, NO SSIS & NO BCP!

    So is it possible doing that using some T-SQL code?

    For my part i tried using the OPENROWSET AND OPENDATASOURCE statements, but nothing helped me. All i ended up when using OPENROWSET was this following error

    OLE DB provider "Microsoft.JET.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".

    The code i used

    DECLARE @SQLSTR nvarchar(MAX)

    SET @SQLSTR = 'INSERT INTO openrowset(

    ''Microsoft.JET.OLEDB.4.0''

    , ''Excel 12.0 ;Database=D:\Test\Testing.xlsx;''

    , ''SELECT * FROM [Sheet1$]'') select * from Scratch.dbo.PETS'

    EXEC (@SQLSTR)

    If anyone knows a method on how to export data in SQL Server 2005 table to an Excel 2007 worksheet, i would greatly appreciate your help..

    TIA, Cheers!!

  • May 3, 2010 at 2:10 pm

    #1161494

    I'm assuming a copy/paste is out of the question? =)

    What about using a data source from the excel worksheet to pull the data in?

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • May 3, 2010 at 5:21 pm

    #1161548

    If this is a one time export ... believe the following link will assist you

    http://www.sql-server-performance.com/articles/biz/How_to_Export_Data_to_Excel_2007_p1.aspx

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

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • May 3, 2010 at 10:23 pm

    #1161595

    Seth, yes. A copy-paste is not enough. And also, i need T-SQL statments only. THis is kind of automating an existing work-process which is using SQL and Excel 2003. He is planning to use Excel 2007, and that why he requires some T-SQL code.

    @Ron, thanks for that info. But i need T-SQL implementation of that.

    Thanks guys, for your replies!!

  • May 5, 2010 at 12:49 pm

    #1162702

    ColdCoffee

    First Excell 2007 uses a different ODBC driver than does previous versions of OFFICE products:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

    Download and install the above, then alter your OPENROWSET statement to:

    Create the Excel workbook (Book1.xlsx) save it on your

    INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',

    'Excel 12.0;Database=C:\Test\Book1.xlsx;',

    'SELECT * FROM [Sheet1$]') SELECT EmployeeId from dbo.Employees

    Tested using NORTHWIND

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

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • May 6, 2010 at 5:20 am

    #1163075

    Oh Ron, thanks! I always had a doubt that this has something to with the ODBC drivers.And you have now confirmed it!

    Thanks for your efforts and time, Ron!

  • May 6, 2010 at 11:28 am

    #1163469

    In SSMS (maybe only in 2008) , you can "copy results with headers" from query window, then paste it into excel. Done!

  • May 6, 2010 at 12:26 pm

    #1163509

    Thanks for the reply, jdddd2! But i need a T-SQL code, as in Ron's example to perform my task!

    Anyways, thanks for the info!

  • July 6, 2010 at 9:38 am

    #1189155

    Hi, new to the forum, and have a question regarding this Microsoft add-in... I installed it correctly, and do see the registry entry, but I get this error:

    OLE DB error trace [Non-interface error: Provider not registered.].

    Msg 7403, Level 16, State 1, Line 1

    Could not locate registry entry for OLE DB provider 'Microsoft.ACE.OLEDB.12.0'.

    Any Ideas why?

    Thanks in advance,

    AV

  • July 6, 2010 at 10:04 am

    #1189175

    just hazarding a guess here, but could it be your SQL instance is 64 bit and the drivers are only 32 bit?

    anthony.ventura (7/6/2010)

    Hi, new to the forum, and have a question regarding this Microsoft add-in... I installed it correctly, and do see the registry entry, but I get this error:

    OLE DB error trace [Non-interface error: Provider not registered.].

    Msg 7403, Level 16, State 1, Line 1

    Could not locate registry entry for OLE DB provider 'Microsoft.ACE.OLEDB.12.0'.

    Any Ideas why?

    Thanks in advance,

    AV

    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 6, 2010 at 10:12 am

    #1189181

    Lowell (7/6/2010)

    just hazarding a guess here, but could it be your SQL instance is 64 bit and the drivers are only 32 bit?

    anthony.ventura (7/6/2010)

    Hi, new to the forum, and have a question regarding this Microsoft add-in... I installed it correctly, and do see the registry entry, but I get this error:

    OLE DB error trace [Non-interface error: Provider not registered.].

    Msg 7403, Level 16, State 1, Line 1

    Could not locate registry entry for OLE DB provider 'Microsoft.ACE.OLEDB.12.0'.

    Any Ideas why?

    Thanks in advance,

    AV

    Regardless, is there a way now to do it with sql 64 bit withou ssis?? I hit that wall a couple years back and I couldn't believe that MS had missed this huge need!

  • July 6, 2010 at 10:18 am

    #1189186

    Good question, I'm not sure how to even check if I am running a 64 bit version, but to give you a little more information, I am running SQL Server 2005 Managment Studio on Windows XP system, and installed the Add-In from the link in this thread above. I was also attempting to run the sample SQL database code to test, also copied from this thread above, along with running it in my actual SQL query, getting the same error message. I'm not really sure where to begin troubleshooting why i am receiving this error...

  • November 18, 2010 at 3:20 am

    #1252363

    Hi thanks for ur support,

    i used ur query to export data from sql to excel.

    but im able to export only one column from the table, but i need to export multiple columns....what should i do....?

    Please help me in this issue.

    Manoj

  • June 15, 2012 at 4:41 am

    #1501527

    HI

    INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',

    'Excel 12.0;Database=C:\Test\Book1.xlsx;',

    'SELECT * FROM [Sheet1$]') SELECT EmployeeId from dbo.Employees

    the Code is working Fine Thanks a lot you 🙂

  • June 19, 2012 at 3:33 am

    #1502652

    Strangely, I was working on something today that benefitted from this XLSX provider. I was SELECTing from OPENROWSET instead of INSERTing, but otherwise the requirement was the same.

    I had to run these commands to get it working though. Didn't see them posted here (and I have no idea what they do) so thought it might be helpful to others trying to get this to work.

    USE [master]

    GO

    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1

    GO

    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

    GO

    For the record, the SQL Server instance I applied them to was on my Windows 7 x64 laptop.

    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

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

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