Импорт данных из Excel в SQL Server или базу данных AzureImport data from Excel to SQL Server or Azure SQL Database

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL AzureнетХранилище данных SQL AzureнетParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Импортировать данные из файлов Excel в SQL Server или базу данных SQL Azure можно несколькими способами.There are several ways to import data from Excel files to SQL Server or to Azure SQL Database. Некоторые методы позволяют импортировать данные за один шаг непосредственно из файлов Excel. Для других методов необходимо экспортировать данные Excel в виде текста, прежде чем их можно будет импортировать.Some methods let you import data in a single step directly from Excel files; other methods require you to export your Excel data as text before you can import it. В этой статье перечислены часто используемые методы и содержатся ссылки для получения дополнительных сведений.This article summarizes the frequently used methods and provides links for more detailed information.

Список методовList of methods

Для импорта данных из Excel можно использовать следующие средства:You can use the following tools to import data from Excel:

Экспорт сначала в текстExport to text first Непосредственно из ExcelDirectly from Excel
Мастер импорта неструктурированных файловImport Flat File Wizard мастер импорта и экспорта SQL ServerSQL Server Import and Export Wizard
Инструкция BULK INSERTBULK INSERT statement Службы SQL Server Integration ServicesSQL Server Integration Services (SSIS)
BCPBCP Функция OPENROWSETOPENROWSET function
Мастер копирования (Фабрика данных Azure)Copy Wizard (Azure Data Factory)
Фабрика данных Azure.Azure Data Factory
   

Если вы хотите импортировать несколько листов из книги Excel, обычно нужно запускать каждое из этих средств отдельно для каждого листа.If you want to import multiple worksheets from an Excel workbook, you typically have to run each of these tools once for each sheet.

Этот список не дает полного описания таких сложных инструментов и служб, как SSIS или фабрика данных Azure.A complete description of complex tools and services like SSIS or Azure Data Factory is beyond the scope of this list. Дополнительные сведения об интересующем вас решении доступны по ссылкам ниже.To learn more about the solution that interests you, follow the provided links.

Важно!

Дополнительные сведения о подключении к файлам Excel, а также об ограничениях и известных проблемах, связанных с загрузкой данных в файлы этого приложения и из них, см. в разделе Загрузка данных в приложение Excel или из него с помощью служб SQL Server Integration Services (SSIS).For detailed info about connecting to Excel files, and about limitations and known issues for loading data from or to Excel files, see Load data from or to Excel with SQL Server Integration Services (SSIS).

Если у вас не установлен SQL Server или SQL Server есть, но нет SQL Server Management Studio, см. статью Скачивание SQL Server Management Studio (SSMS).If you don't have SQL Server installed, or you have SQL Server but don't have SQL Server Management Studio installed, see Download SQL Server Management Studio (SSMS).

Мастер импорта и экспорта SQL ServerSQL Server Import and Export Wizard

Импортируйте данные напрямую из файлов Excel, выполнив инструкции на страницах мастера импорта и экспорта SQL Server.Import data directly from Excel files by stepping through the pages of the SQL Server Import and Export Wizard. При необходимости сохраните параметры в виде пакета служб SQL Server Integration Services (SSIS), доступного для настройки и многократного применения в будущем.Optionally, save the settings as a SQL Server Integration Services (SSIS) package that you can customize and reuse later.

  1. В SQL Server Management StudioSQL Server Management Studioподключитесь к экземпляру SQL ServerSQL Server Компонент Database EngineDatabase Engine.In SQL Server Management StudioSQL Server Management Studio, connect to an instance of the SQL ServerSQL Server Компонент Database EngineDatabase Engine.

  2. Разверните узел Базы данных.Expand Databases.

  3. Щелкните базу данных правой кнопкой мыши.Right-click a database.

  4. Наведите указатель мыши на пункт Задачи.Point to Tasks.

  5. Выберите один из следующих параметров:Click one of the following options.

    • Импорт данныхImport Data
    • Экспорт данныхExport Data

    Запуск мастера SSMS

Подключение к источнику данных Excel

Пример использования мастера для импорта из Excel в SQL Server см. в разделе Get started with this simple example of the Import and Export Wizard (Начало работы с помощью простого примера использования мастера импорта и экспорта).For an example of using the wizard to import from Excel to SQL Server, see Get started with this simple example of the Import and Export Wizard.

