Импорт данных в SQL Server или базу данных Azure из Excel

Область применения: SQL Server База данных SQL Azure

Импортировать данные из файлов Excel в SQL Server или базу данных SQL Azure можно несколькими способами. Некоторые методы позволяют импортировать данные за один шаг непосредственно из файлов Excel. Для других методов необходимо экспортировать данные Excel в виде текста (CSV-файла), прежде чем их можно будет импортировать.

В этой статье перечислены часто используемые методы и содержатся ссылки для получения дополнительных сведений. Однако в ней не указано полное описание таких сложных инструментов и служб, как SSIS или Фабрика данных Azure. Дополнительные сведения о решении, интересующем вас, см. по указанным ссылкам.

Список методов

Существует несколько способов импорта данных из Excel. Чтобы использовать некоторые из этих средств, необходимо установить SQL Server Management Studio (SSMS).

Для импорта данных из Excel можно использовать следующие средства:

Если вы хотите импортировать несколько листов из книги Excel, обычно нужно запускать каждое из этих средств отдельно для каждого листа.

Дополнительные сведения см. в разделе ограничения и известные проблемы при загрузке данных в или из файлов Excel.

Мастер импорта и экспорта

Импортируйте данные напрямую из файлов Excel с помощью мастера импорта и экспорта SQL Server. Вы также можете сохранить параметры в виде пакета SQL Server Integration Services (SSIS), который можно изменить и повторно использовать позже.

1. В SQL Server Management Studio подключитесь к экземпляру ядра СУБД SQL Server.

2. Разверните Базы данных.

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

4. Выберите "Задачи".

5. Выберите Импортировать данные или Экспортировать данные:

   

Откроется мастер:

Дополнительные сведения см. в следующих статьях:

• Запуск мастера импорта и экспорта SQL Server
• Начните с этого простого примера мастера импорта и экспорта

Интеграционные службы (SSIS)

Если вы знакомы с SQL Server Integration Services (SSIS) и не хотите запускать мастер импорта и экспорта SQL Server, можно создать пакет служб SSIS, использующий источник Excel и назначение SQL Server в потоке данных.

Дополнительные сведения см. в следующих статьях:

• Источник Excel
• Конечный объект SQL Server

Чтобы научиться создавать пакеты SSIS, см. руководство How to Create an ETL Package (Как создать пакет ETL).

OPENROWSET и связанные серверы

В следующих примерах используется поставщик JET, так как поставщик ACE, включенный в Office, который подключается к источникам данных Excel, предназначен для интерактивного использования на стороне клиента.

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

Импортируйте данные напрямую из файлов Excel в SQL Server с помощью функции Transact-SQL OPENROWSET или OPENDATASOURCE. Такая операция называется распределенный запрос.

Перед выполнением распределенного запроса необходимо включить параметр Ad Hoc Distributed Queries в конфигурации сервера, как показано в примере ниже. Дополнительные сведения см. в разделе "Конфигурация сервера: нерегламентированные распределенные запросы".

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

В приведенном ниже примере кода данные импортируются из листа Excel OPENROWSET в новую таблицу базы данных с помощью Sheet1.

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

Ниже приведен тот же пример с OPENDATASOURCE.

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

Чтобы добавить импортированные данные в существующую таблицу, а не создавать новую, используйте синтаксис INSERT INTO ... SELECT ... FROM ... вместо синтаксиса SELECT ... INTO ... FROM ... из предыдущих примеров.

Для обращения к данным Excel без импорта используйте стандартный синтаксис SELECT ... FROM ....

Дополнительные сведения о распределенных запросах см. в следующих статьях:

• Распределенные запросы¹
• OPENROWSET (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)

¹ Распределенные запросы по-прежнему поддерживаются в SQL Server, но документация по этой функции не обновляется.

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

Кроме того, можно настроить постоянное подключение от SQL Server к файлу Excel как к связанному серверу. В примере ниже данные импортируются из листа Excel Data на существующем связанном сервере EXCELLINK в новую таблицу базы данных SQL Server с именем Data_ls.

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

Вы можете создать связанный сервер из SQL Server Management Studio (SSMS) или запустить системную хранимую процедуру sp_addlinkedserver, как показано в следующем примере.

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.JET.OLEDB.4.0';
SET @datasrc = 'C:\Temp\Data.xls';
SET @provstr = 'Excel 8.0';

EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server,
    @srvproduct,
    @provider,
    @datasrc,
    @location,
    @provstr,
    @catalog;

Дополнительные сведения о связанных серверах см. в следующих статьях:

