How to Insert Data from Excel to SQL Table

Transferring data from a trusty Excel spreadsheet into a SQL database is a common task that can feel a bit intimidating at first. You've got your data neatly organized in rows and columns, but now you need to move it into a powerful, scalable database system. This guide will walk you through a few different methods for inserting data from Excel into a SQL table, from user-friendly wizards to more advanced scripting approaches.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Before You Begin: Preparing Your Excel Data for Import

Clean data is happy data. Before you even think about the import process, spending a few minutes preparing your Excel file can save you hours of troubleshooting later. A pristine source file makes the entire transfer smoother and more reliable. Here’s a simple checklist to run through.

• Consistent Column Headers: Make sure your column headers are in an official header row. They should be simple, descriptive, and contain no special characters or spaces. Use FirstName instead of First Name, for example. These headers will likely become the column names in your SQL table.
• One Data Type Per Column: Scan each column to ensure it contains only one type of data. A column for dates should only have dates, a numeric column should only contain numbers, and so on. Mixed data types are one of the most common causes of import errors.
• Remove Extraneous Information: Get rid of any empty rows, merged cells, subtotals, or flashy formatting. Your data should be a simple, flat table starting from cell A1.
• Standardize Dates: Ensure all dates are in a consistent format, like YYYY-MM-DD. Excel’s date formats can be quirky, and standardizing them helps SQL interpret them correctly.
• Check for Blanks: Decide how you want to handle blank cells. Should they be imported as NULL values in your database or as empty strings? Being aware of them now will help later.

Once your file is clean and tidy, you're ready to choose your import method.

Method 1: The SQL Server Management Studio (SSMS) Import and Export Wizard

If you're using Microsoft SQL Server, the built-in Import and Export Wizard is your best friend. It’s a graphical, step-by-step tool that guides you through the process, making it perfect for beginners or for quick, one-off imports. There's no need to write any code.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step-by-Step Guide to the SSMS Wizard

1. Start the Wizard

In SQL Server Management Studio, connect to your database instance. In the Object Explorer, right-click on the database you want to import data into. From the context menu, navigate to Tasks > Import Data...

2. Choose a Data Source

The first screen of the wizard asks for your data source. Since you're importing from an Excel file, select "Microsoft Excel" from the dropdown menu. Click the "Browse..." button to locate your prepared Excel file. Based on your file type (.xls or .xlsx), choose the correct Excel version from the version dropdown. It's also a good practice to check the box for "First row has column names."

3. Choose a Destination

Next, you’ll specify where the data is going. Select the appropriate SQL Server driver from the destination dropdown — typically "SQL Server Native Client 11.0" works well. The server name should already be populated with your instance name. Confirm your authentication method (either Windows Authentication or SQL Server Authentication).

4. Select Source Tables or Write a Query

Choose "Copy data from one or more tables or views" to proceed. This will allow you to import directly from a spreadsheet tab in Excel. Then, you can map the source (your Excel sheet) to a new or existing destination SQL table. The wizard will display your Excel "tables" — each of which corresponds to sheet names (for example, names like Sheet1$). Select the worksheet you want to import, and on the right side under "Destination", you can select an existing SQL table or let SSMS automatically create a new one based on the sheet name.

5. Review Mappings and Edit If Needed (Important!)

Before proceeding, we recommend clicking on "Edit Mappings…" beside your mapping. This is a critical step because this is where a lot of import errors can creep in due to default settings. The Edit Mappings window lets you review and override the destination column names and data types that the wizard chose for your target SQL database.

For example, if the default data type is too short (such as varchar(50)) and you have longer values in an Excel field, we recommend that you change it to varchar(255) while clicking on this setup step because a bad data mapping can cause truncated (incomplete) data in your import — and you never want that. Be proactive and use this step to define a database table that works for your unique data or your import might just fail altogether.

6. Run the Package Immediately or Save It

Simply make sure the “Run immediately" checkbox is selected to start the process right now. Then you should be good to go. The next page shows a summary of everything you've configured. Give it a final review and click “Finish."

7. Review and Verify Your SQL Insert

The wizard will show the status of each step. Look for green checkmarks! Once it’s done, you may think your work is complete — but you are not. Before calling everything a success, make sure to browse over to your new database table in the Object Explorer to run a quick SELECT query to verify that your data was imported correctly, all columns are present, and the data types match what you expected.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 2: Using the OPENROWSET Function in T-SQL