Сведения о других способах запустить мастер импорта и экспорта см. в разделе Запуск мастера импорта и экспорта SQL Server.To learn about other ways to launch the Import and Export wizard, see Start the SQL Server Import and Export Wizard.

Службы SQL Server Integration ServicesSQL Server Integration Services (SSIS)

Если вы работали со службами SSIS и не хотите запускать мастер экспорта и импорта SQL Server, создайте пакет SSIS, который использует для потока данных источник Excel и назначение SQL Server.If you're familiar with SSIS and don't want to run the SQL Server Import and Export Wizard, create an SSIS package that uses the Excel Source and the SQL Server Destination in the data flow.

Дополнительные сведения о компонентах SSIS см. в указанных ниже статьях.For more info about these SSIS components, see the following topics:

Чтобы научиться создавать пакеты SSIS, см. руководство How to Create an ETL Package (Как создать пакет ETL).To start learning how to build SSIS packages, see the tutorial How to Create an ETL Package.

Компоненты потока данных

OPENROWSET и связанные серверыOPENROWSET and linked servers

Примечание

В Azure функции OPENROWSET и OPENDATASOURCE доступны только в Управляемом экземпляре Базы данных SQL.In Azure, the OPENROWSET and OPENDATASOURCE functions are available only on SQL Database Managed Instance.

Примечание

Поставщик ACE (прежнее название — поставщик Jet), который подключается к источникам данных Excel, предназначен для интерактивного клиентского использования.The ACE provider (formerly the Jet provider) that connects to Excel data sources is intended for interactive client-side use. Если поставщик ACE используется на сервере, особенно в автоматизированных процессах или процессах, выполняющихся параллельно, вы можете получить непредвиденные результаты.If you use the ACE provider on the server, especially in automated processes or processes running in parallel, you may see unexpected results.

Распределенные запросыDistributed queries

Импортируйте данные напрямую из файлов Excel с помощью функции Transact-SQL OPENROWSET или OPENDATASOURCE.Import data directly from Excel files by using the Transact-SQL OPENROWSET or OPENDATASOURCE function. Такая операция называется распределенный запрос.This usage is called a distributed query.

Перед выполнением распределенного запроса необходимо включить параметр ad hoc distributed queries в конфигурации сервера, как показано в примере ниже.Before you can run a distributed query, you have to enable the ad hoc distributed queries server configuration option, as shown in the following example. Дополнительные сведения см. в статье ad hoc distributed queries Server Configuration Option (Параметр конфигурации сервера "ad hoc distributed queries").For more info, see ad hoc distributed queries Server Configuration Option.

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

В приведенном ниже примере кода данные импортируются из листа Excel Sheet1 в новую таблицу базы данных с помощью OPENROWSET.The following code sample uses OPENROWSET to import the data from the Excel Sheet1 worksheet into a new database table.

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

Ниже приведен тот же пример с OPENDATASOURCE.Here's the same example with OPENDATASOURCE.

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

Чтобы добавить импортированные данные в существующую таблицу, а не создавать новую, используйте синтаксис INSERT INTO ... SELECT ... FROM ... вместо синтаксиса SELECT ... INTO ... FROM ... из предыдущих примеров.To append 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.

Для обращения к данным Excel без импорта используйте стандартный синтаксис SELECT ... FROM ....To query the Excel data without importing it, just use the standard SELECT ... FROM ... syntax.

Дополнительные сведения о распределенных запросах см. в указанных ниже разделах.For more info about distributed queries, see the following topics:

Связанные серверыLinked servers

Кроме того, можно настроить постоянное подключение к файлу Excel как к связанному серверу.You can also configure a persistent connection to the Excel file as a linked server. В примере ниже данные импортируются из листа Excel Data на существующем связанном сервере Excel EXCELLINK в новую таблицу базы данных с именем Data_ls.The following example imports the data from the Data worksheet on the existing Excel linked server EXCELLINK into a new database table named Data_ls.

USE ImportFromExcel;
GO
SELECT * INTO Data_ls FROM EXCELLINK...[Data$];
GO

