Вытягивание данных с вкладки листа Excel в SQL Server

проблема

Мне нужно многократные и повторяющиеся запросы, извлекающие данные из Excel (т. Е. Я хочу запрос, а не импорт). Книга Excel содержит несколько таблиц / таблиц, содержащих таблицы / именованные диапазоны. Мое решение уже работает с MS Access, но я пытаюсь заставить его работать с SQL Server. Я вижу, что это было задано несколько раз ранее, но я не смог заставить его работать.

В прототипировании ниже, файл Excel – Spread1.xlsm; одна вкладка называется «Datasource». Я создал базу данных «ExcelProto» для прототипирования.

Ниже перечислены две приведенные ниже ссылки. Я пробовал как описанный подход ad hoc-запросов , так и подход с подключенным сервером, но оба отказались аналогичным образом. Мой адаптированный код:

Первый подход: прототип Linked Server

USE ExcelLink GO EXEC sp_dropserver @server= 'ExcelLink', @droplogins= 'droplogins'; GO EXEC sp_addLinkedServer @server= N'ExcelLink', @srvproduct= N'ACE 12.0', @provider= N'Microsoft.ACE.OLEDB.12.0', @datasrc= N'C:\TestProgs\Spread1.xlsm', @location= NULL, @provstr= N'Excel 12.0 Macro;HDR=YES', @catalog= NULL; GO SELECT * FROM OPENQUERY (ExcelLink, 'Select * from [Datasource$]'); 

В этом коде прототипа я сначала удалил связанный сервер, созданный в предыдущей попытке выполнения, как вы видите; в любом случае его нельзя было инициализировать.

Поведение и сообщения об ошибке связанного сервера

ACE отображается правильно в дереве Провайдеров. Правильно выполните шаги кода в sp_addLinkedServer, аргументы должным образом оцениваются, а внутренние заявления sp_addLinkedServer отображаются правильно. Но при выходе sp_addLinkedServer выполнение останавливается и отображается сообщение об ошибке:

 OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelLink" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 19 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelLink". 

Второй подход: прототип Ad Hoc Query Ad Hoc Query

 USE ExcelProto GO sp_configure 'show advanced options', 1 GO RECONFIGURE WITH OverRide GO sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE WITH OverRide GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 GO 

Сообщения об установке

 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install. Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install. 

запрос

 SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Macro;Database=C:\TestProgs\Spread1.xlsm;HDR=YES', 'SELECT * FROM [Datasource$]'); 

Сообщения запроса

 OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". 

Вопросов

Такая же или подобная ошибка для обоих подходов. См. Любую проблему с моей адаптацией кода? Если код выглядит нормально, может ли проблема быть назначением разрешений или ролей, и если да, то какие объекты? Это могут быть ограничения в Express? В ссылках используется ACE, но документы Microsoft относятся к Jet для Excel … действительно ли ACE работает для Excel в SQL Server 2017? Есть ли реактивный самолет на 2017 год?

конфигурация

  • Windows 10 Pro x64. Я Администратор.
  • SQL Server Express 2017 x64, SSMS 17.3
  • Установленный Microsoft Access Database Engine 2010 Распространяемый, ссылающийся на ссылки
  • Office 365 / (Excel 2016) 32-разрядный

Рекомендации

Как настроить файл Excel как связанный сервер в SQL Server

Импорт Excel 2010 в SQL Server

Доступ к СУБД

** Обновление 27 октября
Код для OPENROWSET и Linked Server, показывающий шаги регистрации и инициализации: **

