BCP from SQL To Excel

• PJ_SQL

  SSCertifiable

  Points: 6703

  More actions

  June 3, 2016 at 3:41 pm

  #319242

  I have this script below which is working fine, but the user wants file as .xls.

  When I change the variable to set @file2 ='C:\Test.xls' ,it generates the file. When I try to open the file it gives me this error message: the file format and extension of don't match excel. the file could be corrupted or unsafe and if I click on ok,it gives output in comma separated. But I want different columns in different cell .

  DECLARE @columns VARCHAR(8000)

  declare @sql VARCHAR(8000)

  declare @table_name varchar (8000)

  declare @file2 varchar(8000)

  declare @servername varchar(8000)

  set @table_name ='temp'

  set @file2 ='C:\Test.csv'

  set @servername = 'ABC'

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

  SELECT @sql = 'bcp "SELECT '+ @columns +' UNION ALL select [A],,[C],[D],[E],[F] from A.temp" queryout '+ @file2 +' -c -t, -Ua -P### -S'+@servername

  EXEC xp_cmdshell @sql

  Thanks.

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  June 3, 2016 at 9:12 pm

  #1883240

  PJ_SQL (6/3/2016)

  ---

  I have this script below which is working fine, but the user wants file as .xls.

  When I change the variable to set @file2 ='C:\Test.xls' ,it generates the file. When I try to open the file it gives me this error message: the file format and extension of don't match excel. the file could be corrupted or unsafe and if I click on ok,it gives output in comma separated. But I want different columns in different cell .

  DECLARE @columns VARCHAR(8000)

  declare @sql VARCHAR(8000)

  declare @table_name varchar (8000)

  declare @file2 varchar(8000)

  declare @servername varchar(8000)

  set @table_name ='temp'

  set @file2 ='C:\Test.csv'

  set @servername = 'ABC'

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

  SELECT @sql = 'bcp "SELECT '+ @columns +' UNION ALL select [A],,[C],[D],[E],[F] from A.temp" queryout '+ @file2 +' -c -t, -Ua -P### -S'+@servername

  EXEC xp_cmdshell @sql

  Thanks.

  The easiest way would be to create a view and a refreshable spreadsheet that would load from the view.

  A more difficult method would be to create a "Master" spreadsheet, copy it to a new file, and use the "ACE Drivers" to populate the copy of the Master spreadsheet.

  I suppose you could also use SSIS for this. Behind the scenes, it also uses the "ACE Drivers" but you don't need to know much about them. Even SSRS might work, as well. I'm just not real happy with any of the SQL Server 4 letter words. 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)