BCP to Excel question?

[KevinFSI]

Can I bcp to a specific worksheet? Let's say I want my QUERYOUT to go to C:\test.xls Can I put that data on Sheet3 if I wanted to? If so, what would the syntax be?

Thanks in advance.

Kevin
slanek@ssd.fsi.com

"Life is what happens to you while you're busy making other plans."
- John Lennon

 

[MAK9974]

why cant you create a DTS package for this?

 

[JohnDTampaBay]

I don't think BCP will create an XLS file. Text files only. A comma-delimited (CSV) text file could be created with BCP and opened in Excel nicely though. DTS would be the way to go if you need an XLS.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[KevinFSI]

BCP will create an xls just fine, but I don't think I have enough control over the individual worksheets. I think DTS is my only other option, but I despise the GUI

Kevin
slanek@ssd.fsi.com

"Life is what happens to you while you're busy making other plans."
- John Lennon

 

[JohnDTampaBay]

Cool. I'd love to see how that's done so I don't have to use DTS all the time. Everything in BOL sounds like a text file is the only file format that can be created. Of course the text file can have any name you like, even BCP.EXE - but its still just a text file.

I'll mark your post and look forward to the code snippet. Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[tlbroadbent]

BCP can output to a file with an XLS extension but having an XLS extension doesn't make a file an Excel workbook. The file will be a flat file. It therefore follows that you cannot create a specific sheet in an Excel workbook.

However, you can name the output file sheet3 and when you open the file in Excel the sheet name will be sheet3. If you want to create an excel workbook with specifically named worksheets, you will need to use DTS.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[KevinFSI]

Let me put it this way.
If I do this:

bcp "myQuery" QUERYOUT c:\testing.xls

The icon for the file shows up as an Excel icon and it opens with Excel without me having to tell it to "open with"

Kevin
slanek@ssd.fsi.com

"Life is what happens to you while you're busy making other plans."
- John Lennon

 

[tlbroadbent]

I agree with what you say. However, the Icon is based on the association established in Windows not on the content of the file. Change the extension on a Word document from DOC to XLS and look at the Icon. The fact the Icon is an Excel Icon doesn't mean the Word document is now an Excel workbook.

You can open a .CSV file in Excel as easily as you open .XLS file. Use BCP to export to a file with a CSV extension. If the default MS Office associations are still set on your computer, the CSV file will show up as an Excel file in Windows Explorer.

You can also open the file created by BCP in Notepad. Try to open an Excel workbook in Notepad. You'll note an immediate difference in the files.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[MAK9974]

basically when you double click XL imports it to XL format.
even .csv file can be opened in Excel application, it doesnt mean that it is an XL file

 

[KevinFSI]

Gotcha Terry. I see what you're saying. Let me axe you dis; so since it seems DTS is the only way I can have the ammount of control I need over the individual sheets, is there a way for me to create the xls files in a loop?

What I WAS doing with bcp was: I created a SP which queried the data I needed. I had all my departments in a cursor. I looped through all the departments and passed the deptID to the SP via bcp using xp_cmdshell in QA. When all was said and done, I had 40 nice little files created with all the data in them.

Am I wrong, or will I have to establish a connection to all these external data files in advance in order to write to them using DTS?

Kevin
slanek@ssd.fsi.com

"Life is what happens to you while you're busy making other plans."
- John Lennon

 

[JohnDTampaBay]

There's a great example of a looping DTS package on

http://www.sqldts.com.

It would need some modification to serve your purposes. You could modify it to loop and call the data stored procedure for each DeptID as the source and the Excel file as the destiniation without having to deal with the individual files.

You'll have to work with some DTS global variables to pull it off. Check

http://www.sqldts.com

for articles on global variables.

Sounds like this is a "chewy" project, but you'll win in the end. Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[KevinFSI]

Thanks I'll check it out. Yeah, it's a nasty one. There are a few other hurdles I'll have to jump too, but first things first.

Kevin
slanek@ssd.fsi.com

"Life is what happens to you while you're busy making other plans."
- John Lennon