Импорт Excel 2010 в SQL Server

Я использую Excel для сбора и настройки данных, а затем импортирую их в SQL Server 2012 для хранения.

До сих пор я использовал мастер импорта и экспорта SQL Server, но постоянно настраивать его вручную неудобно. Поскольку я использую Express, конечно, он не позволит мне сохранять или даже просматривать фактические команды для передачи данных.

Я попытался настроить связанный сервер в соответствии с как использовать Excel со связанными серверами SQL Server и распределенными запросы, но получите следующую ошибку:

Связанный сервер был создан, но не прошел тест подключения. Вы хотите сохранить связанный сервер?

Исключение возникло при выполнении инструкции или пакета Transact-SQL. (Microsoft.SqlServer.ConnectionInfo)

Не удается инициализировать объект источника данных поставщика OLE DB "Microsoft.Jet.OLEDB.4.0" для связанного сервера "FLTST".
Поставщик OLE DB "Microsoft.Jet.OLEDB.4.0" для связанного сервера "FLTST" вернуло сообщение "Неизвестная ошибка". (Microsoft SQL Server, ошибка: 7303)

Я подумал, что, возможно, проблема была в номере версии Excel, поскольку веб-страница с 2005 года, поэтому я попытался:

  • Excel 8.0 (Excel 2002), как показано на странице
  • Excel 12.0 (Excel 2007), который, похоже, использует мастер
  • Excel 14.0 (Excel 2010) что у меня есть на самом деле

Все они дали мне одинаковые результаты.

Затем я попробовал распределенный запрос, как показано на странице Импорт файла Excel в SQL. Server Express (опять же с разными вариантами строки провайдера)

USE ExTest

SELECT * INTO TstTbl FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 14.0;Database=c:\ExTest.xlsm', [Contacts])

go

Что дает мне следующую ошибку:

Поставщик OLE DB "Microsoft.Jet.OLEDB.4.0" для связанного сервера "(null)" вернул сообщение "Неопределенная ошибка".
Сообщение 7303, уровень 16, состояние 1, строка 3
Не удается инициализировать объект источника данных поставщика OLE DB "Microsoft.Jet.OLEDB.4.0" для связанного сервера "(null)".

Вместо того, чтобы идти на SQL Server и извлекать данные, должен ли я оставаться в Excel и отталкиваться от них?

Что я делаю неправильно?

PS: Пожалуйста, не говорите мне преобразовать его в файл csv! Я стараюсь делать меньше шагов, а не больше!


tsql sql-server-express sql-server-2012-express
person Deina Underhill    schedule 15.03.2013    source источник
comment
это боль, чтобы вручную настроить его постоянно. Это потому, что ваши поля Excel изменились, или потому, что вам не нравится работать с мастером? Вы должны иметь возможность сохранить пакет и повторно запустить его в следующий раз... Я думаю - не уверен на 100% в SQL Express.   -  person Nick.McDermaid    schedule 15.03.2013
comment
В вашей ситуации я бы, вероятно, просто написал небольшое приложение C#, которое читает Excel и извлекает из него необходимые данные, а затем вставляет эти строки в SQL Server. Драйверы Jet OleDB, как известно, сложны в использовании, особенно в 64-разрядной серверной ОС, поэтому я бы по возможности избегал их.   -  person marc_s    schedule 15.03.2013
comment
если ваша схема файла excel не меняется, меняется только имя файла, то вы можете попробовать настроить пакет SSIS с динамическим источником для решения вашей проблемы, вам не нужно будет каждый раз проходить мастер, просто обновите конфигурацию SSIS каждый раз при изменении источника.   -  person ljh    schedule 15.03.2013

Ответы (2)


arrow_upward
2
arrow_downward

Имея такие же проблемы, как и у вас в вашем вопросе, я провел некоторое исследование по этому вопросу. Моя проблема еще не полностью решена, но я думаю, что могу сделать еще один шаг вперед. Хотя вопрос старый, возможно, кому-то еще нужна помощь.

Запустив:

SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;Database=P:\Path\File.xlsx','SELECT * FROM [Sheet1$]');
GO

Я получаю следующее сообщение об ошибке:

Сообщение 15281, уровень 16, состояние 1, строка 19 SQL Server заблокировал доступ к STATEMENT «OpenRowset/OpenDatasource» компонента «Ad Hoc Distributed Queries», поскольку этот компонент отключен как часть конфигурации безопасности для этого сервера. Системный администратор может включить использование «нерегламентированных распределенных запросов» с помощью процедуры sp_configure. Для получения дополнительных сведений о включении «нерегламентированных распределенных запросов» найдите «нерегламентированные распределенные запросы» в электронной документации по SQL Server.

Чтобы решить эту проблему, я запускаю следующее:

sp_configure 'show advanced options', 1  
RECONFIGURE  
GO  
sp_configure 'ad hoc distributed queries', 1  
RECONFIGURE  
GO

Но я получаю новое сообщение об ошибке:

Сообщение 7302, уровень 16, состояние 1, строка 19. Не удается создать экземпляр поставщика OLE DB «Microsoft.ACE.OLEDB.12.0» для связанного сервера «(null)».

Чтобы исправить это, я запускаю:

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

Но вместо этого я получаю эту ошибку:

Сообщение 7438, уровень 16, состояние 1, строка 19. 32-разрядный поставщик OLE DB «Microsoft.ACE.OLEDB.12.0» не может быть загружен в процессе на 64-разрядном SQL Server.

В моем случае я попросил ИТ-отдел установить 64-битную версию Excel на сервер, и я надеюсь, что это положит конец техническим проблемам при импорте из Excel.

Чтобы очистить потом, я отключаю настройки, которые я только что включил:

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0
GO

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 0
GO

sp_configure 'ad hoc distributed queries', 0
RECONFIGURE  
GO

sp_configure 'show advanced options', 0  
RECONFIGURE  
GO
person Taher    schedule 16.07.2014

arrow_upward
0
arrow_downward

  1. Создайте пакет служб SSIS с диспетчером подключения к источнику данных Excel, пункт назначения — SQL Express, пункт назначения OLE DB.
  2. Когда вы создаете диспетчер соединений Excel, вы можете просто использовать один существующий файл Excel.
  3. Определите одну пользовательскую переменную, например user::sourceFile, которая используется для ввода полного пути к файлу Excel.
  4. После создания диспетчера соединений Excel щелкните правой кнопкой мыши -> preperties-> найдите «Выражение», просто укажите свой [User :: sourceFile] в Expression
  5. Просто создайте один простой поток данных от источника к месту назначения.
  6. Сохраните и отладьте свой пакет SSIS, убедитесь, что все учетные данные работают, а данные могут передаваться в целевую таблицу. Примечание: не сохраняйте конфиденциальные данные в своем пакете с зашифрованным ключом машины.
  7. Каждый раз, когда вам нужно загрузить новый файл, используйте DTEXEC для выполнения пакета и переопределите параметр

удачи

person ljh    schedule 15.03.2013
comment
Я вас не забыл, у меня просто не было времени поработать с вашим решением, чтобы попытаться заставить его работать :( - person Deina Underhill; 18.03.2013