Exporting SQL Server Query Output to an Excel File

SQL Server tooling is generous with ways to see a result and stingy with ways to get a real Excel file out of one. Management Studio saves the grid as CSV, bcp writes delimited text, and both leave Excel to guess the types when the file is opened. If you want a workbook with true dates, numbers and formatted columns, you write it from a script. This post shows that script with pyodbc and pandas, then covers what the built-in tools do and where they stop. It is a spoke of sending database results to Excel.

The built-in options and what they give

SSMS: Save Results As. In the results grid, right-click and choose "Save Results As", which writes a CSV file. Column headers are included in this path, and "Copy with Headers" does the same for the clipboard. The file is plain text. Types are lost, dates are written in a format that depends on the settings of the machine, and decimals use the machine's decimal separator in some configurations. Open the CSV in Excel and Excel re-guesses every column. SSMS also has a setting for the encoding of saved results; the default has varied between versions, so choose UTF-8 explicitly in the save dialog when you can.

bcp. The bulk copy program writes a query result to a delimited file:

bcp "SELECT order_no, customer, amount, placed_at FROM app.dbo.orders ORDER BY placed_at" queryout orders.csv -c -t, -S myserver -T

-c writes character data, -t, sets the delimiter, -S names the server, and -T uses a trusted connection. The output has no header row and no quoting, so a comma inside a name corrupts the layout. It is fast and suited to machine consumers with a known layout, and unsuited to files for people.

Import and Export Wizard. In SSMS, Tasks, Export Data starts the same wizard used for imports, with "Microsoft Excel" as the destination. It creates a workbook with one sheet per table or query. It needs the ACE provider, as described in importing Excel into SQL Server with the Import Wizard, chooses Excel column types from the SQL types, and does no formatting. Identifiers that are bigint can be written as numbers and lose digits past 15. Treat it as a quick dump.

OPENROWSET writes. It is possible to INSERT into a sheet through OPENROWSET or a linked server, after creating the file with a header row. It needs the ad hoc option and the provider and offers no formatting. It is not worth the setup for new work.

All of these leave Excel to interpret or the provider to guess. A script gives you the control.

The script

Install the driver and the Python packages. The Microsoft ODBC Driver is a separate installation for your operating system; the Python side is:

pip install pandas sqlalchemy pyodbc xlsxwriter

Connect with SQLAlchemy, which pandas supports directly:

import pandas as pd
from sqlalchemy import create_engine, text
from sqlalchemy.engine import URL

url = URL.create(
    "mssql+pyodbc",
    host="myserver",
    database="app",
    query={
        "driver": "ODBC Driver 18 for SQL Server",
        "Encrypt": "yes",
        "TrustServerCertificate": "no",
        "Trusted_Connection": "yes",
    },
)
engine = create_engine(url)

sql = text("""
    SELECT order_no, customer, amount, placed_at, placed_at_offset
    FROM dbo.orders
    WHERE placed_at >= :start AND placed_at < :end
    ORDER BY placed_at, order_no
""")
df = pd.read_sql(sql, engine, params={"start": "2024-03-01", "end": "2024-04-01"})

ODBC Driver 18 encrypts connections by default and validates the server certificate, which is why the connection string mentions Encrypt and TrustServerCertificate. A lab server with a self-signed certificate fails with a certificate error until the certificate is trusted; setting TrustServerCertificate to yes removes the check and should stay out of production. Trusted_Connection=yes uses Windows authentication. For SQL authentication, give a username and password through the URL's username and password arguments and keep the values in the environment.

Fix the types

Check what you received:

print(df.dtypes)

order_no                           object
customer                           object
amount                             object
placed_at                  datetime64[ns]
placed_at_offset    datetime64[ns, UTC+01:00]
dtype: object

Three adjustments, mirroring the other databases:

df["order_no"] = df["order_no"].astype(str)
df["amount"] = df["amount"].astype(float)
df["placed_at_offset"] = df["placed_at_offset"].dt.tz_convert("UTC").dt.tz_localize(None)

