How to Import Excel Data into SQL Server? (5 Easy Methods)

Sharing is caring!

Managing business data often requires moving spreadsheets into a structured database system. Microsoft Excel is excellent for analysis, but storing large datasets in SQL Server makes reporting, querying, and automation easier.

If you regularly work with Excel files, knowing how to import Excel data into SQL Server is essential. In this guide, we will explain different ways to perform the import, step by step, with practical examples and tips.

Preparing Excel Data Before Import

Before moving data from Excel to SQL Server, ensure the spreadsheet is ready.

Clean Your Excel Data

• Remove blank rows and unnecessary formatting.
• Ensure column headers have no special characters or spaces.
• Check for duplicate records and fix inconsistencies.

Set Data Types Properly

SQL Server requires correct data types (e.g., INT, VARCHAR, DATE). In Excel, ensure numbers, dates, and text fields are formatted consistently.

Save Excel in Compatible Format

SQL Server works best with:

• .xlsx (Excel 2007 or later)
• .xls (older versions)
• .csv (comma-separated values, widely supported)

Methods to Import Excel Data into SQL Server

There are multiple ways to perform the import depending on your workflow. Let’s explore each method.

1) Import Using SQL Server Import and Export Wizard

The SQL Server Import and Export Wizard is a built-in tool that provides a step-by-step interface.

Steps:

1. Open SQL Server Management Studio (SSMS).
2. Connect to the database where you want to import the data.
3. Right-click the database → Select Tasks → Import Data.
4. In the Data Source window, choose Microsoft Excel.
5. Browse and select the Excel file.
6. Specify the Excel version and whether the first row contains column names.
7. In the Destination, choose SQL Server Native Client.
8. Map Excel columns to SQL Server table columns.
9. Review the summary and click Finish.

Advantages:

• Easy to use with no coding.
• Can import directly into a new or existing table.

Limitations:

• Not suitable for automation.
• Large files may take longer to process.

2) Import Using T-SQL with OPENROWSET

OPENROWSET allows importing Excel data with SQL queries directly. This requires enabling Ad Hoc Distributed Queries.

Enable Ad Hoc Queries:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

Example Query:

SELECT * 
INTO ExcelImportTable
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Data\Sales.xlsx;HDR=YES',
'SELECT * FROM [Sheet1$]');

Key Points:

• HDR=YES means the first row is treated as headers.
• [Sheet1$] specifies the sheet name.

Advantages:

• Fast and direct import.
• Useful for running scheduled scripts.

Limitations:

• Requires driver installation (ACE OLEDB).
• Permissions must be configured.

3) Import Using SQL Server Integration Services (SSIS)

SSIS (SQL Server Integration Services) is a powerful ETL (Extract, Transform, Load) tool for automating imports.

Steps:

1. Open SQL Server Data Tools.
2. Create a new Integration Services Project.
3. Add a Data Flow Task.
4. Select Excel Source → point to the Excel file.
5. Choose OLE DB Destination → map to SQL Server table.
6. Run the package to load data.

Advantages:

• Supports large datasets.
• Allows data transformation before loading.
• Can be automated with SQL Server Agent Jobs.

Limitations:

• Requires SSIS installation.
• Setup may be complex for beginners.

4) Import Using BULK INSERT with CSV Files

If Excel is saved as CSV, you can use BULK INSERT for fast loading.

Example Query:

BULK INSERT SalesData
FROM 'C:\Data\Sales.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2
);

Explanation:

• FIELDTERMINATOR defines column separator.
• ROWTERMINATOR defines end of row.
• FIRSTROW=2 skips header row.

Advantages:

• Very fast for large files.
• Easy to use with automation scripts.

Limitations:

• Only works with CSV format.
• No automatic data type conversion.

5) Import Using PowerShell Script

PowerShell can automate importing Excel files into SQL Server.

Example Script:

# Load Excel data
$ExcelFile = "C:\Data\Sales.xlsx"
$ConnectionString = "Server=SQLServer01;Database=TestDB;Integrated Security=True"

# Import using SQLBulkCopy
$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($ExcelFile)
$Worksheet = $Workbook.Sheets.Item(1)
$Range = $Worksheet.UsedRange.Value2

# Further code to push into SQL Server using SqlBulkCopy

Advantages:

• Automates recurring imports.
• Can schedule with Task Scheduler.

Limitations:

• Requires scripting knowledge.
• More setup compared to wizard.

Choosing the Right Method to Import Excel Data into SQL Server

Different scenarios require different import methods.

Common Issues and Fixes When Importing Excel Data

Even with proper setup, you may face errors. Here’s how to fix them.

Issue 1: Data Type Mismatch

• Problem: Excel column contains mixed values (e.g., numbers and text).
• Fix: Format Excel column consistently before import.

Issue 2: Truncated Data

• Problem: Long text values cut off in SQL Server.
• Fix: Use NVARCHAR(MAX) instead of default VARCHAR(255).

Issue 3: Permission Denied

• Problem: Access denied when using BULK INSERT or OPENROWSET.
• Fix: Ensure SQL Server has read permissions on the folder.

Issue 4: Excel Driver Not Found

• Problem: Missing ACE OLEDB driver.
• Fix: Install Microsoft Access Database Engine.

Best Practices for Importing Excel Data

• Always backup your database before bulk imports.
• Use staging tables for raw imports, then clean data with T-SQL.
• Validate row counts to ensure no data loss.
• Automate recurring imports with SSIS or PowerShell.
• Optimize performance with indexes after loading data.

Final Thoughts

Importing Excel data into SQL Server is a common task for developers, analysts, and database administrators. Depending on your needs, you can use SQL Server Import Wizard, T-SQL OPENROWSET, SSIS packages, BULK INSERT with CSV, or PowerShell scripts.

By preparing your data, choosing the right import method, and following best practices, you can ensure smooth integration between Excel and SQL Server. This not only improves data management but also enhances your ability to run complex queries, generate reports, and make better business decisions.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.