Export SQL Server data to Excel

I am often called upon to import data from Excel into SQL Server. This can easily be achieved even if the schema is unknown using a SELECT * INTO [tablename] FROM OPENROWSET(). Slightly more difficult however is exporting from a SQL Server database to an Excel file. This can be achieved if you don't mind spending some time setting up an Excel template first but this can lead to problems when a) the database has a large number of tables or b) the schema is likely to change.

The solution I came up with for this was to write a script to generate code to both build and populate the Excel template. The generated code is in two parts. The first is Excel macro code and will set up a file with a given filename containing a worksheet for each table in the database. Each worksheet will have a column for each field in the matching database table. The second is T-SQL containing an INSERT INTO OPENROWSET() command for each table in the database. Tables with no rows are skipped.

To generate the code, copy the script below into a query window in management studio, select the correct database, set the results to text, set the @filename variable to a valid file and folder (this file need not yet exist) and execute. The first section in the results (down as far as the first INSERT) should be copied into a macro in a new Excel spreadsheet and executed there. This will build your template file and you should then save this to the location specified in @filename earlier and close it. The second section (the INSERTs) should be copied to a new query window in management studio and executed. This will copy the data from SQL Server to your Excel file and depending on the database may take some time.

Notes

• I have tested this in SQL 2005/2008 and Excel 2007. Be aware of the row and column limits in Excel (65536 rows, 256 columns in 2000 and 1,048,576 rows and 16,384 columns in 2007).
• If you have a large number of tables and/or fields, the macro code may exceed the 64k limit for a procedure and you will encounter the following error in Excel when you attempt to run it: Compile error: Procedure too large. In this situation, split the code into a number of procedures and run each in turn.
• The script makes use of the Information_schema views so will not work in any version of SQL Server prior to 2005.

DECLARE @cnt            INT

DECLARE @schema         VARCHAR(128)
DECLARE @table          VARCHAR(128)
DECLARE @schematable    VARCHAR(256)
DECLARE @col            VARCHAR(128)
DECLARE @cmd            VARCHAR(2500)
DECLARE @nextcoli       INT
DECLARE @nextcolc       VARCHAR(10)
DECLARE @i              INT
DECLARE @j              INT
DECLARE @filename       VARCHAR(250)
DECLARE @sqlcmd         VARCHAR(1500)

SET NOCOUNT ON

SET @nextcoli = 0

SET @filename = 'C:\ExcelData.xls'

CREATE TABLE #rowcount (tablename VARCHAR(128), schematable VARCHAR(256), rowcnt INT)
CREATE TABLE #XLCmdlist (xlid INT identity (1,1), cmd VARCHAR(1000))
CREATE TABLE #SQLCmdlist (xlid INT identity (1,1), cmd VARCHAR(3000))

DECLARE tables CURSOR FOR

 SELECT table_schema, table_name, '[' + table_schema + '].[' + table_name + ']' AS table_name FROM information_schema.tables WHERE table_type = 'base table' ORDER BY table_schema + '.' + table_name
OPEN tables
FETCH NEXT FROM tables INTO @schema, @table, @schematable
WHILE @@fetch_status = 0
 BEGIN

    SET @cmd = 'select ''' + @table + ''',''' + @schematable + ''', count(*) from ' + @schematable

    INSERT INTO #rowcount EXEC (@cmd)

    SELECT @cnt = rowcnt FROM #rowcount WHERE tablename = @table AND schematable = @schematable

    IF @cnt > 0

     BEGIN

        SET @nextcoli = 0

        SET @sqlcmd = ''

        INSERT #XLCmdlist (cmd) VALUES ('')

        INSERT #XLCmdlist (cmd) VALUES ('Sheets.Add After:=Sheets(Sheets.Count)')
        INSERT #XLCmdlist (cmd) VALUES ('Sheets(Sheets.Count).Select')
        INSERT #XLCmdlist (cmd) VALUES ('Sheets(Sheets.Count).Name = "' + @schematable + '"')

        DECLARE cols CURSOR FOR

         SELECT column_name FROM Information_Schema.columns WHERE table_schema =
@schema AND table_name = @table ORDER BY ordinal_position
        OPEN cols
        FETCH NEXT FROM cols INTO @col
        WHILE @@fetch_status = 0
         BEGIN
            SET @nextcoli = @nextcoli + 1
            SET @i = @nextcoli
            SET @nextcolc = ''

            WHILE @i > 0

             BEGIN
                SET @j = (@i - 1) % 26
                SET @nextcolc = Char(65 + @j) + @nextcolc
                SET @i = CAST(((@i - @j) / 26) AS INT)
             END

            INSERT #XLCmdlist (cmd) VALUES ('Range("' + @nextcolc + '1").Select')

            INSERT #XLCmdlist (cmd) VALUES ('ActiveCell.FormulaR1C1 = "' + @col +
'"')
            SET @sqlcmd = @sqlcmd + '[' + @col + '], '

            FETCH NEXT FROM cols INTO @col

         END

         CLOSE cols
         DEALLOCATE cols

         SET @sqlcmd = LEFT(@sqlcmd, LEN(@sqlcmd) - 1)

         INSERT #SQLCmdlist (cmd) VALUES ('INSERT INTO
OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=' + @filename + ';' + '''' + ', ' + '''' + 'SELECT ' + @sqlcmd + ' FROM [' + @table + '$]' + '''' + ')')

         INSERT #SQLCmdlist (cmd) VALUES ('SELECT ' + @sqlcmd + ' FROM [' + @table + ']')

         INSERT #SQLCmdlist (cmd) VALUES ('')

      END

  FETCH NEXT FROM tables INTO @schema, @table, @schematable

END
CLOSE tables
DEALLOCATE tables

SELECT cmd FROM #XLCmdlist ORDER BY xlid

SELECT cmd FROM #SQLCmdlist ORDER BY xlid

DROP TABLE #rowcount

DROP TABLE #XLCmdlist
DROP TABLE #SQLCmdlist