Post reply

BCP out to xls file

  • June 28, 2016 at 10:19 am

    #308737

    Query below works fine but when I change @file2 ='\\files\test\A.xls' , it generates the file but get error message while opening the file:

    The file format and extension of the file name don't match. The file could be corrupted or unsafe. Unless you trust its source,don't open it. Do you want to open it anyway? When I click on yes, the files opens with comma separated values.

    How do I make it work in xls format with column header?

    DECLARE @columns VARCHAR(8000)

    declare @sql VARCHAR(8000)

    declare @table_name varchar (8000)

    declare @file2 varchar(8000)

    declare @servername varchar(8000)

    set @table_name ='tmp'

    set @file2 ='\\files\test\A.csv'

    set @servername = 'AB'

    SELECT @columns = COALESCE(@columns+',' ,'')+ ''''+column_name +'''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@table_name

    SELECT @sql = 'bcp "SELECT '+ @columns +' UNION ALL select convert(varchar(20),[id]) as [id],[des],convert(varchar(20),P) as P,convert(varchar(20),S) as from DB.dbo.table" queryout '+ @file2 +' -c -t, -Utest -P### -S'+@servername

    EXEC xp_cmdshell @sql

  • June 28, 2016 at 10:26 am

    #1887383

    bcp doesn't have output to xls files. It generates flat files (delimited or fixed length) which you can add any extension.

    Use SSIS for this.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 2 posts - 1 through 1 (of 1 total)

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