If you're more comfortable with writing SQL queries or want to automate the import process within a script, the OPENROWSET function is a powerful tool. It lets you query data from an external source — like an Excel file — as if it were a table directly in SQL.

Before you use it, there are two prerequisites:

• You need sysadmin privileges: First, you’ll most likely need to configure your SQL Server options for ad-hoc distributed queries by using SQL Server Management Studio and executing this quick script:
• You’ll need to install office drivers: Second, make sure the machine running SQL Server has the Microsoft Access Database Engine driver installed. Sometimes this is pre-packaged with SQL Server, but not always. If not, you can download the drivers. Just search for Microsoft Access Database Engine 2016 Redistributable, install it on the DB server, and you're good to go.

Here’s what the query looks like:

INSERT INTO TargetDatabase.dbo.YourTargetTable (FirstName, Email, SignUpDate)
SELECT FirstName, Email, SignUpDate
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0, Database=C:\Temp\MyData.xlsx, HDR=YES',
    'SELECT * FROM [Sheet1$]'),

Let's find out how that command works:

• INSERT INTO ...: This specifies the target table and columns where you want to insert the data. The target SQL table must already exist and have columns that align with your select statement. So, your SQL table might have the fields FirstName varchar(255), Email nvarchar(255), and SignUpDate Date.
• OPENROWSET('Provider', 'Connection String', 'Query'): This is the core of the command.

This method offers very granular control but requires a bit more setup and troubleshooting if you’ve never used it before. If permissions aren't properly configured or if the drivers aren't installed on the server correctly, you can spin your wheels on this for hours.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 3: Using BULK INSERT with a CSV File

The BULK INSERT command in T-SQL is incredibly fast and efficient for importing large amounts of data, like from a table with a large file size. The main condition: it works directly with flat text files, not native .xlsx files. To get started, you'll first have to convert your MS Excel data in your targeted spreadsheet to CSV format before you can move forward.

Here's the overall process:

1. Open Excel File and "Save As". Then just pick the CSV format to create a .csv flat-file with your data for importing. Don't overthink things, this step simply converts your structured Excel spreadsheet to a plain text file. A comma-separated values (CSV) format where values are literally separated as they are read with — you guessed it — a comma.
2. Run the BULK INSERT Command With a Query:

BULK INSERT TargetDatabase.dbo.YourTargetTable
FROM 'C:\Temp\MyData.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
),

• FROM: Specifies the full path to your CSV file on the server.
• FIRSTROW = 2: This useful little parameter tells SQL to begin importing at the second row within the flat-file to effectively exclude column headers from being read as data rows.
• FIELDTERMINATOR: Specifies what separator character is being used, like a , (comma), which indicates your flat-file data is separated into distinct fields.
• ROWTERMINATOR: Specifies that the database knows that new rows begin with every new-line character.

Common Issues and Troubleshooting

The most important thing to remember when importing from an unstructured source like Excel into your highly structured SQL server is to plan ahead as much as possible. For example, if you create a new target table that has an existing primary key, then be sure your Excel file's data contains valid columns for new primary keys. If not, you're going to get into a situation where you have duplicates, which the database is not going to accept.

• Data Types do not map: You might encounter errors when trying to import text into a column defined in your database as an INT. To fix it, make sure data types are aligned between Excel and your database.
• Provider is not registered: This error often occurs with OPENROWSET. It simply means the SQL Server can't find the appropriate driver, and to fix it, you have to install the 'Microsoft Access Database Engine Redistributable' on the DB Server.
• Permissions/File path errors: The SQL server account doesn't have access to the location you are trying to import from. You'll need to move your Excel file or CSV to a folder that the SQL Server service account has permission to access.
• Data truncation: This happens when you try to insert a long string of data into a database column that's not long enough to hold it. If you're seeing incomplete data after importing, then just go back to your SQL table and increase the length of its varchar columns.

Final Thoughts

Moving your data from Excel to SQL opens up a world of possibilities for robust data analysis, reporting, and application development. Whether you prefer the straightforward experience of the SSMS wizard or the scripted flexibility of T-SQL commands like OPENROWSET and BULK INSERT, there's a method that fits your needs.

Once your data is in an organized system — whether it’s a SQL database, Google Analytics, or HubSpot — the next step is translating it into actionable insights. At Graphed we help you close that final gap. Instead of spending hours manually creating reports, you can connect your data sources to our platform and use natural language to build real-time dashboards instantly. It's like having a data analyst on your team who can answer any question about your analytics and build any report you need in just a few seconds.