Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 И снова импорт из Excel  [new]
enemyclosed
Member

Откуда:
Сообщений: 17
Перечитал массу топиков, вопрос в очередной раз об Excel, импорте данных в MS SQL из него.
Имеется MS SQL 2012, Microsoft.ACE.OLEDB.12.0 установлен, EXEC sp_configure 'Ad Hoc Distributed Queries', '1'; выполнено, EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1, EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1 - выполнено.
Пытаюсь подцепиться запросом типа:

select F1, F2, F3, F4, F5, F6, substring (F4, charindex ('с номера ', F4)+9, 6) as F7, substring (F4, charindex ('на номер ', F4)+9, 11) as F8  
from openrowset('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 xml; HDR=NO; Database=D:\Telephonecall\Детализация\012016.xlsx', 'select * from [Лист$]')

Получаю ошибки:

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

Подскажите куда копать?

P.S. Машина локальная, учетка NT Service\MSSQLSERVER, права на папку полные у нее.
2 авг 16, 12:31    [19486266]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4213
enemyclosed,
попробуйте задвоить слеши

Database=D:\\Telephonecall\\Детализация\\012016.xlsx
2 авг 16, 12:34    [19486286]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
enemyclosed
Member

Откуда:
Сообщений: 17
Задвоил, те же ошибки.
2 авг 16, 12:37    [19486315]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4213
enemyclosed
Задвоил, те же ошибки.


Еще поставить N перед текстовыми полями

charindex (N'с номера '
2 авг 16, 12:39    [19486333]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
enemyclosed
Member

Откуда:
Сообщений: 17
И букву N поставил, и все одно ошибки те же.
2 авг 16, 12:59    [19486471]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4213
enemyclosed,

[Лист$] попробуйте заменить на [List$], так как он назван внутри Excel, а не как имя вкладки листа.
Вообще русские имена папок, листов, полей и ексель-книг это зло по определению.
2 авг 16, 13:03    [19486493]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4213
Так попробуйте

select *  
from openrowset('Microsoft.ACE.OLEDB.12.0', N'Excel 12.0 xml; HDR=NO; Database=D:\Telephonecall\Детализация\012016.xlsx', N'select * from [Лист$]')
2 авг 16, 13:05    [19486501]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
enemyclosed
Member

Откуда:
Сообщений: 17
Не прокатило. Технически понимаю, что выполнил все необходимые действия, чтобы эта процедура заработала, но вот чем отличается 2012 от 2008 не пойму.
2 авг 16, 13:11    [19486543]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
enemyclosed
Member

Откуда:
Сообщений: 17
Ошибки те же.
2 авг 16, 13:12    [19486547]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm
2 авг 16, 13:38    [19486746]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
enemyclosed
Member

Откуда:
Сообщений: 17
invm,
все действия выполнены ровно по статейке, результат тот же.
2 авг 16, 13:53    [19486836]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
enemyclosed
Member

Откуда:
Сообщений: 17
Пойду ставить 2008 скуль, на ней работало.
2 авг 16, 13:56    [19486860]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7768
enemyclosed,

ПКМ по базе - задачи - импорт.
2 авг 16, 14:26    [19487060]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
enemyclosed
Member

Откуда:
Сообщений: 17
Владислав, если бы это была разовая операция и табличку Excel не надо было обрабатывать, ну там убирать разного рода строки с промежуточными итогами, шапку документа, то да, импорт бы прокатил, но тут задача несколько иная и реализацией этой задачи будут пользоваться люди, которые в софтине кнопки будут нажимать.
2 авг 16, 14:31    [19487088]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4213
enemyclosed
Пойду ставить 2008 скуль, на ней работало.

Если есть возможность перевести базу в режим совместимости с 2008, то можете опробовать и так.
2 авг 16, 15:03    [19487343]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7768
enemyclosed,

в этом случае задачи экспорта должны решаться специальным софтом, например, тем же макросом на VBA. Это намного проще в сопровождении.
2 авг 16, 15:11    [19487398]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
enemyclosed
Member

Откуда:
Сообщений: 17
Владислав, ну какой ещё VBA? Оператор связи присылает детализацию, пользователь без лишних заморочек грузит её в софтину, нажатием одной кнопки, без разбора, что в файле прислали ибо процедура сама отработает и пропустит грязные данные. Куда я должен пихать макрос? В присланные детализации? Перестаньте чушь пороть, ей таки больно)))).
2 авг 16, 16:40    [19488118]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
enemyclosed
Member

