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 last year 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.
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.
How to import data into a SQL database from Excel
Importing data in SQL database is playing an important role when working with SQL servers. There are various techniques and tools to facilitate data entry into the SQL database. This article will explain how to import data in an SQL database from an Excel file by using two methods:
-
(SQL Server Import and Export data wizard)
Import data in SQL database via SQL Server Import and Export data wizard
SQL Server Management Studio allows users to import data from different data sources, which will be explained in this chapter.
On SQL Server Management Studio launch, the Connect to Server window will be opened. Choose the Server name and the type of Authentication, provide credentials, and click the Connect button:
When SSMS is connected to the chosen instance of SQL Server, right-click on the desired database and navigate to Tasks > Import data option from the Tasks submenu:
That action will open the SQL Server Import and Export Wizard window. The first step of this process gives us a brief overview of what the wizard does. It’s designed to help users import and export data between many popular data formats including databases, spreadsheets, and text files. By clicking on the Next button, the wizard will go to the next step:
The Choose a Data Source step will be the next on the journey through import data in SQL database with this wizard. The source from which data will be copied will be selected in this step.
From the Data source drop-list list, choose Microsoft Excel as the source. The section below will be changed with options following the selected data source. In the Excel connection settings section, the path to an Excel file will be chosen by clicking on the Browse button:
The data from the dbo.Export_data Excel file will be used for importing in the desired database:
When the data source is chosen, click on the Next button to continue. The following warning message might be shown:
- The operation could not be completed.
Additional information:
The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)
This warning message is usually encountered on a 64-bit operating system in a combination with the 32-bit version of SQL Server Management Studio. To bypass this issue, close SSMS, go to the Start menu, find and open the 64-bit version of SQL Server Import and Export Wizard:
Like on the 32-bit version, the same welcome step will be presented with the exact same steps. When everything previously mentioned in the article has been set, from the Choose a Data Source window click on the Next button.
Choose a destination step will be next in which the Destination where the data will be copied to will be set. From the Destination drop-down list, choose SQL Server Native Client 11.0:
With the selected destination, the section below the Destination drop-list will change automatically. Here, the Server name, type of Authentication, and the Database need to be set. Click on the Next button when all is specified:
Moving on, in the Specify Table Copy or Query step, there are two options available:
- Copy data from one or more tables or views
- Write a query to specify the data to transfer
The Copy data from one or more tables or views option will be selected in this case. Click on the Next button to continue the process of importing data in SQL Database:
The Select Source Tables and Views step is next in this wizard. It allows users to chosen one or more tables and views to copy data. For this article, dbo.Export_data table is selected. Furthermore, it allows users to edit mappings by clicking on the Edit mappings button, and to see how the imported data will look in the database by clicking on the Preview button. Click Next to continue the process of importing data in SQL database:
In the Save and Run Package step, the user can choose whether to save the SSIS package. The default option is Run immediately. Click on the Next button to continue the importing process:
Last but not least, the Complete the Wizard is the last step in the processing of import data in SQL database. Here, a summary of the choices that were made through the process of importing data is listed. Verify that everything is okay and click Finish to end the process:
The execution was successful message is shown with a brief status of the done operation. Click Close to exit the wizard:
The targeted database will be populated with the newly created table and data as can be seen below:
Import data in SQL database via ApexSQL Pump
ApexSQL Pump is a database pump tool, which allows the users to easily export or import data in SQL database and reverse.
On ApexSQL Pump launch, the New project window will be shown. In the Data source tab, choose Server, the type of the Authentication, and the Database. For this article, the AW2019 database will be used. Click Next to continue:
Under the Action tab, the Import action will be selected. Click OK to connect to the targeted database:
The main window with tables and views from the targeted database will appear in the main window:
To import data in SQL database, click on the Manage button in the Home tab:
The Manage import window will be opened. Click on the Add button in the Format tab to add the external file for import:
The Add import source window will appear where users can choose between the Database or the File data source for importing. The File option will be checked for purpose of this article. When checked, and File option with the Browse for folder button will appear:
Click on this option in which the file for import data in SQL database will be selected. In this case, dbo.Excel_data Excel file will be selected. Click Open to continue:
Click OK to close the Add Import source window:
When the import source is added, on the right side of the Manage import window, additional options for an imported data source will appear. In our case, options for the Excel file.
From Import by section, the option for Rows will be ticked by default, and in the Header section, the First row in range option will be checked. The Preview window shows how imported data will look in real-time. When all is set, click OK to continue:
The view will go back to the main grid, where columns from the chosen table will be used for mapping columns from the imported file, in this case, the dbo.Customer table will be used:
The next step is to go into the Settings window on the right side of the main window.
From the Mapping drop-down list, choose the previously selected dbo.Export_data Excel file. Then under the Table section, click on the Create new option, and in the field below type the name of the new table. Last, from the Import mode section, click on the Insert new option:
Moving on, in the main grid, go to the selected table, and from the Column mapping drop-down list choose which columns will be paired, for example, Customer_ID column with ID column in imported file source:
When all columns from the imported source are mapped with propriety column from the selected table, click on the Process button in the Home tab:
The job summary window will be opened, whereby clicking on the Import button, import data in SQL database process will be started:
The View results window with all results will be presented. This window also allows the users to Export results or Create report as shown below:
Back to SSMS, do a Select Top 1000 Rows command from right-click in the Object Explorer to verify that the imported data in SQL database was successful:
Как загрузить в 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