How to export SQL data to Excel in 3 ways
Eliott Ardisson
Founder & CEO - Basalt Studio
A practical guide for SMBs on three ways to export SQL data to Excel: Power Query, SSMS, and automation tools — with honest trade-offs for each method.
TL;DR
- Three methods cover most SMB scenarios: Excel’s Power Query for recurring reports, SQL Server Management Studio for one-off or complex exports, and automation tools like n8n for high-frequency or multi-stakeholder workflows.
- Your choice should follow export frequency — manual tools work fine once a week, but daily or multi-recipient exports justify the setup cost of automation.
- Each method has a real ceiling: Power Query struggles past roughly 100K rows, SSMS requires SQL knowledge and manual effort each time, and automation tools need upfront configuration.
- Security and access control are often overlooked — direct database connections from individual laptops create risks that centralized approaches avoid.
- Most teams underestimate how much time routine exports consume until they track it for a month.
Why This Is a More Common Pain Point Than It Looks
Most small and mid-sized businesses store their operational data somewhere structured — a CRM, an e-commerce platform, an accounting system, a custom-built SQL database. But when it’s time to report on that data, share it with a client, or make a decision, the tool everyone reaches for is still Excel.
That gap between “data lives in SQL” and “analysis happens in Excel” has to be bridged manually unless someone builds a process around it. At low frequency, that’s fine. But for businesses that export data multiple times a week — a recruitment agency pulling candidate pipeline reports, an accounting firm generating client summaries, a real estate brokerage tracking deal flow — the cumulative cost in staff time adds up quickly.
This guide covers three approaches that actually work for SMBs, what each one costs in time and effort, and the specific scenarios where each one makes sense.
Definitions: Key Terms Before We Get Into the Methods
SQL (Structured Query Language): The standard language for querying relational databases. When someone says “SQL database,” they usually mean MySQL, PostgreSQL, Microsoft SQL Server, or SQLite.
ODBC (Open Database Connectivity): A standard interface that lets Windows applications — including Excel — talk to database systems regardless of vendor. You install a driver, configure a connection, and Excel can treat the database like a data source.
Power Query: A data transformation tool built into Excel (available from Excel 2016 onward). It can pull data from dozens of sources, including SQL databases via ODBC, and refresh that data on demand.
SSMS (SQL Server Management Studio): A free Microsoft tool for managing SQL Server databases. It includes an Import and Export Wizard that can push query results directly into an Excel file.
SSIS (SQL Server Integration Services): A more advanced data integration platform built into SQL Server. The SSMS export wizard can save configurations as SSIS packages, which can then be scheduled.
n8n: An open-source workflow automation tool that can connect to databases, process data, and push results to Excel or other destinations. Relevant for technical teams who want to self-host automation infrastructure.
Method 1: Excel Power Query
Power Query is Excel’s native data import and transformation layer. If you’re on Excel 2016 or later, it’s already installed. For teams that need the same data refreshed on a regular schedule — a weekly pipeline report, a monthly customer summary — this is often the lowest-friction starting point.
What you need
- Excel 2016 or later (Microsoft 365 preferred)
- An ODBC driver for your database type (MySQL, PostgreSQL, SQL Server, etc.)
- Database credentials with read access
- Network access to the server
Setup steps
Install the ODBC driver. Each database type has its own driver. SQL Server drivers usually come pre-installed on Windows. MySQL and PostgreSQL drivers are available from their respective official download pages. Make sure the driver architecture matches your Excel version — a 64-bit Excel installation needs a 64-bit driver.
Configure a Data Source Name (DSN). Search for “ODBC Data Sources” in the Windows Start menu, open the appropriate version (32 or 64-bit), click Add, select your driver, and fill in the server address, database name, and credentials. Test the connection before saving.
Connect from Excel. Go to the Data tab, click Get Data, then From Other Sources, then From ODBC. Select your DSN, authenticate if prompted, and Excel will show you the available tables.
Filter and shape your data. The Power Query Editor lets you remove columns, filter rows, change data types, rename fields, and merge tables — all without writing SQL. For most reporting use cases, this is sufficient.
Load and refresh. Once you click Close and Load, Excel imports the data and maintains a live connection. Right-click the table and select Refresh to pull fresh data whenever you need it.
Where this works well and where it breaks down
Power Query is well-suited for small teams running the same report repeatedly. The connection is persistent, the refresh is one click, and you don’t need SQL skills beyond basic familiarity.
The constraints are real, though. Performance degrades noticeably above roughly 100K rows. Each user needs the ODBC driver configured on their own machine, which becomes a support burden as headcount grows. And the refresh is manual — someone has to open the file and click refresh, or schedule a macro, which is its own layer of fragility.
Method 2: SQL Server Management Studio (SSMS) Export
SSMS is the right tool when you need a one-time or occasional export, when you’re working with large datasets, or when you want precise control over what the output contains. It’s free, it’s fast, and it doesn’t require anything beyond a working SQL Server connection.
What you need
- SQL Server Management Studio (free download from Microsoft)
- Connection credentials to your SQL Server instance
- Appropriate read permissions on the database
How the export works
Connect to your server, open a new query window, write and test the SQL query you want to export, then right-click the database in Object Explorer and go to Tasks > Export Data. The Import and Export Wizard walks you through selecting a data source, choosing Microsoft Excel as the destination, setting the output file path, and mapping your query results to worksheet columns.
For a straightforward export, the whole process takes under fifteen minutes once you know the query you want. For complex outputs involving multiple tables, calculated fields, or conditional categorization, SSMS’s full SQL support means you can do that work in the query itself before the data ever touches Excel.
Scheduling via SQL Server Agent
If you need this to run automatically on a schedule, the wizard offers an option to save the configuration as an SSIS package. SQL Server Agent can then execute that package on a daily, weekly, or custom schedule. This is a viable path for recurring exports without purchasing additional software — though it does require someone comfortable with SSIS to set it up and maintain it.
Limitations to be aware of
SSMS is SQL Server-specific. If your data lives in MySQL, PostgreSQL, or a cloud database, you’ll need a different approach. The wizard also produces static files — it doesn’t email them, upload them to SharePoint, or notify anyone. Every step beyond “file saved to disk” is manual unless you build additional infrastructure around it.
Method 3: Workflow Automation Tools
For businesses that export SQL data multiple times a week, need to distribute results to several stakeholders, or want the process to run without anyone pressing a button, dedicated automation tools are worth the setup investment.
The category, without ranking products
The automation tool landscape includes cloud-based platforms with broad integration libraries as well as self-hosted options that give technical teams more control. For SMBs already in the Microsoft ecosystem, Power Automate has native Excel integration. For teams with a developer or technical ops resource, n8n — which Basalt Studio uses in client deployments — offers open-source flexibility and can be self-hosted, keeping sensitive data off third-party infrastructure.
The right choice depends on your team’s technical comfort, your data sensitivity requirements, and whether you’re building one workflow or a library of them.
What a typical automated workflow looks like
The general structure is consistent regardless of tool:
- Trigger: A time schedule (every Monday at 8 AM), a database event (new records inserted), or a manual button
- Query: Connect to the database, run the SQL, retrieve results
- Transform: Filter, aggregate, calculate derived fields, format dates and numbers
- Generate: Create an Excel file with the formatted output
- Distribute: Email to a distribution list, upload to SharePoint or Google Drive, post a notification to Slack
In practice, a workflow like “pull last week’s sales by region, format into a branded Excel report, email to the sales manager every Monday morning” can be built and tested in a few hours. Once it’s running, it requires no human involvement unless the underlying query or schema changes.
Where automation investment pays off
The setup cost is real. Expect two to four hours for a straightforward workflow, longer for anything with complex logic or multiple outputs. But that cost is fixed. A workflow that runs fifty times over the next year cost a few hours once. A manual process that takes thirty minutes each run costs twenty-five hours over the same period.
McKinsey research on workflow automation has consistently found that repetitive, rule-based tasks — exactly the kind of work that manual SQL-to-Excel exports represent — are among the highest-return targets for automation investment in mid-sized businesses. The productivity gains aren’t dramatic per instance, but they compound across a team over months.
Side-by-Side: Choosing the Right Method
| Factor | Power Query | SSMS Export | Automation Tools |
|---|---|---|---|
| Setup effort | Moderate (ODBC config) | Low | High (initial build) |
| Time per export after setup | 2–5 minutes | 10–20 minutes | Zero (runs unattended) |
| SQL knowledge required | Minimal | Intermediate | Intermediate |
| Works without SQL Server | Yes | No | Yes |
| Handles large datasets well | No (degrades at ~100K rows) | Yes | Depends on tool |
| Distributes output automatically | No | No | Yes |
| Best for | Regular small-team reports | One-off or complex exports | High-frequency or multi-recipient |
Common Mistakes Worth Avoiding
Starting with Power Query and not planning for growth. A 10K-row export today might be 200K rows in eighteen months. If you’re building a reporting workflow you expect to scale, check your data growth trajectory before committing to an approach with a hard ceiling.
Treating SSMS as a set-and-forget solution. SSMS exports are manual by default. Teams often use the wizard successfully for months, then find they’ve inadvertently built a dependency on one person running it each time. If more than one or two people need to trigger exports, document the process or automate it.
Underestimating setup time for automation tools. Automation platforms look simple in demos. In practice, database connections involve credential management, the data transformation layer takes longer than expected, and error handling adds another layer of work. Budget realistically, or bring in someone with prior experience on the platform you’ve chosen.
Storing database credentials in Excel files or shared drives. Direct connections from individual Excel workbooks often involve credentials saved in connection strings. If that file is shared via email or stored on a public-access network drive, those credentials travel with it. Use read-only service accounts for exports, and review where connection details are stored.
Skipping error handling in automated workflows. An automated export that fails silently is worse than a manual one, because no one notices the report didn’t arrive. Any production automation workflow should have error notifications configured — at minimum, an email alert when a run fails.
Practitioner Observations
In work helping founder-led professional services firms set up data workflows, the most common breakdown isn’t technical — it’s the gap between “we built it” and “we own it.” An automated SQL-to-Excel workflow needs someone who understands it well enough to update the SQL query when the schema changes, or to reconfigure the output when the report format evolves. That’s not a reason to avoid automation, but it’s a reason to document what you build and make sure more than one person knows how it works.
The other consistent observation: teams that track the actual time cost of manual exports before choosing a method make better decisions. Thirty minutes twice a week feels manageable until you realize it’s forty-plus hours a year from a skilled employee who could be doing something else.
Implementation Checklist
Power Query path:
- Install and verify the correct ODBC driver for your database
- Configure and test DSN in Windows ODBC Administrator
- Set up Power Query connection in Excel
- Configure data transformations and test refresh
- Document the setup process for other users
SSMS path:
- Verify SSMS installation and test database connection
- Write and validate SQL query
- Run Import and Export Wizard, verify column mappings
- Confirm output file and test in Excel
- Save SQL query somewhere accessible for future exports
Automation path:
- Select a tool appropriate to your team’s technical level
- Set up database connection with a read-only service account
- Build and test the core query and transformation logic
- Configure output format and distribution steps
- Add error handling and failure notifications
- Run end-to-end test before putting in production
- Document the workflow and train at least one other person on it
Getting the Process Right the First Time
These three methods are not mutually exclusive. Many businesses use SSMS for ad-hoc deep-dives, Power Query for a handful of standing reports, and automation for the high-frequency or high-visibility outputs that need to run reliably without someone babysitting them.
The right approach is almost always the simplest one that meets your actual requirements today, with a clear upgrade path as requirements grow. If you’re not sure which category you’re in, spend two weeks tracking how often your team exports SQL data, how long it takes each time, and who’s waiting on the output. That data will make the decision obvious.
If your team is spending meaningful hours each week on repetitive data exports, or if you’ve hit the limits of what manual methods can deliver, a short conversation about your specific workflows is often more useful than more research.
Book an AI strategy call with Basalt Studio to talk through your reporting workflows and figure out where automation would actually move the needle.