Import data from Excel to Microsoft SQL Server in T-SQL language.

Preamble

Microsoft SQL Server allows you to import data from an Excel file into a database using the built-in T-SQL language in an SQL query. Today I will tell you in detail how it is done, what conditions need to be fulfilled to make this operation successful, tell you about the features of import for the most common cases of SQL server configurations and give specific procedures and practical examples.

Import of data from Excel to Microsoft SQL Server

I will start by saying that you can import data from Excel to Microsoft SQL Server using “Distributed Queries” and “Linked Servers”. You probably already know this, as I have written about it more than once (references to the relevant materials are given above).

You can access the Excel file and import data into Microsoft SQL Server using T-SQL instructions OPENDATASOURCE, OPENROWSET or OPENQUERY.

However, in the above articles I have missed several important points, one of which is that all SQL Server configurations are different, due to which many have different problems and errors during the execution of distributed queries and calls to related servers.

I also described the way to download data from Excel, which is now outdated, so today I will try to give you a little more information on how to import data from Excel file to Microsoft SQL Server in T-SQL language.

Introduction

So, as I said, the configuration of the SQL server plays a very important role here, in particular, which version of the server is installed, x86 or x64.

If we talk about the latest versions of Microsoft SQL Server 2016-2019, they are only x64 and are installed on 64-bit versions of Windows.

On this basis, I will divide the article into several parts, in each of which I will tell you about features of importing data from Excel for the most common configuration cases and give you a specific order of action.

In order to quickly find out which version of SQL Server is installed on your computer, you can make a simple SQL query

SELECT @@VERSION;

Access to the Excel file and, accordingly, data import into Microsoft SQL Server is performed by special providers (vendors). To work with Excel in Microsoft SQL Server are usually used:

• Jet.OLEDB.4.0
• ACE.OLEDB.12.0

In all examples below, I will send a simple SELECT query to select data from an Excel file to check access to the data in the Excel file. To import data (upload data to the database), you can use any method convenient for you, e.g. SELECT INTO or INSERT INTO construction.

In addition, it is recommended to close the Excel file when accessing it in distributed queries, as well as to specify the path to the file without spaces (although modern SQL server can work with spaces).

Import data from Excel 2003 (xls file) into Microsoft SQL Server x86

Step 1 – Check for Microsoft.Jet.OLEDB.4.0 provider on SQL Server

The first thing we need to start with is to check if Microsoft.Jet.OLEDB.4.0 provider is registered on SQL Server, because in this case we need to use that provider. This can be done using the following SQL instruction

EXEC sp_enum_oledb_providers;

The resulting dataset should contain a string with Microsoft.Jet.OLEDB.4.0. If there is no such provider, then most likely there is no Excel 2003 installed in the system and, accordingly, it should be installed.

Step 2 – Granting user rights to a temporary directory

The peculiarity of distributed queries and work with related Excel servers in x86 versions of SQL Server is that regardless of the name of which account sends an SQL query to Excel, this account must have rights to write to the temporary directory of the account under which the SQL Server service itself operates.

Since the OLE DB vendor creates a temporary file during the query in the temporary directory of SQL Server using the credentials of the user executing the query.

Thus, if the SQL Server service runs on behalf of either a local or a network service, it is necessary to give the appropriate permissions to the temporary directory of these services to all users who will send distributed queries and contact the associated Excel server (if the server runs on behalf of the user who sends SQL queries, then such permissions are not required, it already has them).

This can be done using the built-in command line utility icacls.

For example, for a local service, the command will look like this.

icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)

For network service

icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)

In place of UserName, provide the name of the user who sends the request.

Step 3 – Enable distributed queries on SQL Server

By default, the ability to use distributed queries, particularly the OPENDATASOURCE and OPENROWSET functions, is prohibited in Microsoft SQL Server, so this feature must be enabled first.

It is enabled using the system stored procedure sp_configure, which is responsible for system parameters of the server. We need to set the Ad Hoc Distributed Queries parameter to 1, to do this we execute the following SQL instruction.

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

Step 4 – Execute SQL query, access to Excel file

Below I will give you some options for accessing the Excel file (TestExcel.xls).

OPENROWSET

SELECT * FROM OPENROWSET
(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0; Database=D:\TestExcel.xls',
'SELECT * FROM [List 1$]'.
);

OPENDATASOURCE

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0'),
'Data Source=D:\TestExcel.xls;
Extended Properties=Excel 8.0')...[List1$];
Linked Server

--Creation of a linked server
EXEC sp_addlinkedserver @server = 'TEST_EXCEL',
@srvproduct = 'Excel',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'D:\TestExcel.xls',
@provstr = 'Excel 8.0;IMEX=1;HDR=YES;';

--Security settings (authorization)
EXEC dbo.sp_addlinkedsrvlogin @rmtsrvname='TEST_EXCEL',
@useself= 'False',
@locallogin=NULL,
@rmtuser=NULL,
@rmtpassword=NULL;

-- Address to the associated server
SELECT * FROM OPENQUERY (TEST_EXCEL, 'SELECT * FROM [List1$]');
--or 
SELECT * FROM TEST_EXCEL...[List1$];

Import of data from Excel 2007 and higher (xlsx file) into Microsoft SQL Server x86

Step 1 – Check for Microsoft.ACE.OLEDB.12.0 provider on SQL Server

