How to Import Data from Excel to SQL Server

Getting your data out of an Excel spreadsheet and into a SQL Server database is a common but crucial task for anyone moving from simple analysis to more robust computing. This article will guide you through the most popular methods for importing Excel files into SQL Server, breaking down the process step-by-step for both beginners and those who are more comfortable writing code.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Bother Moving Data from Excel to SQL Server?

You might be perfectly happy using Excel for your data, and for many tasks, it’s a brilliant tool. But as your data grows, you'll start running into Excel's limitations. Shifting your data to a SQL Server database isn't just a technical exercise, it solves several real-world problems.

• Scalability and Performance: Imagine your quarterly sales report, Sales_Q4.xlsx, grows to hundreds of thousands of rows. Excel starts to lag, calculations take forever, and the application might even crash. SQL Server is built to handle millions, even billions, of rows with ease, allowing you to run complex queries in seconds, not minutes.
• Data Integrity: In Excel, it's easy to accidentally type "New York" in one cell and "NY" in another, leading to inconsistent data. A SQL database can enforce rules (like data types and constraints) to ensure that a column meant for dates only contains dates, or a numerical column only contains numbers, protecting the quality of your data.
• Concurrent Access: What happens when two team members need to update the sales spreadsheet at the same time? You end up with confusing file versions like Sales_Q4_FINAL_v2_MarksEdits.xlsx. A database allows multiple users to connect and work with the same data simultaneously, safely, and efficiently.
• Security and Control: SQL Server provides granular control over who can see or change specific data. You can give a sales manager read-only access to a results table while only allowing the finance team to modify the numbers, something that's difficult to properly manage in a shared spreadsheet.

Before You Start: Prepping Your Excel File for a Smooth Import

A few minutes of prep work in Excel can save you a mountain of headaches during the import process. SQL Server is much less forgiving than Excel when it comes to messy data. Before you begin, open your spreadsheet and do a quick cleanup.

• One Header Row: Ensure your data starts in the very first row (cell A1) and that this row contains simple, clear column headers. These headers will become the column names in your SQL table. Avoid spaces or special characters, use FirstName instead of "First Name", for example.
• Remove Extraneous Info: Delete any images, graphs, merged cells, pivot tables, subtotals, or empty rows that might be floating around your data. Your goal is a clean, rectangular block of raw data.
• Consistent Data Types: Every column should contain a single type of data. A "SaleDate" column should only have dates, and a "Revenue" column should only have numbers. A common mistake is mixed data, like having "N/A" text in a column that is otherwise numeric. Find and replace these with a null (empty) value or a consistent format.
• Check for Formatting Issues: Excel's "Number" formatting doesn't always translate perfectly. For example, product IDs with leading zeros (like 00123) might get imported as just 123. To prevent this, you can format the column as "Text" in Excel before importing, or be prepared to change the data type to varchar during the import process.
• Create a Named Range (Optional but Recommended): This is a pro-tip that makes things much easier. In Excel, highlight all of your data, including the header row. In the top-left corner (in the Name Box, left of the formula bar), type a simple name for this range, like SalesData, and hit Enter. Now, you can point the import tool directly to this clean range instead of a whole sheet.

Method 1: The SQL Server Import and Export Wizard

For one-time imports or for users who prefer a graphical interface, the built-in SQL Server Import and Export Wizard is the perfect tool. It's user-friendly and walks you through every step of the process.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Launch the Wizard

Open SQL Server Management Studio (SSMS) and connect to your database. In the Object Explorer panel, right-click on the database you want to import your data into. From the context menu, navigate to Tasks > Import Data.... This will launch the SQL Server Import and Export Wizard welcome screen. Click "Next" to get started.

Step 2: Choose Your Data Source

This is where you tell the wizard where the data is coming from.

• In the Data source dropdown, select "Microsoft Excel."
• Click the Browse... button and navigate to your prepared Excel file.
• In the Excel version dropdown, choose the version that matches your file (e.g., "Microsoft Excel 2016" or "Microsoft Excel 2007-2010" for .xlsx files).
• Crucially, check the box that says "First row has column names." This tells the wizard to use your header row to name the columns in the new SQL table.