• Создание связанных серверов (SQL Server ядро СУБД)
• OPENQUERY (Transact-SQL)

Дополнительные примеры и сведения о связанных серверах и распределенных запросах см. в следующей статье:

• Использование Excel со связанными серверами SQL Server и распределенными запросами

Предварительные требования

Чтобы использовать остальные методы, описанные на этой странице (BULK INSERT оператор, инструмент bcp или Azure Data Factory), сначала необходимо экспортировать данные Excel в текстовый файл.

Сохранение данных Excel в виде текста

В Excel выберите "Файл" | Сохранить как и выберите Текст (разделённый табуляцией) (*.txt) или CSV (разделённый запятыми) (*.csv) в качестве типа целевого файла.

Если вы хотите экспортировать несколько листов из книги, выберите каждый лист и повторите эту процедуру. Команда Сохранить как экспортирует только активный лист.

Мастер импорта неструктурированных файлов

Импортируйте данные, сохраненные как текстовые файлы, выполнив инструкции на страницах мастера импорта неструктурированных файлов.

Как описано ранее в разделе "Предварительные требования" , необходимо экспортировать данные Excel в виде текста, прежде чем использовать мастер импорта неструктурированных файлов для импорта.

Дополнительные сведения о мастере импорта неструктурированных файлов см. в разделе Мастер импорта неструктурированных файлов в SQL.

Команда BULK INSERT

BULK INSERT — это команда Transact-SQL, которую можно выполнить в SQL Server Management Studio. В приведенном ниже примере данные загружаются из файла Data.csv с разделителями-запятыми в существующую таблицу базы данных.

Как описано ранее в разделе предварительных требований, необходимо экспортировать данные Excel в виде текста, прежде чем использовать BULK INSERT его для импорта. BULK INSERT Не удается считывать файлы Excel напрямую. BULK INSERT С помощью команды можно импортировать CSV-файл, хранящийся локально или в хранилище BLOB-объектов Azure.

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

Дополнительные сведения и примеры SQL Server и База данных SQL Azure см. в следующих статьях:

• Использование BULK INSERT или OPENROWSET(BULK...) для импорта данных в SQL Server
• BULK INSERT (Transact-SQL)

Инструмент массового копирования (bcp)

Средство bcp запускается из командной строки. В приведенном ниже примере данные загружаются из файла Data.csv с разделителями-запятыми в существующую таблицу базы данных Data_bcp.

Как описано ранее в разделе "Предварительные требования" , необходимо экспортировать данные Excel в виде текста, прежде чем использовать bcp для его импорта. Средство bcp не может напрямую считывать файлы Excel. Используется для импорта в SQL Server или базу данных SQL из текстового файла (CSV), сохраненного в локальном хранилище.

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

Дополнительные сведения о bcp см. в следующих статьях:

• Импорт и экспорт массовых данных с помощью bcp (SQL Server)
• bcp Utility
• Подготовка данных для массового экспорта или импорта

Мастер копирования (ADF)

Импортируйте данные, сохраненные как текстовые файлы, с помощью пошаговой инструкции мастера копирования Фабрики данных Azure (ADF).

Как описано ранее в разделе "Предварительные требования", необходимо экспортировать данные Excel в виде текста, прежде чем использовать Фабрика данных Azure для его импорта. Фабрика данных не может считывать файлы Excel напрямую.

Дополнительные сведения о мастере копирования см. в следующих статьях:

• Мастер копирования Data Factory
• Руководство: Создание пайплайна с операцией копирования с помощью мастера копирования в Data Factory.

Azure Data Factory

Если вы уже работали с Azure Data Factory и не хотите запускать мастер копирования, создайте конвейер с копирующей активностью, которая будет копировать данные из текстового файла в SQL Server или в Azure SQL Database.

Как описано ранее в разделе "Предварительные требования", необходимо экспортировать данные Excel в виде текста, прежде чем использовать Фабрика данных Azure для его импорта. Фабрика данных не может считывать файлы Excel напрямую.

Дополнительные сведения об использовании источников и приемников Data Factory см. в следующих статьях.

• Файловая система
• SQL Server
• База данных SQL Azure

Чтобы начать изучение процесса копирования данных с помощью Azure Data Factory, см. следующие статьи:

• Перемещение данных с помощью действия копирования
• Учебное пособие: Создание конвейера с активностью копирования с использованием портала Azure

Связанный контент

• Приступите с этого простого примера мастера импорта и экспорта
• Импорт данных из Excel или экспорт данных в Excel с помощью служб SQL Server Integration Services (SSIS)
• bcp Utility
• Перемещение данных с помощью действия копирования