Just like in the previous example, we first check if we have the ISP we need installed, in this case we need Microsoft.ACE.OLEDB.12.0.

EXEC sp_enum_oledb_providers;

Step 2 – Installing Microsoft.ACE.OLEDB.12.0 (32-bit) Provider

If there is no provider, it must be installed. Here’s a link to the ISP download: https://www.microsoft.com/en-us/download/details.aspx?id=13255

Select and download the file corresponding to the x86 architecture (i.e. in the name without x64).

Step 3 – Granting user rights to a temporary directory

In this case, we also give rights to the temporary directory of local or network service to all users who will send SQL queries to the Excel file.

We use the same command line utility icacls.

For local service:

icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)

For network service:

icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)

In place of UserName, provide the name of the user who sends the request.

Step 4 – Enable Distributed Queries on SQL Server

Enable the ability to use OPENDATASOURCE and OPENROWSET on Microsoft SQL Server, I repeat that this feature is disabled by default.

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

Step 5 – Configuring Microsoft.ACE.OLEDB.12.0 provider

In this case you will need to additionally configure the provider Microsoft.ACE.OLEDB.12.0. To do this, enable the following provider parameters (specify 0 instead of 1 to disable).

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

If these parameters are not included, an error is likely to appear, approximately the following

“Message 7399, level 16, state 1, line 25.
The provider of OLE DB “Microsoft.ACE.OLEDB.12.0” for the associated server “(null)” reported an error. The provider did not provide the error data.
Message 7330, level 16, state 2, line 25
We failed to get the string from the OLE DB provider “Microsoft.ACE.OLEDB.12.0” for the associated server “(null)”.

Step 6 – Execute SQL query, access to Excel file

Examples of accessing the Excel file (TestExcel.xlsx).

OPENROWSET

SELECT * FROM OPENROWSET
(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;
Database=D:\TestExcel.xlsx',
'SELECT * FROM [List 1$]'.
);

OPENDATASOURCE

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0'),
'Data Source=D:\TestExcel.xlsx;
Extended Properties=Excel 12.0')...[List1$];

Linked Server

--Creation of a linked server
EXEC sp_addlinkedserver @server = 'TEST_EXCEL',
@srvproduct = 'Excel',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'D:\TestExcel.xlsx',
@provstr = 'Excel 12.0;IMEX=1;HDR=YES;';

--Security settings (authorization)
EXEC dbo.sp_addlinkedsrvlogin @rmtsrvname='TEST_EXCEL',
@useself= 'False',
@locallogin=NULL,
@rmtuser=NULL,
@rmtpassword=NULL;

-- Address to the associated server
SELECT * FROM OPENQUERY (TEST_EXCEL, 'SELECT * FROM [List1$]');
-or 
SELECT * FROM TEST_EXCEL...[List1$];

Import of data from Excel (any files) into Microsoft SQL Server x64

Step 1 – Check for Microsoft.ACE.OLEDB.12.0 provider on SQL Server

In this case we also use Microsoft.ACE.OLEDB.12.0 provider, first check if it is registered on the server.

EXEC sp_enum_oledb_providers;

Step 2 – Installing Microsoft.ACE.OLEDB.12.0 (64-bit) provider

In case the provider is not installed, it must be downloaded and installed: https://www.microsoft.com/en-us/download/details.aspx?id=13255

Download the x64 file.

Step 3 – Enable distributed queries on SQL Server

There is also a need to enable the ability to use distributed queries (OPENDATASOURCE and OPENROWSET) on Microsoft SQL Server x64, so first enable it by following exactly the same instruction.

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

Step 4 – Configuring Microsoft.ACE.OLEDB.12.0 provider

In this case, most likely, the provider configuration is not required, so first try to execute SQL queries (refer to data in Excel), and if an error occurs (all with the same message 7399 and 7330), then try to enable the parameters AllowInProcess and DynamicParameters (to disable, specify 0 instead of 1).

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

Step 5 – Execute SQL query, access to Excel file

Here the same parameters are used in SQL queries as in the previous example. For convenience, I will duplicate them once again.

Examples of accessing the Excel file (TestExcel.xlsx):

OPENROWSET

SELECT * FROM OPENROWSET
(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;
Database=D:\TestExcel.xlsx',
'SELECT * FROM [List 1$]'.
);

OPENDATASOURCE

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0'),
'Data Source=D:\TestExcel.xlsx;
Extended Properties=Excel 12.0')...[List1$];

Linked Server

--Creation of a linked server
EXEC sp_addlinkedserver @server = 'TEST_EXCEL',
@srvproduct = 'Excel',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'D:\TestExcel.xlsx',
@provstr = 'Excel 12.0;IMEX=1;HDR=YES;';

--Security settings (authorization)
EXEC dbo.sp_addlinkedsrvlogin @rmtsrvname='TEST_EXCEL',
@useself= 'False',
@locallogin=NULL,
@rmtuser=NULL,
@rmtpassword=NULL;

--Address to the associated server
SELECT * FROM OPENQUERY (TEST_EXCEL, 'SELECT * FROM [List1$]');
--or 
SELECT * FROM TEST_EXCEL...[List1$];

Summing up

Finally, I will group the actions to be performed depending on the release of SQL Server (x68 or x64) and the version of the Excel file (xls or xlsx) into one table for your convenience.

That’s all for today, good luck in learning T-SQL!

Import data from Excel to SQL Server table

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.