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

Откуда:
Сообщений: 902
Добрый день!
Понимаю, что тема избитая, но всё же.
Есть файлы Excel (формат xls, несколько страниц в книге) лежащие на сетевом, общедоступном диске (в рамках одного домена).
Файлы каждый день выкладываются новые (маска по дате). Их надо закачать в таблички базы SQL (2016;64 битный).
Вот варианты, которые приходят на ум:

1) Использование функции OPENROWSET или с OPENDATASOURCE:
2) Второй способ через Linkedserver.
3) Пакет SSIS в Microsoft Visual Studio
4) Преобразование файла Excel в XML и работа уже с XML (ни разу так не пробовал).

Какой вариант используете Вы, какой метод является наиболее надёжным?
20 дек 19, 17:21    [22045456]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
medoed
Member

Откуда:
Сообщений: 902
medoed,
5. Внешнее клиентское приложение (про это я и забыл).
20 дек 19, 18:11    [22045522]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30778
medoed
1) Использование функции OPENROWSET или с OPENDATASOURCE:
2) Второй способ через Linkedserver.
3) Пакет SSIS в Microsoft Visual Studio
4) Преобразование файла Excel в XML и работа уже с XML (ни разу так не пробовал).
Третий способ надёжный, и самый быстрый, и, вообще то, файл Excel и есть XML (точнее, архив, содержащий несколько XML), так что ничего преобразовывать не надо.
Но это непросто программировать, формат очень громоздкий.

Остальные варианты надёжны до драйверов доступа, с ними могут быть проблемы. При этом проще всё таки SSIS, т.к. достаточно 32 бит драйвера.
20 дек 19, 18:28    [22045542]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
Shakill
Member

Откуда: мск
Сообщений: 1870
medoed,

6. Сохранение как csv и bulk insert
20 дек 19, 18:36    [22045546]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30778
Shakill
6. Сохранение как csv и bulk insert
Для этого же Эксель нужен.

И вообще, если вопрос был про "надёжность" метода, то речь о ETL, а запускать из кода кучу Экселей, для преобразования в csv, выглядит как то кустарно.
20 дек 19, 20:07    [22045627]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4885
Мы через линкед-сервер работаем чаще всего. Минусы.
20 дек 19, 20:44    [22045652]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
L_argo
Member

Откуда:
Сообщений: 1137
Когда-то делал такую вещь: в лоб файл не принимался (не помню почему). Делал к нему коннект из АКСЕСС, а из МССКЛ делал коннект к присоединенной таблице АКСЕСС. И это работало очень надежно. :)
21 дек 19, 00:09    [22045738]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
medoed
Member

Откуда:
Сообщений: 902
alexeyvg
medoed
1) Использование функции OPENROWSET или с OPENDATASOURCE:
2) Второй способ через Linkedserver.
3) Пакет SSIS в Microsoft Visual Studio
4) Преобразование файла Excel в XML и работа уже с XML (ни разу так не пробовал).
Третий способ надёжный, и самый быстрый, и, вообще то, файл Excel и есть XML (точнее, архив, содержащий несколько XML), так что ничего преобразовывать не надо.
Но это непросто программировать, формат очень громоздкий.

Остальные варианты надёжны до драйверов доступа, с ними могут быть проблемы. При этом проще всё таки SSIS, т.к. достаточно 32 бит драйвера.


Спасибо!
Если мы говорим про 3-ий способ. А то что источников несколько и имена файлов динамически могут менять для источника данных SSIS ничего? Я конечно больше с DTS пакетами работал, но мне казалось там на вход статичное имя файлов должно быть, разве нет?
21 дек 19, 21:36    [22046051]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
medoed
Member

Откуда:
Сообщений: 902
Shakill
medoed,

6. Сохранение как csv и bulk insert

У файла несколько страниц и вставка должны быть, примерно - каждая страница в свой лист.
Мне кажется csv в эту схему не укладывается!
21 дек 19, 21:38    [22046054]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
medoed
Member

Откуда:
Сообщений: 902
Megabyte
Мы через линкед-сервер работаем чаще всего. Минусы.

Ну по сути это тот же 1-ый метод, вы регаете файла через драйвера , как внешний линкованный сервер и уже потом к нему обращаетесь. Ну и для линковки повышенные права нужны
21 дек 19, 21:42    [22046058]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4885
medoed
Megabyte
Мы через линкед-сервер работаем чаще всего. Минусы.

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

Внутри проц execute as owner делаем, а самому логину права не даём.
22 дек 19, 13:05    [22046278]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
a_voronin
Member

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

Рекомендую использовать https://docs.microsoft.com/en-us/office/open-xml/open-xml-sdk . С самостоятельным парзингом xml намучаетесь, а там есть готовая объектная модель.
23 дек 19, 09:33    [22046612]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
medoed
Member

Откуда:
Сообщений: 902
a_voronin
medoed,

Рекомендую использовать https://docs.microsoft.com/en-us/office/open-xml/open-xml-sdk . С самостоятельным парзингом xml намучаетесь, а там есть готовая объектная модель.

Спасибо за ваш ответ!
1. Я немного не понял, устанавливать Open XML SDK 2.5 for Microsoft Office - надо на сервер или на локальную станцию?
2. Посмотрел примеры, там на VB или C# - обработка кода.
Мне же бы хотелось средствами SQL всё сделать.
23 дек 19, 10:05    [22046642]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
Gerros
Member