Step 3: Choose Your Destination

Now, you'll specify where the data is going.

• The Destination should default to a "SQL Server Native Client" option. This is usually correct.
• Confirm the Server name is correct and verify your authentication method (either Windows Authentication or SQL Server Authentication).
• In the Database dropdown, select the target database where you want to create your new table.

Step 4: Select Source Tables and Views

In this step, you can either copy data from an entire sheet or from the named range you created earlier. You’ll have two options:

• Copy a table or view: This is the most common. Select it and click 'Next'. On the following screen, you'll see a list of available Excel sheets (ending in a $) and any named ranges. Select the one you want to import. Creating a named range earlier helps isolate just your data.
• Write an SQL Query to get the data: This is great if you want to import specific columns, e.g., SELECT FirstName, LastName, SaleAmount FROM [SalesData$].

On the right side, you'll see the destination table name. The wizard will propose a name (e.g., Sheet1$), but you should change it to something descriptive like dbo.Sales_2024_Q4.

Step 5: Review Data and Data Type Mapping

Don't skip this step! The wizard detects data types and suggests SQL types for each column.

• Click the Edit Mappings... button to review and adjust.
• Ensure numeric columns are correctly typed (e.g., not as text), date columns are proper datetime, and text columns have enough length.
• Adjust types as needed to prevent data issues.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 6: Run the Package

Choose to save and run the import. For a one-time load, leave "Run immediately" checked, then click "Next" and "Finish." The wizard executes and displays progress. After completion, your Excel data will be in SQL Server as a new table!

Method 2: Using the OPENROWSET Function with T-SQL

If you're comfortable with SQL or need to automate multiple imports, OPENROWSET offers a powerful, scriptable method. It allows querying Excel files as if they were database tables directly from SSMS.

Prerequisite: Enable Ad Hoc Queries

By default, SQL Server may disable ad hoc distributed queries. To enable, run once:

-- You only need to run this configuration block once
sp_configure 'show advanced options', 1,
RECONFIGURE,
sp_configure 'Ad Hoc Distributed Queries', 1,
RECONFIGURE,

Ensure you have the appropriate OLE DB provider installed, such as the Microsoft Access Database Engine 2016 Redistributable. If queries fail about 'Microsoft.ACE.OLEDB.12.0' not being registered, install it from Microsoft.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Querying and Importing Data

To read data from an Excel file:

SELECT * FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0,Database=C:\YourPath\YourFile.xlsx,',
    'SELECT * FROM [SalesData$]'
),

• 'Microsoft.ACE.OLEDB.12.0': provider name
• 'Excel 12.0,Database=...': connection string with file path
• 'SELECT * FROM [SalesData$]': query inside Excel, [SalesData$] refers to sheet or named range

To import data into a new table:

SELECT *
INTO dbo.NewSalesDataTable
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0,Database=C:\YourPath\YourFile.xlsx,',
    'SELECT * FROM [SalesData$]'
),

Or insert into an existing table:

INSERT INTO dbo.ExistingSalesTable (FirstName, Email, SaleDate)
SELECT FName, EmailAddress, TransactionDate
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0,Database=C:\YourPath\Updates.xlsx,',
    'SELECT * FROM [Updates$]'
),

Which Method Should You Use?

Both achieve the same goal—import data from Excel into SQL Server. Choose based on your needs:

• Use the Import/Export Wizard when:
• Use OPENROWSET when:

Final Thoughts

Moving your Excel data into SQL Server is fundamental for scaling your analysis and building a reliable data foundation. Mastering both the Import/Export Wizard and OPENROWSET gives you flexibility: the wizard for quick one-offs, and scripts for automation.

Manual imports are just the start. If you're dealing with disconnected data sources like Excel, HubSpot, Google Analytics, or Salesforce, our tool <a href="https://www.graphed.com/register" target="_blank" rel="noopener">Graphed</a> can automate and centralize this effort. Use natural language queries to quickly build dashboards and insights—freeing you from manual, tedious tasks and enabling faster decision-making.