How can I import an Excel file into SQL Server? [closed]
Want to improve this question? Update the question so it focuses on one problem only by editing this post.
Closed 4 years ago .
The community reviewed whether to reopen this question 11 months ago and left it closed:
Original close reason(s) were not resolved
I have data in an Excel file — actually XLSX format since it is now 2020. My requirement is to get this data into SQL Server as follows:
ad hoc, the use case being feeding tables with test data, or infrequent data loads of small amounts of data (say < 3k rows), and
In a repeatable, robust, and possibly automated way for a production system.
2 Answers 2
There are many articles about writing code to import an Excel file, but this is a manual/shortcut version:
If you don’t need to import your Excel file programmatically using code, you can do it very quickly using the menu in SQL Server Management Studio (SSMS).
The quickest way to get your Excel file into SQL is by using the import wizard:
Open SSMS (SQL Server Management Studio) and connect to the database where you want to import your file into.
Import Data: in SSMS in Object Explorer under ‘Databases’, right-click the destination database, and select Tasks, Import Data. An import wizard will pop up (you can usually just click Next on the first screen).
The next window is ‘Choose a Data Source‘. Select Excel:
In the ‘Data Source’ dropdown list, select Microsoft Excel (this option should appear automatically if you have Excel installed).
Click the ‘Browse’ button to select the path to the Excel file you want to import.
Select the version of the Excel file (97-2003 is usually fine for files with a .XLS extension, or use 2007 for newer files with a .XLSX extension)
Tick the ‘First Row has headers’ checkbox if your Excel file contains headers.
- On the ‘Choose a Destination‘ screen, select destination database:
Select the ‘Server name’, Authentication (typically your sql username & password) and select a Database as destination. Click Next.
- On the ‘Specify Table Copy or Query‘ window:
- For simplicity just select ‘Copy data from one or more tables or views’, click Next.
‘Select Source Tables:‘ choose the worksheet(s) from your Excel file and specify a destination table for each worksheet. If you don’t have a table yet the wizard will very kindly create a new table that matches all the columns from your spreadsheet. Click Next.
Немножко всего .. из жизни администратора ms sql server
нет, не нужен, у вас должны стоять Management Studio, при старте Wizart-а будет возможность указать Excel файл.
А как сделать так, чтобы у меня при открытии или обращении к таблице excel из ms sql всегда были актуальные данные?
в каком смысле актуальные данные ? Это вам надо уже заботиться , чтобы в Excel были актуальные данные, далее загружаете в MS SQL Server изапросов сортируете на актуальные данные
Import data from Excel to Microsoft SQL Server in T-SQL language
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
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
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.
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.
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.
Как загрузить в MS SQL Server данные из Excel
Для импорта данных Вам необходима консоль администратора MS SQL Server Managment Studio, которая является компонентой при установке экземпляра MS SQL Server.
Рассмотрим самый простой способ загрузки — через средство экспорта/импорта данных из сторонних форматов. Наиболее распространенная необходимость — это загрузка из файла Excel (.xlsx, .xls).
Для того, чтобы загрузить данные в MS SQL Server из Excel необходимо:
- Открыть консоль MS SQL Server Managment Studio
- Подключиться к серверу:
3. На БД, в которую будет производится загрузка данных, нажать правой кнопкой мыши и выбрать пункт Import Data
4. В появившемся окне SQL Server Import and Export Wizard нажать кнопку Next
5. В следующем окне необходимо в поле Data Source выбрать из списка значение Microsoft Excel, в поле Excel fale path указать путь к файлу Excel, в поле Excel version выбрать версию файла Excel (.xlsx — соответствует MS Excel 2007, .xls — более ранние версии Excel)
Рис.2 — MS SQL Server — Import Data — choose data source
* Если в файле Excel таблица оформлена с шапкой (наименованием столбцов), то необходимо установить галку First row has column names.
Нажать на кнопку Next.
6. В следующем окне необходимо проверить доступа к экземпляру MS SQL Server и БД, в которую будут импортироваться данные
*Есть возможность создать новую БД с помощью кнопки New
Нажать на кнопку Next
7. В следующем окне необходимо выбрать пункт Copy data from one or more tables or views
Нажмите кнопку Next
8. В следующем окне необходимо выбрать листы в файле Excel, которые вы хотите импортировать (загрузить). Например, Лист 1
- Есть возможность предварительного просмотра результата загрузки (кнопка Preview)
Нажать на кнопку Next
9. В следующем окне нажмите кнопку Finish. В случае успешной обработки появится окно:
Нажмите на кнопку Close
Теперь данные из таблицы Excel загружены в БД test MS SQL Server