Exporting to Excel with headers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Exporting to Excel with headers

Author

Topic

mikemcg36
Starting Member

9 Posts

Posted - 2011-11-03 : 12:43:56

I have done some googling on this but have not found anything that works. I am using this to export my SQL data to an excel file:


INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Temp\SAL.xlsx;','SELECT * FROM [Sheet1$]')

It works! But, I have multiple queries to go into one excel file. How can I insert a blank row and then a heading before each query using OPENROWSET?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 13:10:53

not possible to export multiple structured data to same excel. Can i ask need of this? what meaning does it make to store data mixed up like this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mikemcg36
Starting Member

9 Posts

Posted - 2011-11-03 : 13:14:38

I am just trying to generate a report with sql and not reporting services. The report has any number of different queries on it. I am now looking into this:

--Insert a header and results
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=E:\Backups\Name.xlsx;',
'SELECT * FROM [Employee$]')
SELECT 'HumanResources.Employee' AS HeaderText
GO

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=E:\Backups\Name.xlsx;',
'SELECT * FROM [Employee$]')
SELECT TOP 10 EmployeeID FROM HumanResources.Employee
GO

--Insert another header and results
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=E:\Backups\Name.xlsx;',
'SELECT * FROM [Employee$]')
SELECT 'HumanResources.Department' AS HeaderText
GO

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=E:\Backups\Name.xlsx;',
'SELECT * FROM [Employee$]')
SELECT TOP 10 DepartmentID FROM HumanResources.Department
GO

Anything is possible.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 13:18:26

why not show them in different sheets then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mikemcg36
Starting Member

9 Posts

Posted - 2011-11-03 : 13:22:25

I guess I could do that. Had not thought about it. I was trying to replicate another report I am looking at. I may go with the different sheets to save time and headache.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 13:23:46

if its different sheets it possible

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-11-03 : 16:10:43

If it is a one time report, simply do this.

1. in SSMS click QUERY, then QUERY OPTIONS:
2. Click on GRID in the tree
3. Click the checkbox for INCLUDE COLUMN HEADERS WHEN COPYING.
4. Run your query as normal
5. Highlight all your results and COPY and Paste into excel...

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 04:25:58

only thing you need to change in above scripts is to include sheetnames if it goes to multiple sheets

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources