Export from SQL to Excel 2007 format

Question

Export from SQL to Excel 2007 format

Ken Davis

SSCarpal Tunnel

Points: 4329
More actions
March 8, 2011 at 8:39 am

#236242

Hello,
I need to export data from SQL 2005 to an Excel 2007 .xlsx formatted file. I know I can use SSIS but I would like to keep it simple and use T-sql or BCP if I can.
I find lots of information about OPENROWSET and the Microsoft.ACE.OLEDB.12.0 driver but I can't find the actual steps I need to follow and the syntax to use.
Also, can BCP create a .xlsx output file that Excel can actually read? People post examples of using the -c parm, writing to xxxxxxx.xlsx but I don't think that is really an Excel-formatted file.
thanks for any information

Viewing 13 posts - 1 through 12 (of 12 total)

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

foscsamuels Mr or Mrs. 500 Points: 560 More actions · Answer 1

Ideally you should be able to create a object that loads the excel application. I dont know how to do this in tsql but in vbscript, it looks like this.

Set oXLS = CreateObject("Excel.Application")

sPath = "File.csv"

oXLS.Workbooks.Open sPath

oXLS.ActiveWorkbook.Saveas "\test.xlsx", fileformat:=51

oXLS.Application.DisplayAlerts = False

oXLS.Workbooks.Close

oXLS.Quit

Set oXLS = nothing

What I would do is

create a csv using bcp

-- see this link http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

then use xp_cmdshell to execute a vbscript to convert the file **code above**

Ken Davis SSCarpal Tunnel Points: 4329 More actions · Answer 2

Thanks. I don't know vbscript but that sounds like a simple solution.

I edited the path in your sPath and Saveas statements like this:

Set oXLS = CreateObject("Excel.Application")

sPath = "c:\File.csv"

oXLS.Workbooks.Open sPath

oXLS.ActiveWorkbook.Saveas "c:\file.xlsx", fileformat:=51

oXLS.Application.DisplayAlerts = False

oXLS.Workbooks.Close

oXLS.Quit

Set oXLS = nothing

I saved that as ToXLS.vbs and I created a simple .csv file as File.cvs. When I doubleclick my .vbs file or run it in a .bat file it just blinks at me without creating the .xlsx file. Can you give me any advice on how to troubleshoot this?

Ken Davis SSCarpal Tunnel Points: 4329 More actions · Answer 3

I was able to make this work with OPENROWSET.

1. Download and install the Office 2007 connectivity components - http://www.microsoft.com/downloads/en/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en

2. Create and save an excel spreadsheet with column headers for each db row you are exporting.

3. Run this

INSERT INTO OPENROWSET

('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=C:\contact.xlsx;Extended Properties=Excel 12.0 XML;HDR=YES',

'SELECT * FROM [Sheet1$]')

SELECT firstname,lastname from table1

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Ken Davis (3/8/2011)
I was able to make this work with OPENROWSET.
1. Download and install the Office 2007 connectivity components - http://www.microsoft.com/downloads/en/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en
2. Create and save an excel spreadsheet with column headers for each db row you are exporting.
3. Run this
INSERT INTO OPENROWSET
('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\contact.xlsx;Extended Properties=Excel 12.0 XML;HDR=YES',
'SELECT * FROM [Sheet1$]')
SELECT firstname,lastname from table1

Just beware when using the Office Automation libs in an unattended execution context. A lot of people do it, and most do not have issues, but Microsoft has officially said they do not recommend it.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757#kb2

Here also is a good conversation on the topic:

http://social.msdn.microsoft.com/Forums/en/netfx64bit/thread/eb5e42fe-f0a0-4a83-95e9-f8d2e891ffbf

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Ken Davis SSCarpal Tunnel Points: 4329 More actions · Answer 5

Thanks for that advice. I will read those articles. We also don't normally install Office components (even drivers) on prod SQL boxes so I'm a little concerned about that.

Pam Brisjar SSChampion Points: 12094 More actions · Answer 6

How is this any "simpler" than creating a simple data flow within SSIS, the tool that was designed to do this?

Seriously, you could have a whole, complete export done in under 5 minutes using SSIS and it would be fully supported.

Ken Davis SSCarpal Tunnel Points: 4329 More actions · Answer 7

Obviously a valid point. I need to move an old process off of SQL 2000 and it uses DTS packages and VB scripts. I kind of challenged myself to recreate the whole process using only sql statements. So it's kind of a point of pride. 🙂 But I can't really argue with using SSIS to do what it's designed to do...

SkyBVI SSCoach Points: 15128 More actions · Answer 8

Ken,

There is other way too and indeed it is more simpler and the fastest way.

Open an excel file->go to data->get external data->from other sources->sql server

then connect to the db and then import data.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 9

Ken

Have you consider to do it the other way around? In short.

What I do is to create an Excel template with a build in "macro"

VBA code. This template is available for the users in the organization

to download to their local PCs.

There is a server with a SQL-server database accessible on the intranet.

There is a user account with limited access rights.

When the user open the template he/she will be prompted for the userid

and a password and sometimes a search criteria from a list (cost center).

The VBA code will access the server by remote OLEDB pull out the data

with SQL and populate one or more sheets in the template. You can formate

the sheets as you like with VBA.

The nice thing is that you don't even have to install any Office components

on the server.

I also use this approach to download data from an Excelsheet to the database.

Gosta M

Ken Davis SSCarpal Tunnel Points: 4329 More actions · Answer 10

Sushant, Gosta,

Thanks for the ideas. In this case the business users are used to opening the output Excel files on a fileshare. I don't want to change their process. I do need to get rid of the DTS packages and VB scripts. I probably need to use SSIS. I was just trying to accomplish this using only sql statements but that is probably not the best idea.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 11

Ken Davis (3/11/2011)
Sushant, Gosta,
Thanks for the ideas. In this case the business users are used to opening the output Excel files on a fileshare. I don't want to change their process. I do need to get rid of the DTS packages and VB scripts. I probably need to use SSIS. I was just trying to accomplish this using only sql statements but that is probably not the best idea.

That's not a problem. Setup an "auto run" macro in Excel to get the newest data everytime they open the spreadsheet.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 12

Ken

If you don't want to distribute the Excelfiles with any macro you can create

the Excelfiles on a server (with an auto open macro which runs frequently enough to refresh the content) and save the result file without the macro part. The users can then fetch the file on the server.

//Gosta M