Select query to xls output

• Minnu

  SSCertifiable

  Points: 6451

  More actions

  April 21, 2015 at 6:20 am

  #303202

  Hi Team,

  using below script to export the select statement result to .xls

  declare @sql varchar(8000)

  select @sql = 'bcp "select * from Databases..Table" queryout c:\bcp\Tom.xls -c -t, -T -S' + @@servername

  exec master..xp_cmdshell @sql

  But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.

  how to export the data in columns to seperate tabs in excel.

  Please suggest

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

  April 21, 2015 at 6:28 am

  #1792373

  Minnu (4/21/2015)

  ---

  Hi Team,

  using below script to export the select statement result to .xls

  declare @sql varchar(8000)

  select @sql = 'bcp "select * from Databases..Table" queryout c:\bcp\Tom.xls -c -t, -T -S' + @@servername

  exec master..xp_cmdshell @sql

  But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.

  how to export the data in columns to seperate tabs in excel.

  Please suggest

  Try outputting to a CSV file (tom.csv) and then opening that in Excel.

  ---

• free_mascot

  One Orange Chip

  Points: 27164

  More actions

  April 21, 2015 at 6:57 am

  #1792382

  It's not BCP but with OPENROWSET you will able to export in various tabs in a xls; refer the following link for further detail

  http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspx

  HTH

  ---------------------------------------------------
  "Thare are only 10 types of people in the world:
  Those who understand binary, and those who don't."