decimal and money columns come back as Python Decimal and land in an object column; astype(float) makes them numbers Excel can add. A datetimeoffset column becomes timezone-aware, and Excel cannot store a zone. pandas raises an error when it meets such a column in to_excel. Convert to the zone your readers expect and drop the marker, and say in the header which zone it is. If the column is datetime2 or datetime, it is already naive. Note that SQL Server's time columns arrive as Python time objects, which pandas writes as time-of-day cells.

Keys and codes: a bigint key can exceed 15 digits, and an nvarchar code can have leading zeros. Convert both to str before writing (Excel number precision and the 15 digit limit). A bit column arrives as a boolean; map it to Yes and No if the readers are not technical. uniqueidentifier values arrive as UUID objects or strings depending on the driver settings, and both are best written as text.

Write it

with pd.ExcelWriter("orders_2024-03.xlsx", engine="xlsxwriter", datetime_format="yyyy-mm-dd hh:mm") as writer:
    df.to_excel(writer, sheet_name="Orders", index=False)
    ws = writer.sheets["Orders"]
    money = writer.book.add_format({"num_format": "#,##0.00"})
    ws.freeze_panes(1, 0)
    ws.autofilter(0, 0, len(df), len(df.columns) - 1)
    ws.set_column("A:A", 14)
    ws.set_column("B:B", 30)
    ws.set_column("C:C", 12, money)
    ws.set_column("D:E", 18)

The formatting calls are explained in writing a DataFrame to Excel with formatting. Confirm the file by opening it: dates should show as dates, the amount column should sum in the status bar, and the order numbers should keep their zeros.

If the result is large, avoid pandas' full in-memory copy: use cursor.fetchmany from pyodbc and write rows with XlsxWriter in constant-memory mode, as in exporting a query to Excel from PostgreSQL with Python, whose pattern transfers directly. pyodbc's fast_executemany is for inserts, not for reading.

A PowerShell route

Teams on Windows who prefer PowerShell can use the SqlServer and ImportExcel modules:

Invoke-Sqlcmd -ServerInstance myserver -Database app -Query "SELECT order_no, customer, amount, placed_at FROM dbo.orders" |
  Export-Excel -Path .\orders.xlsx -WorksheetName Orders -AutoSize -FreezeTopRow -AutoFilter

Export-Excel comes from the community ImportExcel module, which writes XLSX without Excel being installed. It has the same type rules: identifiers should be converted to strings first, and dates arrive as dates. A scheduled task can run it under a service account. If your shop uses PowerShell for everything else, this is a valid path, and the checks in this article still apply.

Reporting Services and other tools

If SQL Server Reporting Services (SSRS) or Power BI Report Server is already in use, it can render a report to Excel on a schedule with subscriptions. The export is formatted by the report's design, which suits fixed-layout reports. For data extracts with exact types, a script gives more control, and SSRS exports often contain merged cells and headers that make later imports hard.

Security notes

Run the export under a login with read access to the tables in the query, and nothing else. Keep credentials in the environment or a secret store. Never concatenate user input into the SQL; pass parameters as above. Check what the file will contain: an export that joins in a personal-data table for convenience sends that data to everyone who receives the workbook. Treat free-text columns as untrusted when writing them, so a customer name that begins with an equals sign is not stored as a formula (sending database results to Excel properly every time).

When exporting from SQL Server to Excel with a script is the wrong route

You need a fixed-layout report with grouping and logos. A report server or Power BI paginated report produces that, and a script produces data.

The recipients can query the data themselves. A Power Query connection from Excel to SQL Server refreshes on demand and avoids files that go stale (Power Query for repeatable Excel cleanups).

The consumer is a program. Write CSV or Parquet with bcp or a script, and document the layout.

The result is more than a million rows. Excel cannot hold it. Give an aggregate and make the detail available through a database view (Excel row and column limits).

The tools are documented in Microsoft's pages on the bcp utility and the ODBC driver connection keywords. To check a small extract as JSON, use the CSV to JSON converter.