Вы можете создать связанный сервер в SQL Server Management Studio или запустить системную хранимую процедуру sp_addlinkedserver, как показано в примере ниже.You can create a linked server from SQL Server Management Studio, or by running the system stored procedure sp_addlinkedserver, as shown in the following example.

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 parameter values
SET @server =     'EXCELLINK'
SET @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:

Примеры и дополнительные сведения о связанных серверах и распределенных запросах см. указанных ниже разделах.For more examples and info about both linked servers and distributed queries, see the following topics:

Предварительное требование — сохранение данных Excel как текстаPrerequisite - Save Excel data as text

Чтобы использовать другие методы, описанные на этой странице (инструкцию BULK INSERT, средство BCP или фабрику данных Azure), сначала экспортируйте данные Excel в текстовый файл.To use the rest of the methods described on this page - the BULK INSERT statement, the BCP tool, or Azure Data Factory - first you have to export your Excel data to a text file.

В Excel последовательно выберите Файл | Сохранить как и выберите как целевой тип файла Текст (разделитель — табуляция) (*.txt) или CSV (разделитель — запятая) (*.csv) .In Excel, select File | Save As and then select Text (Tab-delimited) (*.txt) or CSV (Comma-delimited) (*.csv) as the destination file type.

Если вы хотите экспортировать несколько листов из книги, выполните эту процедуру для каждого листа.If you want to export multiple worksheets from the workbook, select each sheet and then repeat this procedure. Команда Сохранить как экспортирует только активный лист.The Save as command exports only the active sheet.

Совет

Чтобы оптимизировать использование средств импорта, сохраняйте листы, которые содержат только заголовки столбцов и строки данных.For best results with data importing tools, save sheets that contain only the column headers and the rows of data. Если сохраненные данные содержат заголовки страниц, пустые строки, заметки и пр., позже при импорте данных вы можете получить непредвиденные результаты.If the saved data contains page titles, blank lines, notes, and so forth, you may see unexpected results later when you import the data.

Мастер импорта неструктурированных файловThe Import Flat File Wizard

Импортируйте данные, сохраненные как текстовые файлы, выполнив инструкции на страницах мастера импорта неструктурированных файлов.Import data saved as text files by stepping through the pages of the Import Flat File Wizard.

Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете импортировать их с помощью мастера импорта неструктурированных файлов.As described previously in the Prerequisite section, you have to export your Excel data as text before you can use the Import Flat File Wizard to import it.

Дополнительные сведения о мастере импорта неструктурированных файлов см. в разделе Мастер импорта неструктурированных файлов в SQL.For more info about the Import Flat File Wizard, see Import Flat File to SQL Wizard.

Команда BULK INSERTBULK INSERT command

BULK INSERT — это команда Transact-SQL, которую можно выполнить в SQL Server Management Studio.BULK INSERT is a Transact-SQL command that you can run from SQL Server Management Studio. В приведенном ниже примере данные загружаются из файла Data.csv с разделителями-запятыми в существующую таблицу базы данных.The following example loads the data from the Data.csv comma-delimited file into an existing database table.

Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете использовать BULK INSERT для их импорта.As described previously in the Prerequisite section, you have to export your Excel data as text before you can use BULK INSERT to import it. BULK INSERT не может считывать файлы Excel напрямую.BULK INSERT can't read Excel files directly.

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

Дополнительные сведения см. в указанных ниже разделах.For more info, see the following topics:

Средство BCPBCP tool

BCP — это программа, которая запускается из командной строки.BCP is a program that you run from the command prompt. В приведенном ниже примере данные загружаются из файла Data.csv с разделителями-запятыми в существующую таблицу базы данных Data_bcp.The following example loads the data from the Data.csv comma-delimited file into the existing Data_bcp database table.

Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете использовать BCP для их импорта.As described previously in the Prerequisite section, you have to export your Excel data as text before you can use BCP to import it. BCP не может считывать файлы Excel напрямую.BCP can't read Excel files directly.

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

Дополнительные сведения о программе BCP см. в указанных ниже разделах.For more info about BCP, see the following topics:

Мастер копирования (Фабрика данных Azure)Copy Wizard (Azure Data Factory)

Импортируйте данные, сохраненные как текстовые файлы, выполнив инструкции на страницах мастера копирования Фабрики данных Azure.Import data saved as text files by stepping through the pages of the Azure Data Factory Copy Wizard.

Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете использовать фабрику данных Azure для их импорта.As described previously in the Prerequisite section, you have to export your Excel data as text before you can use Azure Data Factory to import it. Фабрика данных не может считывать файлы Excel напрямую.Data Factory can't read Excel files directly.

