SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 BCP problem		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts
Posted - 03/14/2001 :  09:08:46  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Sica writes "How can I make BCP output the field names along with the data? I BCP out to a xls-file and need to have the field names be the first row in the table.
EX:
EXEC('exec master..xp_cmdshell ''BCP "' + @DatabaseName + '..' + @TableName + '" OUT ' + @Path + @TableName + '.xls /SSLQSERVER /Usa /Psa@sa -t \t /r\n /c -C ACP -q''')"

robvolk
Most Valuable Yak

USA
15559 Posts
Posted - 03/14/2001 :  09:41:54  Show Profile  Visit robvolk's Homepage  Reply with Quote
BCP can't do this, but DTS can. You may be able to BCP the column names from the INFORMATION_SCHEMA views, using a tab (\t) as a row delimiter, and then use the DOS COPY command to append this file with the data. (I'll try it and will post the code if I'm successful)
Go to Top of Page

sica
Posting Yak Master

Sweden
143 Posts
Posted - 03/15/2001 :  09:36:52  Show Profile  Send sica an ICQ Message  Reply with Quote
The problem is that you cannot use Copy command to append an-xls file....Maybe if I use in BCP "queryout" argument instead!...

Edited by - sica on 03/15/2001 09:42:40
Go to Top of Page

robvolk
Most Valuable Yak

USA
15559 Posts
Posted - 03/15/2001 :  12:11:24  Show Profile  Visit robvolk's Homepage  Reply with Quote
Yes you can:

execute xp_cmdshell 'bcp "SELECT column_name FROM ' + @DatabaseName + '.information_schema.columns where table_name=''' + @TableName + '" queryout colnames.txt -r\t -c -Usa -Psa@sa -SSQLSERVER'
execute xp_cmdshell 'bcp ' + @DatabaseName + '..' + @TableName + ' out ' + @path + @TableName + '.xls -c -Usa -Psa@sa -SSQLSERVER'
execute xp_cmdshell 'bcp "SELECT Null" queryout lineterm.txt -c -r\n -Usa -Psa@sa -SSQLSERVER'
execute xp_cmdshell 'copy colnames.txt + lineterm.txt + ' + @path + @TableName + '.xls ' + @path + @TableName + '.xls'

The hard part was including the CRLF at the end of the column names, the lineterm.txt file will accomplish that.

You're not actually creating a true XLS file, it's just a plain tab-delimited text file with an .XLS extension that Excel will convert automatically.

Edited by - robvolk on 03/15/2001 12:17:32
Go to Top of Page

sica
Posting Yak Master

Sweden
143 Posts
Posted - 03/15/2001 :  16:41:59  Show Profile  Send sica an ICQ Message  Reply with Quote
yes,you're right...I tried my self append 2 xls files and it didn't work , but I did't thought of 2 txt files append in an xls-files.Very ingenious.Thanks a lot!!!
I will try it and come back to you..
Go to Top of Page

sica
Posting Yak Master

Sweden
143 Posts
Posted - 03/16/2001 :  09:29:29  Show Profile  Send sica an ICQ Message  Reply with Quote
Yes,it works perfectly...Thanks a lot man...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000