Solved

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

Posted on 2011-09-22
12
1,503 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now