OPENROWSET

 USE ExcelProto GO /* Configure OLEDB */ sp_configure @configname='Show Advanced Options', @configvalue=1; RECONFIGURE WITH OverRide; GO sp_configure @configname='Ad Hoc Distributed Queries', @configvalue=1; RECONFIGURE WITH OverRide; GO EXEC master.sys.sp_MSset_oledb_prop @provider_name=N'Microsoft.ACE.OLEDB.12.0', @property_name=N'AllowInProcess', @property_value=1; GO EXEC master.sys.sp_MSset_oledb_prop @provider_name=N'Microsoft.ACE.OLEDB.12.0', @property_name=N'DynamicParameters', @property_value=1; GO /* Pull in each Excel worksheet/table */ SELECT * FROM OPENROWSET( N'Microsoft.ACE.OLEDB.12.0', N'Excel 12.0 Xml; Database=C:\TestProgs\Spread3.xlsx; HDR=YES; IMEX=1', 'SELECT * FROM [Datasource$]' ); GO 

Взял sp_MSset_oledb_prop из master.sys вместо master.dbo, надеюсь, что все в порядке; они выполняются должным образом.

Связанный сервер и OPENQUERY

 USE ExcelProto GO /* Configure OLEDB */ sp_configure @configname='Show Advanced Options', @configvalue=1; RECONFIGURE WITH OverRide; GO sp_configure @configname='Ad Hoc Distributed Queries', @configvalue=1; RECONFIGURE WITH OverRide; GO EXEC master.sys.sp_MSset_oledb_prop @provider_name=N'Microsoft.ACE.OLEDB.12.0', @property_name=N'AllowInProcess', @property_value=1; GO EXEC master.sys.sp_MSset_oledb_prop @provider_name=N'Microsoft.ACE.OLEDB.12.0', @property_name=N'DynamicParameters', @property_value=1; GO /* Delete prior instances of Linked Server to each worksheet/table */ EXEC sp_dropserver @server= 'ExcelLink', @droplogins= 'droplogins'; GO /* Create a Linked Server to each Excel worksheet/table */ EXEC sp_addLinkedServer @server= N'ExcelLink', @srvproduct= N'Excel', @provider= N'Microsoft.ACE.OLEDB.12.0', @datasrc= N'C:\TestProgs\Spread3.xlsx', @location= NULL, @provstr= 'Excel 12.0 Xml;HDR=YES;IMEX=1;', @catalog= NULL; GO /* Pull in each Excel worksheet/table */ SELECT * FROM OPENQUERY (ExcelLink, 'Select * from [Sheet1$]'); 

Регистрации и инициализации.

Access Database Engine 2010 был установлен, никаких ошибок при установке. Записи реестра верны для Microsoft.ACE.OLEDB.12.0, на компьютере \ HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Microsoft SQL Server \ MSSQL14.SQLEXPRESS \ Providers \ Microsoft.ACE.OLEDB.12.0

Тем не менее, как подход OPENROWSET, так и Linked Server приводят к появлению сообщений
OPENROWSET:
Msg 7303, уровень 16, состояние 1, строка 27 Не удается инициализировать объект источника данных поставщика OLE DB «Microsoft.ACE.OLEDB.12.0» для связанного сервера «(null)».

Связанный сервер:
Msg 7303, уровень 16, состояние 1, строка 44 Не удается инициализировать объект источника данных поставщика OLE DB «Microsoft.ACE.OLEDB.12.0» для связанного сервера «ExcelLink».

Поэтому ни один из них не может инициализировать Microsoft.ACE.OLEDB.12.0.

Altho нет ошибок при установке Access Database Engine 2010, установка Office 365 – 32-разрядная (рекомендуется MS config!). Прежде чем переустановить это, я попытаюсь использовать последний SQL выше на машине без установки Office.

Можете ли вы попробовать это так?

 SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml; Database=C:\DataFiles\EmployeeData1.xlsx', [vEmployee$]); 

ИЛИ

 SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\DataFiles\EmployeeData1.xlsx; Extended Properties=Excel 12.0 Xml')...[vEmployee$] 

;

С заголовком:

 SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml; HDR=YES; Database=C:\DataFiles\EmployeeData1.xlsx', [vEmployee$]); 

Без заголовка:

 SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml; HDR=NO; Database=C:\DataFiles\EmployeeData1.xlsx', [vEmployee$]); 

https://www.red-gate.com/simple-talk/sql/t-sql-programming/questions-about-using-tsql-to-import-excel-data-you-were-too-shy-to- просить/