Откуда:
Сообщений: 17
Переставил базу на 2008 MS SQL, все работает как и прежде. Жутко интересно на какие же грабли наступил. Если кто порешает сей вопрос буду рад услышать решение.
4 авг 16, 05:28    [19496128]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4213
enemyclosed, а разрядность одинакова у обоих серверов?
4 авг 16, 09:15    [19496342]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
enemyclosed
Member

Откуда:
Сообщений: 17
Да, одинаковая
4 авг 16, 09:34    [19496426]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4213
А вот ради интереса
замените

... Excel 12.0 xml; ...

на

... Excel 12.0; ...

(убрать xml)
4 авг 16, 09:35    [19496432]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
по моему опыту: только кастомное решение для импорта, т.к. готовые решения зачастую идиотски тупят на простых проблемах.
Источник данных может иметь (обычно имеет) кучу разных проблем: странные форматы дат, нестандартные разделители разрядов, суммы/даты/булеаны как текст, лишние пробелы, пропуски между строк, непонятное начало-конец данных и пр.
Т.е. "как есть" эксель-файл может быть просто неприменим для импорта и требовать серьезной подготовки ручкаме опытным пользователем.

Готовые решения применимы только для тупого переноса плоской таблицы в БД как есть. Дальнейшие обработки - отдельно.

Я имею настраиваемый модуль, кот. учитывает многие приведенные нюансы и вставляет данные в ХП, кот. умеет сразу их проверить и правильно вставить в кучу таблиц. Есть возможность тестового прогона, т.е. без физической вставки, но с проверками на сервере и с указанием проблемных строк.
Также есть возможность импортировать "или все или ничего", что обеспечивает целостность.

Да, это небыстро: обычно 20-30 строк/сек. Зато файл почти не требует коррекции.
4 авг 16, 09:40    [19496444]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
enemyclosed
Владислав, если бы это была разовая операция и табличку Excel не надо было обрабатывать, ну там убирать разного рода строки с промежуточными итогами, шапку документа, то да, импорт бы прокатил, но тут задача несколько иная и реализацией этой задачи будут пользоваться люди, которые в софтине кнопки будут нажимать.


Для этого используются SSIS-пакеты, можно импорт сохранить как пакет и потом вызывать
Если Вы не хотите использовать SSIS-пакеты - как минимум в сохраненном пакете можно посмотреть строку инициализации провайдера (если через пакет все сработает).
4 авг 16, 09:54    [19496532]     Ответить | Цитировать Сообщить модератору
 Re: И снова импорт из Excel  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
Мне показали хинт, который решает некоторые сложнообъяснимые проблемы с открытием файлов по сети:

нужно у драйвера в регистре прописать ключ DisallowAdHocAccess со значением 0.
Фишка в том, что это нельзя сделать через интерфейс - галка там ставит 1, а если галки нет, то нет и ключа.
Этого нельзя добиться и скриптом, потому что
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DisallowAdHocAccess' , 0

удаляеть ключ, так же, как и снятие галки, а если ключа нет, то выдаёт ошибку.
Т.е. нужно ключ добавить через интерфейс или скрипт, а потом ручками поменять на 0 в регистре.

П.С. У некоторых драйверов, SQLNCLI10 этот ключ стоит на 0 по умолчанию. Обычно всё работает и без него, но в некоторых случаях без него не работает, не зависимо от размеров админского бубна и продолжительности танцев. Поэтому мы включаем его в рамках установки
20 июн 17, 15:45    [20577884]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить