T-sql to excel (bcp)

Question

T-sql to excel (bcp)

river1

SSC-Insane

Points: 24128
More actions
July 27, 2009 at 3:37 am

#73882

Hi,
I trie to execute the folowing command:
exec master..xp_cmdshell 'bcp "select cod,nome,data from [SGCTcentral]..Vteste" queryout "c:\excel\teste21.xls" -c -T -S [fscg41\sql2000]'
I receive this error:
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]O SQL Server não existe ou o acesso foi recusado.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
NULL
can someone help, please?
thank you

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

manish.singh-1128425 SSC-Addicted Points: 402 More actions · Answer 1

river (7/27/2009)
exec master..xp_cmdshell 'bcp "select cod,nome,data from [SGCTcentral]..Vteste" queryout "c:\excel\teste21.xls" -c -T -S [fscg41\sql2000]'
I receive this error:
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]O SQL Server não existe ou o acesso foi recusado.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
NULL

Hi,

Try using -S"fscg41\sql2000" in place of -S [fscg41\sql2000]

river1 SSC-Insane Points: 24128 More actions · Answer 2

yap, it works.

But i have a problem:

I do this comand:

exec master..xp_cmdshell 'bcp "select * from database..table order by field1" queryout "c:\excel\1.xls" -c -T -S "Servername\sql2000"'

This command creates the excel file.

What i would like is that this comand inserts the data in a excel template file that i have.

This template file as the columns formated i would like this comand to insert the data into it, instead of creating a new one

Is it possible?

e.g - Make a select from a table and insert the data into a excel file that i have allready created (this excel file as the same number of columns that the table have, i would like that the BCP starts to insert data below the column names in this existente excel file).

thanks

manish.singh-1128425 SSC-Addicted Points: 402 More actions · Answer 3

Hi,

BCP always overwrites any file that might already be there. There is no option to append.

However there are work arrounds for the same, either you can use following steps to append the file from dos prompt

1) BCP to a temporay file

2) Run the following commands from dos prompt

TYPE [TemperoryFileName.csv] >> [ActualFileName.csv]

Or u can have a global temporary table created and do the bcp oyt once all your results are copied to the ##temp table.