Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2062
  • Last Modified:

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

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
andrishelp
Asked:
andrishelp
1 Solution
 
lcohanDatabase AnalystCommented:
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
 
andrishelpAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
andrishelpAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
andrishelpAuthor Commented:

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
 
Kevin CrossChief Technology OfficerCommented:
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now