T-SQL Export to new Excel file

Question

I have a script that does various things and the end result is one large table. I was wondering how I could export this final table to a new Excel file (with column headers as well).

I would need to do this within the script.

User · Answer

Use PowerShell    Server    TestServer   Database    TestDatabase   Query    select   from TestTable   FilePath    C  OutputFile csv     This will overwrite the file if it already exists  Invoke-Sqlcmd -Query  Query -Database  Database -ServerInstance  Server   Export-Csv  FilePath   In my usual cases  all I really need is a CSV file that can be read by Excel  However  if you need an actual Excel file  then tack on some code to convert the CSV file to an Excel file  This answer gives a solution for this  but I ve not tested it

User · Answer

This is by far the best post for exporting to excel from SQL   http   www sqlteam com forums topic asp TOPIC ID 49926  To quote from user madhivanan   Apart from using DTS and Export wizard  we can also use this query to export data from SQL Server2000 to Excel  Create an Excel file named testing having the headers same as that of table columns and use these queries  1 Export data to existing EXCEL file from SQL Server table  insert into OPENROWSET  Microsoft Jet OLEDB 4 0         Excel 8 0 Database D  testing xls          SELECT   FROM  SheetName     select   from SQLServerTable   2 Export data from Excel to new SQL Server table  select    into SQLServerTable FROM OPENROWSET  Microsoft Jet OLEDB 4 0         Excel 8 0 Database D  testing xls HDR YES         SELECT   FROM  Sheet1       3 Export data from Excel to existing SQL Server table  edited   Insert into SQLServerTable Select   FROM OPENROWSET  Microsoft Jet OLEDB 4 0         Excel 8 0 Database D  testing xls HDR YES         SELECT   FROM  SheetName       4 If you dont want to create an EXCEL file in advance and want to export data to it  use  EXEC sp makewebtask       outputfile    d  testing xls         query    Select   from Database name  SQLServerTable         colheaders  1        FixedFont 0  lastupdated 0  resultstitle  Testing details     Now you can find the file with data in tabular format   5 To export data to new EXCEL file with heading column names   create the following procedure  create procedure proc generate excel with columns        db name    varchar 100        table name varchar 100           file name  varchar 100    as  --Generate column names as a recordset declare  columns varchar 8000    sql varchar 8000    data file varchar 100  select       columns coalesce  columns         column name   as   column name  from      information schema columns where      table name  table name select  columns        replace replace  columns   as         as                  --Create a dummy file to have actual data select  data file substring  file name 1 len  file name -charindex     reverse  file name      data file xls   --Generate column names in the passed EXCEL file set  sql  exec master  xp cmdshell   bcp   select   from  select    columns    as t  queryout     file name    -c    exec  sql   --Generate data in the dummy file set  sql  exec master  xp cmdshell   bcp  select   from    db name       table name    queryout     data file    -c    exec  sql   --Copy dummy file to passed EXCEL file set  sql   exec master  xp cmdshell   type    data file    gt  gt      file name       exec  sql   --Delete dummy file  set  sql   exec master  xp cmdshell   del    data file      exec  sql    After creating the procedure  execute it by supplying database name  table name and file path   EXEC proc generate excel with columns  your dbname    your table name   your file path    Its a whomping 29 pages but that is because others show various other ways as well as people asking questions just like this one on how to do it   Follow that thread entirely and look at the various questions people have asked and how they are solved   I picked up quite a bit of knowledge just skimming it and have used portions of it to get expected results   To update single cells  A member also there Peter Larson posts the following  I think one thing is missing here  It is great to be able to Export and Import to Excel files  but how about updating single cells  Or a range of cells   This is the principle of how you do manage that  update OPENROWSET  Microsoft Jet OLEDB 4 0     Excel 8 0 Database c  test xls hdr no     SELECT   FROM  Sheet1 b7 b7    set f1   -99   You can also add formulas to Excel using this   update OPENROWSET  Microsoft Jet OLEDB 4 0     Excel 8 0 Database c  test xls hdr no     SELECT   FROM  Sheet1 b7 b7    set f1     a7 c7    Exporting with column names using T-SQL  Member Mladen Prajdic also has a blog entry on how to do this here  References  www sqlteam com  btw this is an excellent blog   forum for anyone looking to get more out of SQL Server    For error referencing I used this  Errors that may occur  If you get the following error      OLE DB provider  Microsoft Jet OLEDB 4 0  cannot be used for   distributed queries   Then run this   sp configure  show advanced options   1  GO RECONFIGURE  GO sp configure  Ad Hoc Distributed Queries   1  GO RECONFIGURE  GO

[sql-server-2008] T-SQL: Export to new Excel file

The answer is

Examples related to sql-server-2008

Examples related to tsql

Examples related to excel

Tags