Откуда: Харьков
Сообщений: 501
medoed
А то что источников несколько и имена файлов динамически могут менять для источника данных SSIS ничего? Я конечно больше с DTS пакетами работал, но мне казалось там на вход статичное имя файлов должно быть, разве нет?
Сначала переименовываете (копируете) свой файл с произвольным названием в файл с фиксированным названием, которое прошито в пакете.
23 дек 19, 11:01    [22046690]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30778
Gerros
medoed
А то что источников несколько и имена файлов динамически могут менять для источника данных SSIS ничего? Я конечно больше с DTS пакетами работал, но мне казалось там на вход статичное имя файлов должно быть, разве нет?
Сначала переименовываете (копируете) свой файл с произвольным названием в файл с фиксированным названием, которое прошито в пакете.
Или так, или можно менять имя файла для Excel-коннекта - сделать переменную пакета, ввести туда имя тестового файла для отладки, назначить её в Expression у Excel-коннекта на имя файла, потом в цикле пакета по файлам менять значение этой переменной.
Конечно, структуры всех файлов должны быть одинаковыми.

Сообщение было отредактировано: 23 дек 19, 11:51
23 дек 19, 11:50    [22046734]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4379
medoed
a_voronin
medoed,

Рекомендую использовать https://docs.microsoft.com/en-us/office/open-xml/open-xml-sdk . С самостоятельным парзингом xml намучаетесь, а там есть готовая объектная модель.

Спасибо за ваш ответ!
1. Я немного не понял, устанавливать Open XML SDK 2.5 for Microsoft Office - надо на сервер или на локальную станцию?
2. Посмотрел примеры, там на VB или C# - обработка кода.
Мне же бы хотелось средствами SQL всё сделать.


Это надо писать код на C#. Разбирать Excel на уровне SQL Server -- идея экстремально бредовая. Как минимум засуньте это в CLR. Но даже это не оптимально. Напишите утилиту и запускайте её джобом.
23 дек 19, 12:15    [22046748]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
Alexander Us
Member

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

попробуйте бесплатную библиотеку EPPlus
23 дек 19, 12:18    [22046749]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
Владислав Колосов
Member

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

мы признали наилучшим вариант загрузчика на С#.
23 дек 19, 12:37    [22046759]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
Alexander Us
Member

Откуда:
Сообщений: 1093
Владислав Колосов
medoed,
мы признали наилучшим вариант загрузчика на С#.

а чем вас EPPlus не устроил?
23 дек 19, 13:06    [22046781]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
Alexander Us,

не имею представления, я на C# не разрабатываю.
23 дек 19, 13:47    [22046834]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
medoed
Member

Откуда:
Сообщений: 902
Владислав Колосов
medoed,

мы признали наилучшим вариант загрузчика на С#.

Видите ли, наверное внешняя утилита надежнее всего. Но если загрузок штук 10 разных, то получается куча утилиток, написанных не на t-SQL, но обсуживающих базу. Человек уволится, заболеет, эти удитки надо кому то поддерживать, опять же база перенесена, логин утилит поменяют (залочат) - всё это слетит.
SSIS пакет хотя бы наглядно, хранится на самом сервере, визуально можно всё поправить, не владея не особо никакими навыками.
Так что я всё же за 3-ий вариант. Ностальгирую по DTS - там вообще даже школьник мог разобраться и всё делалось встроенными средствами!
23 дек 19, 14:41    [22046903]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30778
medoed
Так что я всё же за 3-ий вариант. Ностальгирую по DTS - там вообще даже школьник мог разобраться и всё делалось встроенными средствами!
SSIS в общем похож на DTS, только вот средства разработки не встроенные, это минус. И вещи, чуть сложнее простого импорта из статического файлаЮ, делать сложнее.
Но в принципе, если писали DTS пакеты, то легко сможете написать и SSIS.
23 дек 19, 15:31    [22046986]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
Valery_B
Member

Откуда: Москва
Сообщений: 2014
В зависимости от сложности, но чаще всего так:
1.
DECLARE @Tmp TABLE
(
 ID Int,
 Name VARCHAR(20)
)

2. Потом в левой колонке Экселя пишу такой текст
INSERT INTO @Tmp (ID,NAME) VALUES ( 

3. Копипаста из Экселя в Нотепад++, немного замен по CTRL+H и финальная копипаста в студию + F5


Но можно и через стандартный импорт во временную таблицу и потом уже работать с ней.
23 дек 19, 16:30    [22047067]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
medoed
Member

Откуда:
Сообщений: 902
Valery_B
В зависимости от сложности, но чаще всего так:
1.
DECLARE @Tmp TABLE
(
 ID Int,
 Name VARCHAR(20)
)

2. Потом в левой колонке Экселя пишу такой текст
INSERT INTO @Tmp (ID,NAME) VALUES ( 

3. Копипаста из Экселя в Нотепад++, немного замен по CTRL+H и финальная копипаста в студию + F5


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

Хм , а если 4-ре файла (каждый день новые, содержат дату в названии), каждый с 5-ю вкладками, на каждой вкладке может быть до 10 тысяч записей. И надо закачивать ежедневно по расписанию, во внерабочее время!
Ваш способ не применим, имхо...
23 дек 19, 16:45    [22047095]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в таблику Ms Sql  [new]
AndrF
Member

Откуда:
Сообщений: 2183
a_voronin
medoed,
С самостоятельным парзингом xml намучаетесь, а там есть готовая объектная модель.


Если экспортировать/импортировать из клиента, то объектная модель есть в OLEDB. Через ADOX.Catalog создается Excel-евский файл со листом в нужном формате. Потом как обычно через ADODB.Connection открываем этот лист и кидаем туда данные. Все это работает без установленного на компе Excel-я. Делал подобное еще на VBA/VB6. В NET-e, естественно, должен быть соответствующий аналог.

Формат вывода для csv/txt файлов может задаваться так же файликом schema.ini. Тоже когда-то использовал...
23 дек 19, 17:20    [22047146]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить