Import information from Excel to SQL Server or Azure SQL Database

  • Article
  • nine minutes to read

Applies to: yesSQL Server (all supported versions) YesAzure SQL Database

In that location are several ways to import information from Excel files to SQL Server or to Azure SQL Database. Some methods let you lot import data in a single step direct from Excel files; other methods require you to export your Excel data as text (CSV file) before you can import information technology.

This article summarizes the frequently used methods and provides links for more detailed information. A complete description of complex tools and services like SSIS or Azure Information Factory is beyond the scope of this article. To learn more nigh the solution that interests you, follow the provided links.

List of methods

There are a number of methods to import information from Excel. You may need to install SQL Server Direction Studio (SSMS) to apply some of these tools.

Y'all can apply the following tools to import information from Excel:

Export to text offset (SQL Server and SQL Database) Directly from Excel (SQL Server on-premises only)
Import Flat File Magician SQL Server Import and Export Magician
Bulk INSERT statement SQL Server Integration Services (SSIS)
BCP OPENROWSET function
Re-create Sorcerer (Azure Data Manufacturing plant)
Azure Data Manufactory

If you want to import multiple worksheets from an Excel workbook, you typically accept to run whatsoever of these tools once for each sheet.

Import and Export Sorcerer

Import data directly from Excel files by using the SQL Server Import and Export Wizard. You also have the option to save the settings every bit a SQL Server Integration Services (SSIS) package that you can customize and reuse later on.

  1. In SQL Server Management Studio, connect to an instance of the SQL Server Database Engine.

  2. Expand Databases.

  3. Right-click a database.

  4. Indicate to Tasks.

  5. Choose to Import Information or Export Data:

    Start wizard SSMS

This launches the wizard:

Connect to an Excel data source

To larn more than, review:

  • Start the SQL Server Import and Export Wizard
  • Get started with this unproblematic case of the Import and Export Wizard

Integration Services (SSIS)

If you're familiar with SQL Server Integration Services (SSIS) and don't want to run the SQL Server Import and Export Wizard, create an SSIS packet that uses the Excel Source and the SQL Server Destination in the information period.

To learn more, review:

  • Excel Source
  • SQL Server Destination

To start learning how to build SSIS packages, run into the tutorial How to Create an ETL Package.

Components in the data flow

OPENROWSET and linked servers

Note

The ACE provider (formerly the Jet provider) that connects to Excel data sources is intended for interactive client-side apply. If you lot use the ACE provider on SQL Server, particularly in automated processes or processes running in parallel, yous may come across unexpected results.

Distributed queries

Import data directly into SQL Server from Excel files by using the Transact-SQL OPENROWSET or OPENDATASOURCE function. This usage is called a distributed query.

Before you lot can run a distributed query, you have to enable the advert hoc distributed queries server configuration option, as shown in the following instance. For more than info, see ad hoc distributed queries Server Configuration Option.

              sp_configure 'prove avant-garde options', 1; RECONFIGURE; Go sp_configure 'Advertizement Hoc Distributed Queries', i; RECONFIGURE; GO

The following code sample uses OPENROWSET to import the information from the Excel Sheet1 worksheet into a new database table.

              Utilise ImportFromExcel; Get SELECT * INTO Data_dq FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',     'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]); GO

Here'southward the same example with OPENDATASOURCE.

              Utilise ImportFromExcel; Become SELECT * INTO Data_dq FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',     'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]; Get

To suspend the imported data to an existing table instead of creating a new table, use the INSERT INTO ... SELECT ... FROM ... syntax instead of the SELECT ... INTO ... FROM ... syntax used in the preceding examples.

To query the Excel data without importing it, just employ the standard SELECT ... FROM ... syntax.

For more info about distributed queries, see the following topics:

  • Distributed Queries (Distributed queries are still supported in SQL Server 2019, but the documentation for this feature has not been updated.)
  • OPENROWSET
  • OPENDATASOURCE

Linked servers

You tin likewise configure a persistent connectedness from SQL Server to the Excel file as a linked server. The following case imports the information from the Data worksheet on the existing Excel linked server EXCELLINK into a new SQL Server database table named Data_ls.

              Utilize ImportFromExcel; Go SELECT * INTO Data_ls FROM EXCELLINK...[Data$]; Get

You tin create a linked server from SQL Server Direction Studio, or by running the system stored procedure sp_addlinkedserver, as shown in the following instance.

              DECLARE @RC int  DECLARE @server     nvarchar(128) DECLARE @srvproduct nvarchar(128) DECLARE @provider   nvarchar(128) DECLARE @datasrc    nvarchar(4000) DECLARE @location   nvarchar(4000) DECLARE @provstr    nvarchar(4000) DECLARE @catalog    nvarchar(128)  -- Set up parameter values SET @server =     'EXCELLINK' Set up @srvproduct = 'Excel' SET @provider =   'Microsoft.ACE.OLEDB.12.0' SET @datasrc =    'C:\Temp\Data.xlsx' SET @provstr =    'Excel 12.0'  EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog

For more info about linked servers, see the following topics:

  • Create Linked Servers
  • OPENQUERY

For more examples and info about both linked servers and distributed queries, see the following topics:

  • How to apply Excel with SQL Server linked servers and distributed queries
  • How to import data from Excel to SQL Server

Prerequisite - Salvage Excel data as text

To use the rest of the methods described on this page - the BULK INSERT statement, the BCP tool, or Azure Data Factory - beginning y'all have to export your Excel information to a text file.

In Excel, select File | Salve As and so select Text (Tab-delimited) (*.txt) or CSV (Comma-delimited) (*.csv) equally the destination file type.

If you want to consign multiple worksheets from the workbook, select each sheet and then repeat this process. The Salve equally control exports only the active sheet.

Tip

For best results with data importing tools, save sheets that contain only the column headers and the rows of data. If the saved information contains page titles, blank lines, notes, and and then along, you may run into unexpected results later when you import the information.

The Import Flat File Wizard

Import data saved every bit text files by stepping through the pages of the Import Flat File Wizard.

As described previously in the Prerequisite section, you have to export your Excel data as text before y'all can use the Import Flat File Wizard to import it.

For more info about the Import Flat File Sorcerer, run into Import Apartment File to SQL Wizard.

BULK INSERT command

BULK INSERT is a Transact-SQL command that you tin can run from SQL Server Management Studio. The following example loads the data from the Information.csv comma-delimited file into an existing database table.

As described previously in the Prerequisite department, yous accept to export your Excel data every bit text before you can use Majority INSERT to import information technology. Bulk INSERT tin't read Excel files straight. With the BULK INSERT command, you can import a CSV file that is stored locally or in Azure Blob storage.

              USE ImportFromExcel; Go BULK INSERT Data_bi FROM 'C:\Temp\data.csv'    WITH (       FIELDTERMINATOR = ',',       ROWTERMINATOR = '\n' ); GO

For more than info and examples for SQL Server and SQL Database, see the following topics:

  • Import Bulk Information by Using Bulk INSERT or OPENROWSET(Majority...)
  • BULK INSERT

BCP tool

BCP is a programme that you run from the command prompt. The post-obit example loads the data from the Information.csv comma-delimited file into the existing Data_bcp database table.

As described previously in the Prerequisite section, you lot have to export your Excel data as text before y'all can apply BCP to import information technology. BCP tin can't read Excel files directly. Apply to import into SQL Server or SQL Database from a test (CSV) file saved to local storage.

Important

For a text (CSV) file stored in Azure Blob storage, use BULK INSERT or OPENROWSET. For an examples, run across Example.

              bcp.exe ImportFromExcel..Data_bcp in "C:\Temp\data.csv" -T -c -t ,

For more info virtually BCP, run across the post-obit topics:

  • Import and Export Bulk Data by Using the bcp Utility
  • bcp Utility
  • Set up Information for Majority Export or Import

Copy Magician (ADF)

Import information saved as text files by stepping through the pages of the Azure Data Factory (ADF) Copy Magician.

Every bit described previously in the Prerequisite section, you have to consign your Excel data as text earlier you can apply Azure Data Factory to import information technology. Data Mill can't read Excel files directly.

For more than info virtually the Re-create Wizard, see the following topics:

  • Information Manufactory Re-create Wizard
  • Tutorial: Create a pipeline with Copy Activity using Data Manufacturing plant Copy Magician.

Azure Information Factory

If you're familiar with Azure Information Manufactory and don't desire to run the Copy Wizard, create a pipeline with a Re-create activity that copies from the text file to SQL Server or to Azure SQL Database.

Equally described previously in the Prerequisite section, you have to export your Excel data every bit text before you can use Azure Data Factory to import information technology. Data Factory can't read Excel files directly.

For more info nearly using these Data Factory sources and sinks, see the following topics:

  • File system
  • SQL Server
  • Azure SQL Database

To starting time learning how to copy data with Azure data factory, see the following topics:

  • Move data past using Copy Action
  • Tutorial: Create a pipeline with Copy Activity using Azure portal

Common errors

Microsoft.ACE.OLEDB.12.0" has non been registered

This error occurs because the OLEDB provider is not installed. Install information technology from Microsoft Access Database Engine 2010 Redistributable. Exist sure to install the 64-flake version if Windows and SQL Server are both 64-bit.

The full error is:

              Msg 7403, Level sixteen, State one, Line 3 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has non been registered.

Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(cypher)"

This indicates that the Microsoft OLEDB has non been configured properly. Run the following Transact-SQL lawmaking to resolve this:

              EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', one EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', North'DynamicParameters', 1

The full error is:

              Msg 7302, Level 16, State one, Line iii Cannot create an example of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(cipher)".

The 32-chip OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server

This occurs when a 32-fleck version of the OLD DB provider is installed with a 64-bit SQL Server. To resolve this issue, uninstall the 32-bit version and install the 64-bit version of the OLE DB provider instead.

The full mistake is:

              Msg 7438, Level 16, State 1, Line 3 The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-fleck SQL Server.

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(cypher)" reported an error.

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(nada)"

Both of these errors typically indicate a permissions issue between the SQL Server procedure and the file. Ensure that the business relationship that is running the SQL Server service has total access permission to the file. We recommend against trying to import files from the desktop.

The full errors are:

              Msg 7399, Level xvi, Country 1, Line 3 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(zippo)" reported an error. The provider did non give any data about the error.                          
              Msg 7303, Level xvi, State 1, Line 3 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Next steps

  • Become started with this unproblematic instance of the Import and Consign Wizard
  • Import data from Excel or export data to Excel with SQL Server Integration Services (SSIS)
  • bcp Utility
  • Move information by using Copy Activeness