Использование возможностей LinkedServer для импорта Excel-файлов

Добрый день!

Хочу поделиться опытом использования Linked Server в Microsoft SQL Server для импорта Excel-файлов в базу данных.

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

В качестве исходного программного обеспечения имелся Microsoft Office 2010 64-bit, Microsoft SQL Server 2008 R2 Express edition 64-bit.

Для поддержки драйверов офиса необходимо было дополнительно установить Microsoft Access Database Engine 2010 Redistributable.

Обратите внимание, что разрядность установленного офиса, SQL сервера и устанавливаемого драйвера должны быть совместимы, т.е. либо все 32-bit, либо все 64-bit. Если у вас установлен офис 32-bit, а сервер 64-bit, то такую процедуру провернуть не получится.

Далее необходимо настроить некоторые параметры ACE OLE DB драйвера следующим запросом:

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
   

Далее создаем Linked Server следующим запросом:

1. if exists (select 1 from sys.servers where name = 'XlsLnkSrv') exec sp_dropserver @server = 'XlsLnkSrv', @droplogins = 'droplogins'
   <span>
   </span>exec sp_addlinkedserver @server = 'XlsLnkSrv', @srvproduct = 'ACE 12.0', @provider = 'Microsoft.ACE.OLEDB.12.0', @datasrc = N'd:\Гор=-65.xlsx', @provstr = 'Excel 12.0; HDR=No'
   

Где параметр @datasrc = N'd:\Гор=-65.xlsx' - явный путь к файлу Excel, @server = 'XlsLnkSrv' - имя Linked Server. В моем случае в Geobank для параметра @datasrc я использовал подстановочный параметр.

Затем, чтобы получить информацию с любого листа Excel, можно обратиться следующим запросом

1. select * from openquery (XlsLnkSrv, 'Select * from [''2-65$'']')
   

Где '2-65$' - имя рабочего листа в Excel файле.

Чтобы имена всех рабочих листов в файле Excel, можно выполнить команду:

1. sp_tables_ex 'XlsLnkSrv'
   

На данном этапе также можно использовать подстановочный параметр с целью выбора интересующих вас листов.

Используя все описанное выше, нетрудно подготовить скрипт, который будет переносить информацию из листов Excel-файла в нужные буферные или основные таблицы.

Более подробно ознакомиться с Linked Server вы можете на официальном сайте Microsoft.