Дополнительные сведения о мастере копирования см. в указанных ниже разделах.For more info about the Copy Wizard, see the following topics:

Фабрика данных AzureAzure Data Factory

Если вы уже работали с фабрикой данных Azure и не хотите запускать мастер копирования, создайте конвейер с действием копирования из текстового файла в SQL Server или Базу данных SQL Azure.If you're familiar with Azure Data Factory and don't want to run the Copy Wizard, create a pipeline with a Copy activity that copies from the text file to SQL Server or to Azure SQL Database.

Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете использовать фабрику данных Azure для их импорта.As described previously in the Prerequisite section, you have to export your Excel data as text before you can use Azure Data Factory to import it. Фабрика данных не может считывать файлы Excel напрямую.Data Factory can't read Excel files directly.

Дополнительные сведения об использовании этих источников и приемников фабрики данных см. в указанных ниже разделах.For more info about using these Data Factory sources and sinks, see the following topics:

Чтобы научиться копировать данные с помощью фабрики данных Azure, см. указанные ниже разделы.To start learning how to copy data with Azure data factory, see the following topics:

Распространенные ошибкиCommon errors

"Microsoft.ACE.OLEDB.12.0" не зарегистрированMicrosoft.ACE.OLEDB.12.0" has not been registered

Эта ошибка возникает, так как не установлен поставщик OLE DB.This error occurs because the OLEDB provider is not installed. Установите его через Распространяемый пакет ядра СУБД Microsoft Access 2010.Install it from Microsoft Access Database Engine 2010 Redistributable. Не забудьте установить 64-разрядную версию, если Windows и SQL Server — 64-разрядные.Be sure to install the 64-bit version if Windows and SQL Server are both 64-bit.

Полный текст ошибки.The full error is:

Msg 7403, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

Не удалось создать экземпляр поставщика OLE DB "Microsoft.ACE.OLEDB.12.0" для связанного сервера "(null)".Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Это означает, что Microsoft OLEDB не был настроен должным образом.This indicates that the Microsoft OLEDB has not been configured properly. Чтобы устранить проблему, выполните приведенный ниже код Transact-SQL.Run the following Transact-SQL code to resolve this:

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

Полный текст ошибки.The full error is:

Msg 7302, Level 16, State 1, Line 3
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

32-разрядный поставщик OLE DB "Microsoft.ACE.OLEDB.12.0" не может быть загружен в процессе на 64-разрядной версии SQL Server.The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.

Это происходит, когда 32-разрядная версия поставщика OLD DB устанавливается вместе с 64-разрядной версией SQL Server.This occurs when a 32-bit version of the OLD DB provider is installed with a 64-bit SQL Server. Чтобы устранить эту проблему, удалите 32-разрядную версию и вместо нее установите 64-разрядную версию поставщика OLE DB.To resolve this issue, uninstall the 32-bit version and install the 64-bit version of the OLE DB provider instead.

Полный текст ошибки.The full error 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-bit SQL Server.

Поставщик OLE DB "Microsoft.ACE.OLEDB.12.0" для связанного сервера "(null)" сообщил об ошибке.The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Поставщик не предоставил данных об ошибке.The provider did not give any information about the error.

Не удалось проинициализировать объект источника данных поставщика OLE DB "Microsoft.ACE.OLEDB.12.0" для связанного сервера "(null)".Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Обе эти ошибки обычно указывают на ошибку разрешений между процессом SQL Server и файлом.Both of these errors typically indicate a permissions issue between the SQL Server process and the file. Убедитесь, что учетная запись, с которой выполняется служба SQL Server, имеет разрешение на полный доступ к файлу.Ensure that the account that is running the SQL Server service has full access permission to the file. Мы не рекомендуем импортировать файлы с настольного компьютера.We recommend against trying to import files from the desktop.

Полный текст ошибки.The full errors are:

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

См. также:See Also

Импорт данных из Excel или экспорт данных в Excel с помощью служб SQL Server Integration Services (SSIS)Import data from Excel or export data to Excel with SQL Server Integration Services (SSIS)