+1

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

Taras Yaremchenko (Moderator / Admin (RUS)) 6 years ago in Geobank / SQL updated 6 years ago 0

Добрый день!


Хочу поделиться опытом использования 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.