Я обновляю свой оригинальный пост здесь. , ,

Проверьте эту ссылку:

https://sqlwithmanoj.com/2012/07/10/querying-excel-2010-from-sql-server-in-64-bit-environment/

Кроме того, я знаю, что людям здесь не нравятся ТОЛЬКО ссылки, поэтому я добавлю дополнительную информацию с сайта, указанного выше.

 So let's first of all enable this: USE [MSDB] GO sp_configure 'show advanced options', 1 GO RECONFIGURE WITH OverRide GO sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE WITH OverRide GO You can also enable this setting graphically by going to “Surface Area Configuration” and enable it. –> Now, to access the Excel file there are 2 ways: 1. Directly fetch records from Excel by using the OPENROWSET() function by providing the providers and other options 2. Indirectly by creating a Linked Server first of all, then: 2.a. fetching records from Excel by using OPENQUERY() function or 2.b. by using the Linked Server name within the SELECT query -- 1. Directly, by using OPENROWSET() function SELECT * FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=E:\SQL Server - Blogs\LinkedServer2010.xlsx;HDR=YES;IMEX=1', 'SELECT * FROM [Sheet1$]' ); -- OR -- -- 2. Indirectly, by Creating Linked Server & using OPENQUERY: EXEC sp_addLinkedServer @server= N'XLSX_2010', @srvproduct = N'Excel', @provider = N'Microsoft.ACE.OLEDB.12.0', @datasrc = N'E:\SQL Server - Blogs\LinkedServer2010.xlsx', @provstr = N'Excel 12.0; HDR=Yes'; GO -- 2.a. Using OPENQUERY() function: SELECT * FROM OPENQUERY (XLSX_2010, 'Select * from [Sheet1$]') -- 2.b. Using the Linked Server name within the SELECT query: SELECT * FROM XLSX_2010...[Sheet1$] I searched on net and I got following solution in MSDN forums to register the ACE OLEDB 12.0 provider: USE [master] GO EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1 GO EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1 GO 
  • Импорт данных из Excel в Sql Server 2008 # 2
  • Преобразовать дату в формате Excel «25-OCT-13 01.08.24.732000000 PM» в DateTime в Sql Server 2008 R2
  • C # Как обнаружить Alt + Enter из ячейки Excel и импортировать в SQL Server
  • Обходной путь для экспорта данных в Excel с более чем 255 столбцами
  • Экспорт SQL Server в Excel 2010 больше 255 символов
  • Хотите выполнить sp_send_dbmail и отправить результаты в формате excel
  • Автоматизация импорта данных из листа excel в SQL-сервер с использованием python
  • Экспорт в Excel с SQL Server через OPENROWSET с использованием кода .Net
  • База данных на основе таблицы с добавленным вручную столбцом - сохранение значений позиций
  • Ошибка SSIS при импорте столбца даты Excel и подачи в медленно изменяющийся размер
  • Активировать конкретный лист Excel в задаче скрипта SSIS
  • Interesting Posts

    Встроенные изображения в html-письме из Excel VBA

    ArrayFormula, которая ссылается на предыдущую строку? (Ошибка круговой зависимости)

    Случайный элемент из условного списка

    Чтение файла Excel 2010 – ячейки в C #

    Excel Power Query: добавление столбца, связанного с именованным диапазоном

    Создать список имен переменных массива Sub

    Имеет ли Excel встроенный метод для разбора формул? (т. е. получить список включенных ссылок RANGE)

    Скрытие рабочего листа при копировании и вставке

    Как скопировать RTF-текст в буфер обмена для использования в excel

    «Вы хотите открыть или сохранить», не отображая

    Excel: удаление повторяющихся значений с использованием формулы массива для нескольких столбцов

    VBA Импорт нескольких листов в книгу

    выбирать значение в определенном порядке из диапазона ячеек

    Как вставить Excel Isblank для всей формулы?

    Фильтр листков VBA Loop с использованием нескольких переменных

    Давайте будем гением компьютера.