Solved

To export data to new Excel file with heading(column names) using stored procedure

Posted on 2011-09-22
12
1,549 Views
Last Modified: 2012-05-12
How to export data to new Excel file with heading(column names) using stored procedure...

I did try what it says here...
http://www.dotnetspider.com/resources/28884-Export-Sql-Server-table-CSV-Excel-Txt-Files.aspx

@columns = "WeekOfDate,DMFirstName,DMLastName,DTRName"

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select '+@columns+' as t" queryout "'+@Path+'" -T -c -t, -S @servername'''

exec(@sql)

When I ran this query, I am getting this following...
 
SQLState = S0022, NativeError = 207
Error = [Microsoft][SQL Native Client][SQL Server]Invalid column name 'WeekOfDate'.
SQLState = S0022, NativeError = 207
Error = [Microsoft][SQL Native Client][SQL Server]Invalid column name 'DMFirstName'.
SQLState = S0022, NativeError = 207
;;;;;;;;;
0
Comment
Question by:andrishelp
12 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 36581723
I don't think your syntax is 100% correct and here's what I use with success so far:


http://blog.sqlauthority.com/2008/01/08/sql-server-2005-export-data-from-sql-server-2005-to-microsoft-excel-datasheet/
0
 

Author Comment

by:andrishelp
ID: 36581812
No issues with export the data to Excel from stored procedure. I have done the samething that's in that article.

But like I said in my question, how do I create the column names in the excel file dynamically before I export the data to excel?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36590263
If you have xp_cmdshell enabled, you can use the bcp Utility. Alternatively, if this does not have to be done from within T-SQL, you can use bcp from command line.
Reference: http://msdn.microsoft.com/en-us/library/ms162802(v=SQL.90).aspx
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 20

Expert Comment

by:Marten Rune
ID: 36590478
or use a union all clause, i e

select 'colnameOne', 'colnameTWO' ...
union all
select colnameOne, colnameTWO
from myTableOrView

Perhaps this is useful, you can do it dynamically by syscolumns etc, but it's probably not worth the effort. If you change your table/view, it's easy to change this stored proc at the same time. Needs to be documented though.

Regards Marten
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36590493
I thought of that too martenrune, but then considered what happens if the column data is numerical. Although, since this is going out to a CSV/Excel file, it may be able to work with CONVERT() expressions.
0
 

Author Comment

by:andrishelp
ID: 36599618
I am using bcp utility which I have posted in my post. Here is my question...

How to export data to new Excel file with heading(column names) using stored procedure...

I did try what it says here...
http://www.dotnetspider.com/resources/28884-Export-Sql-Server-table-CSV-Excel-Txt-Files.aspx

@columns = "WeekOfDate,DMFirstName,DMLastName,DTRName"

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select '+@columns+' as t" queryout "'+@Path+'" -T -c -t, -S @servername'''

exec(@sql)

When I ran this query, I am getting this following...
 
SQLState = S0022, NativeError = 207
Error = [Microsoft][SQL Native Client][SQL Server]Invalid column name 'WeekOfDate'.
SQLState = S0022, NativeError = 207
Error = [Microsoft][SQL Native Client][SQL Server]Invalid column name 'DMFirstName'.
SQLState = S0022, NativeError = 207
;;;;;;;;;
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36600413
I am sorry, you are right you are using bcp; therefore, I should have been more specific. Use bcp's option with a table name versus a query. If you put in the table name there, it should bring the data and the column headings.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36600576
And actually, I was told it worked that way, but never got a chance to try for myself. I just tested and I got an Excel fine okay, just not with column headings. *sigh*

This is what I meant by the way:
bcp AdventureWorks2008R2.Production.Product out "c:\temp\product.xls" -T -c

Open in new window


I would try http:#36590478 or Nigel's set fmtonly off http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

I will test also and see what I come up with.

If not, this may help: http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/812b8eec-5b77-42a2-bd23-965558ece5b9/
- create a file with just the column headings;
- create blank file to add in new line;
- create file with data;
- copy them together using "copy /b".
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 36600680
Hmm. I did not try the last method which I am sure works because it is just merging a spreadsheet with headings with one that has data, but could not get the others to work except martenrune's by doing what I said which is to account for datatype differences:

bcp "select 'ProductID', 'Name', 'ProductNumber' union all select convert(varchar(10), ProductID), Name, ProductNumber from AdventureWorks2008R2.Production.Product" queryout "c:\temp\product.xls" -c -T

By the way, not having single quotes around your column names is likely why you are getting the error you are. Since they are not string literals, SQL is trying to resolve them as columns, but you are really not selecting from a table ... so fails.
0
 

Author Comment

by:andrishelp
ID: 36711754

Thanks mwvisa1 ! It worked after adding single quotes around the column names.

If I want to be more specific about excel sheet where it supposed to go, how do I import the data in to the specific sheet in the excel file?

For example: I have one excel file which has 4 different sheets, and I want to import the data on one sheet. how to do that?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36712265
I do not know of a way to do that with bcp. You may have to turn to .NET code to do that as you have the Excel Interop that allows more control. Similarly, you can go from the Excel end and do a query to SQL and designate which sheet to put the results. Since these sheets refresh, they do well if the export is a constant need.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question