Importing Excel Into SQL Server With the Import Wizard

SQL Server comes with a wizard that imports an Excel sheet in a few clicks, and for a first look at a file it is the quickest way. It is also the way most people end up with a table that is missing a third of its text values, because the wizard decides the type of each column by looking at a handful of rows. This post walks through the wizard, the two errors that stop most first attempts, and the settings that make the result trustworthy. It belongs to the cluster on getting Excel data into a database; the scripted alternative is importing Excel data into SQL Server with OPENROWSET.

Start the wizard

In SQL Server Management Studio (SSMS), right-click the target database in Object Explorer, choose Tasks, then Import Data. The same wizard is available from the Windows Start menu as "SQL Server Import and Export Data", in a 32-bit and a 64-bit version. That distinction matters in a moment.

The steps are:

1. Choose a data source. Select "Microsoft Excel". Browse to the file. Pick the Excel version that matches it, or use the newest entry for .xlsx. Leave "First row has column names" ticked if the sheet has a header in its first row.

2. Choose a destination. Select "Microsoft OLE DB Driver for SQL Server" (or "SQL Server Native Client" on older installs), the server, the authentication, and the database.

3. Specify table copy. Choose "Copy data from one or more tables or views".

4. Select source tables. Each sheet appears as Sheet1$; named ranges and Tables appear without the dollar sign. Choose the sheets and edit the destination table names.

5. Edit mappings. The step that decides the quality of the load; see below.

6. Run immediately, and optionally save an SSIS package.

If you are loading into an existing table, the mappings step shows whether source columns match. If the table does not exist, the wizard creates it.

The two errors that stop first attempts

"The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine." Excel is read through an OLE DB provider, the Access Database Engine (ACE). It is a separate download from Microsoft, the "Access Database Engine Redistributable", and is not installed with SQL Server. Install the version whose bitness matches the wizard you launched: a 64-bit wizard needs the 64-bit provider, and a 32-bit wizard needs the 32-bit one. The two cannot be installed side by side from the normal installer on a machine with 32-bit Office; the installer has a quiet-mode switch for that situation, which carries its own risks, so read the vendor's notes. SSMS itself is 32-bit or 64-bit depending on its version, which is why a wizard launched from the Start menu can behave differently from the one launched inside SSMS.

"External table is not in the expected format." The file is not what its extension says. A CSV or HTML page saved as .xls, or an XLSX picked with the old Excel 97-2003 version in the source dialog, produces this message. Check the real format (XLS versus XLSX versus XLSB), and select the matching version in the dialog.

A third problem is permissions: the wizard runs as you, so the file must be reachable from your session. A file open in Excel is locked, and the wizard may fail with a message about the file being in use. Close it first.

The type-guessing trap

The ACE provider decides each column's type by sampling the first rows of the sheet. By default it inspects eight rows. The sampling is controlled by a setting called TypeGuessRows, stored in the registry for the provider; its default is 8, and a value of 0 makes the provider examine many more rows, up to a documented ceiling. If the first eight rows of a column are numbers, the provider calls the column numeric. Any later cell that holds text is then read as NULL. No error is raised. A phone number column, an id column with a few letter-prefixed values, or a column of comments that begins with numbers are all at risk.

You can see the damage by comparing the count of non-null values in the table with the number of non-blank cells in the sheet. A way to prevent it is to tell the provider to treat mixed columns as text. In a connection string, that is the extended property IMEX=1:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\data\orders.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"

In the wizard, choosing "Microsoft Excel" as the source does not let you edit extended properties directly. If you need IMEX=1, pick ".NET Framework Data Provider for OLE DB" or the "Microsoft OLE DB Provider for ACE" entry as the source (the names differ by version) and type the connection string there, or use the scripted route. Even with IMEX=1, the behavior depends on the registry setting for ImportMixedTypes, which defaults to text, so confirm on your machine with a test sheet that has a number first and text later.

A sturdier option is to avoid the guesswork altogether: make the first data row of the sheet contain text for columns that must be text, or read the sheet with a library that lets you choose (importing an Excel sheet into PostgreSQL with Python shows the pandas version of the same idea).

Edit the mappings

On the mapping page, click Edit Mappings. For each column you see the source type and the destination type the wizard chose. Change them as follows:

• Identifiers and codes: nvarchar(50) or similar, never numeric. The provider reports numeric columns as float, and a float cannot hold a 16-digit id.

• Money: decimal(12,2). The wizard offers float by default, and floating point money is a mistake.

• Dates: datetime2 or date. If the wizard offered nvarchar because the column had mixed content, load as text and convert later.

• Everything uncertain: nvarchar(max) or a generous length. Truncation errors are loud, but only after a long run.

Tick "Drop and re-create destination table" only for staging tables. Look at "Enable identity insert" and the "On error" setting: the default is to fail the whole run on an error, which is what you want while testing. A setting that ignores errors loads whatever it can and hides the rest.

There is a dialog to set what happens on truncation and on conversion errors, which is worth reading once: "Fail", "Ignore" and "Redirect row". Redirecting rows to an error file is the nearest the wizard comes to a rejects table.

Save the package

On the final page, tick "Save SSIS Package" and choose to save it to the file system. The wizard turns your choices into a package that can be opened in Visual Studio, edited and scheduled with SQL Server Agent. A package is the difference between a one-off import and a repeatable job. It also records the mappings, which solves the problem of remembering what you did. Review it before scheduling it: it stores the source path and the connection settings, and a scheduled job runs under a service account that needs access to the folder.

Load into a staging table

As with every route, the safest pattern is not to import into the final table. Import into a staging table with nvarchar columns, then convert in T-SQL:

INSERT INTO dbo.orders (order_no, customer, amount_eur, ordered_on)
SELECT order_no,
       customer,
       TRY_CONVERT(decimal(12,2), amount_eur),
       DATEADD(day, TRY_CONVERT(int, ordered), '1899-12-30')
FROM dbo.stage_orders;

TRY_CONVERT returns NULL where a conversion fails, instead of raising an error. That allows a follow-up query for the rows where the source was not null and the result is:

SELECT * FROM dbo.stage_orders
WHERE amount_eur IS NOT NULL AND TRY_CONVERT(decimal(12,2), amount_eur) IS NULL;

Those are your rejects. The date arithmetic adds the serial number as days to the epoch (converting Excel serial dates to real dates in SQL). Compare the row count of the stage with the number of data rows in the sheet before you convert.

Limits of the wizard

The wizard is interactive, so it cannot run in a pipeline without the package. It reads one machine's files, and in the common case of a server that cannot see your laptop's folders, you copy the file first. It does not understand Tables as structure beyond treating them as ranges. It leaves no record of what happened unless you save logging. For anything that repeats, graduate to a script, a saved SSIS package or a proper ETL job.

When the Import Wizard is the wrong tool for an Excel load

The load recurs. Use a saved package, OPENROWSET, a script or an ETL tool, with a staging table and checks.

The server cannot install the provider. Some managed environments, including Azure SQL Database, do not allow the ACE provider. Convert to CSV and use BULK INSERT or bcp, or load with a script from outside.

The workbook is large. The provider is slow on big sheets. Convert to CSV first and bulk load.

The file is untrusted. Parsing an unknown workbook with a native provider on a database server enlarges the attack surface. Parse it in a sandboxed job and load the result.

Microsoft documents the wizard in its guide to importing data from Excel to SQL Server and the conversion function in the TRY_CONVERT reference. To check a small extract as JSON, use the CSV to JSON converter.