Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-09-22
12
Medium Priority
?
1,925 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 40

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 60

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 60

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 60

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 60

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 60

Accepted Solution

by:
Kevin Cross earned 1000 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 60

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Copy Database Wizard 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.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

596 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