Creating Excel Using T-SQL

http://www.databasejournal.com/scripts/creating-excel-using-t-sql.html

PRINT 'Begin CreateXLS script at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))+' '
PRINT ''
GO

SET NOCOUNT ON
DECLARE @Conn int -- ADO Connection object to create XLS
      , @hr int -- OLE return value
      , @src varchar(255) -- OLE Error Source
      , @desc varchar(255) -- OLE Error Description
      , @Path varchar(255) -- Drive or UNC path for XLS
      , @Connect varchar(255) -- OLE DB Connection string for Jet 4 Excel ISAM
      , @WKS_Created bit -- Whether the XLS Worksheet exists
      , @WKS_Name varchar(128) -- Name of the XLS Worksheet (table)
      , @ServerName nvarchar(128) -- Linked Server name for XLS
      , @DDL varchar(8000) -- Jet4 DDL for the XLS WKS table creation
      , @SQL varchar(8000) -- INSERT INTO XLS T-SQL
      , @Recs int -- Number of records added to XLS
      , @Log bit -- Whether to log process detail
 
SELECT @Recs = 0 
   , @Log = 1 

SET @Path = 'E:\Rahul\'+CONVERT(varchar(10),GETDATE(),112)+'.xls'
SET @Path = 'E:\Rahul\RecordsHistory_MobDW.xls'
SET @Connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@Path+';Extended Properties=Excel 8.0'
SET @ServerName = 'EXCEL_TEST'
SET @WKS_Name = CONVERT(varchar(10),GETDATE(),112)


SET @DDL = 'CREATE TABLE '+@WKS_Name+' (TableName nvarchar, RowsCount int)'
SET @SQL = 'INSERT INTO '+@ServerName+'...'+@WKS_Name+' (TableName, RowsCount) '

SET @SQL = @SQL+'SELECT au_id AS SSN'
SET @SQL = @SQL+', LTRIM(RTRIM(ISNULL(au_fname,'''')+'' ''+ISNULL(au_lname,''''))) AS Name'
SET @SQL = @SQL+', phone AS Phone '
SET @SQL = @SQL+'FROM Rahul.dbo.Dim_Date_Test'

IF @Log = 1 PRINT 'Created OLE ADODB.Connection object'

-- Create the Conn object
EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT IF @hr <> 0 BEGIN
      EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
      SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
      RETURN
END

 

IF @Log = 1 PRINT char(9)+'Assigned ConnectionString property'
 EXEC @hr = sp_OASetProperty @Conn, 'ConnectionString', @Connect IF @hr <> 0 BEGIN
      EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
      SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
      RETURN
END

IF @Log = 1 PRINT char(9)+'Open Connection to XLS, for file Create or Append'

-- Call the Open method to create the XLS if it does not exist, can't use parameters

EXEC @hr = sp_OAMethod @Conn, 'Open'
IF @hr <> 0
BEGIN
       EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
      SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
      RETURN
END

 

-- %%% This section could be repeated for multiple Worksheets (Tables)

IF @Log = 1 PRINT char(9)+'Execute DDL to create '''+@WKS_Name+''' worksheet'
 EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @DDL, NULL, 129 -- adCmdText + adExecuteNoRecords

-- 0x80040E14 for table exists in ADO

IF @hr = 0x80040E14 
   OR @hr = 0x80042732
BEGIN
      IF @hr = 0x80040E14
      BEGIN
            PRINT char(9)+''''+@WKS_Name+''' Worksheet exists for append'
            SET @WKS_Created = 0
      END
      SET @hr = 0 -- ignore these errors END

IF @hr <> 0
BEGIN
      -- Return OLE error
      EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
      SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
      RETURN
END

 

IF @Log = 1 PRINT 'Destroyed OLE ADODB.Connection object'
-- Destroy the Conn object, +++ important to not leak memory +++ EXEC @hr = sp_OADestroy @Conn IF @hr <> 0 BEGIN
      -- Return OLE error
      EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
      SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
      RETURN
END

 

-- Linked Server allows T-SQL to access the XLS worksheet (Table)
--   This must be performed after the ADO stuff as the XLS must exist
--   and contain the schema for the table, or worksheet

IF NOT EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName) BEGIN
      IF @Log = 1 PRINT 'Created Linked Server '''+@ServerName+''' and Login'
      EXEC sp_addlinkedserver @server = @ServerName
            , @srvproduct = 'Microsoft Excel Workbook'
            , @provider = 'Microsoft.Jet.OLEDB.4.0'
            , @datasrc = @Path
            , @provstr = 'Excel 8.0' 
      EXEC sp_addlinkedsrvlogin @ServerName, 'false' 
END


EXEC (@SQL)
PRINT char(9)+'Populated '''+@WKS_Name+''' table with '+CONVERT(varchar,@@ROWCOUNT)+' Rows'

 IF EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName) BEGIN
      IF @Log = 1 PRINT 'Deleted Linked Server '''+@ServerName+''' and Login'
      EXEC sp_dropserver @ServerName, 'droplogins'
END
GO

 

SET NOCOUNT OFF
PRINT ''
PRINT 'Finished CreateXLS script at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))+' '
GO