ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Импорт из Excel в таблику Ms Sql
25 сообщений из 27, страница 1 из 2
  1  все
Импорт из Excel в таблику Ms Sql
    #39906274
Фотография medoed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
medoed
Участник
Добрый день!
Понимаю, что тема избитая, но всё же.
Есть файлы Excel (формат xls, несколько страниц в книге) лежащие на сетевом, общедоступном диске (в рамках одного домена).
Файлы каждый день выкладываются новые (маска по дате). Их надо закачать в таблички базы SQL (2016;64 битный).
Вот варианты, которые приходят на ум:

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

Какой вариант используете Вы, какой метод является наиболее надёжным?
...
Рейтинг: 0 / 0
20.12.2019, 17:21
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39906311
Фотография medoed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
medoed
Участник
medoed,
5. Внешнее клиентское приложение (про это я и забыл).
...
Рейтинг: 0 / 0
20.12.2019, 18:11
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39906320
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
medoed
1) Использование функции OPENROWSET или с OPENDATASOURCE:
2) Второй способ через Linkedserver.
3) Пакет SSIS в Microsoft Visual Studio
4) Преобразование файла Excel в XML и работа уже с XML (ни разу так не пробовал).
Третий способ надёжный, и самый быстрый, и, вообще то, файл Excel и есть XML (точнее, архив, содержащий несколько XML), так что ничего преобразовывать не надо.
Но это непросто программировать, формат очень громоздкий.

Остальные варианты надёжны до драйверов доступа, с ними могут быть проблемы. При этом проще всё таки SSIS, т.к. достаточно 32 бит драйвера.
...
Рейтинг: 0 / 0
20.12.2019, 18:28
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39906322
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
medoed,

6. Сохранение как csv и bulk insert
...
Рейтинг: 0 / 0
20.12.2019, 18:36
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39906363
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Shakill
6. Сохранение как csv и bulk insert
Для этого же Эксель нужен.

И вообще, если вопрос был про "надёжность" метода, то речь о ETL, а запускать из кода кучу Экселей, для преобразования в csv, выглядит как то кустарно.
...
Рейтинг: 0 / 0
20.12.2019, 20:07
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39906376
Фотография Megabyte
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Megabyte
Участник
Мы через линкед-сервер работаем чаще всего. Минусы.
...
Рейтинг: 0 / 0
20.12.2019, 20:44
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39906416
L_argo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
L_argo
Участник
Когда-то делал такую вещь: в лоб файл не принимался (не помню почему). Делал к нему коннект из АКСЕСС, а из МССКЛ делал коннект к присоединенной таблице АКСЕСС. И это работало очень надежно. :)
...
Рейтинг: 0 / 0
21.12.2019, 00:09
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39906567
Фотография medoed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
medoed
Участник
alexeyvg
medoed
1) Использование функции OPENROWSET или с OPENDATASOURCE:
2) Второй способ через Linkedserver.
3) Пакет SSIS в Microsoft Visual Studio
4) Преобразование файла Excel в XML и работа уже с XML (ни разу так не пробовал).
Третий способ надёжный, и самый быстрый, и, вообще то, файл Excel и есть XML (точнее, архив, содержащий несколько XML), так что ничего преобразовывать не надо.
Но это непросто программировать, формат очень громоздкий.

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


Спасибо!
Если мы говорим про 3-ий способ. А то что источников несколько и имена файлов динамически могут менять для источника данных SSIS ничего? Я конечно больше с DTS пакетами работал, но мне казалось там на вход статичное имя файлов должно быть, разве нет?
...
Рейтинг: 0 / 0
21.12.2019, 21:36
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39906570
Фотография medoed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
medoed
Участник
Shakill
medoed,

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

У файла несколько страниц и вставка должны быть, примерно - каждая страница в свой лист.
Мне кажется csv в эту схему не укладывается!
...
Рейтинг: 0 / 0
21.12.2019, 21:38
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39906573
Фотография medoed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
medoed
Участник
Megabyte
Мы через линкед-сервер работаем чаще всего. Минусы.

Ну по сути это тот же 1-ый метод, вы регаете файла через драйвера , как внешний линкованный сервер и уже потом к нему обращаетесь. Ну и для линковки повышенные права нужны
...
Рейтинг: 0 / 0
21.12.2019, 21:42
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39906670
Фотография Megabyte
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Megabyte
Участник
medoed
Megabyte
Мы через линкед-сервер работаем чаще всего. Минусы.

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

Внутри проц execute as owner делаем, а самому логину права не даём.
...
Рейтинг: 0 / 0
22.12.2019, 13:05
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39906848
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
medoed,

Рекомендую использовать https://docs.microsoft.com/en-us/office/open-xml/open-xml-sdk . С самостоятельным парзингом xml намучаетесь, а там есть готовая объектная модель.
...
Рейтинг: 0 / 0
23.12.2019, 09:33
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39906868
Фотография medoed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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 всё сделать.
...
Рейтинг: 0 / 0
23.12.2019, 10:05
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39906905
Gerros
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gerros
Участник
medoed
А то что источников несколько и имена файлов динамически могут менять для источника данных SSIS ничего? Я конечно больше с DTS пакетами работал, но мне казалось там на вход статичное имя файлов должно быть, разве нет?
Сначала переименовываете (копируете) свой файл с произвольным названием в файл с фиксированным названием, которое прошито в пакете.
...
Рейтинг: 0 / 0
23.12.2019, 11:01
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39906936
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Gerros
medoed
А то что источников несколько и имена файлов динамически могут менять для источника данных SSIS ничего? Я конечно больше с DTS пакетами работал, но мне казалось там на вход статичное имя файлов должно быть, разве нет?
Сначала переименовываете (копируете) свой файл с произвольным названием в файл с фиксированным названием, которое прошито в пакете.
Или так, или можно менять имя файла для Excel-коннекта - сделать переменную пакета, ввести туда имя тестового файла для отладки, назначить её в Expression у Excel-коннекта на имя файла, потом в цикле пакета по файлам менять значение этой переменной.
Конечно, структуры всех файлов должны быть одинаковыми.
...
Рейтинг: 0 / 0
23.12.2019, 11:50
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39906942
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
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. Но даже это не оптимально. Напишите утилиту и запускайте её джобом.
...
Рейтинг: 0 / 0
23.12.2019, 12:15
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39906943
Alexander Us
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexander Us
Участник
medoed,

попробуйте бесплатную библиотеку EPPlus
...
Рейтинг: 0 / 0
23.12.2019, 12:18
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39906951
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
medoed,

мы признали наилучшим вариант загрузчика на С#.
...
Рейтинг: 0 / 0
23.12.2019, 12:37
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39906967
Alexander Us
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexander Us
Участник
Владислав Колосов
medoed,
мы признали наилучшим вариант загрузчика на С#.

а чем вас EPPlus не устроил?
...
Рейтинг: 0 / 0
23.12.2019, 13:06
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39907003
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Alexander Us,

не имею представления, я на C# не разрабатываю.
...
Рейтинг: 0 / 0
23.12.2019, 13:47
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39907043
Фотография medoed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
medoed
Участник
Владислав Колосов
medoed,

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

Видите ли, наверное внешняя утилита надежнее всего. Но если загрузок штук 10 разных, то получается куча утилиток, написанных не на t-SQL, но обсуживающих базу. Человек уволится, заболеет, эти удитки надо кому то поддерживать, опять же база перенесена, логин утилит поменяют (залочат) - всё это слетит.
SSIS пакет хотя бы наглядно, хранится на самом сервере, визуально можно всё поправить, не владея не особо никакими навыками.
Так что я всё же за 3-ий вариант. Ностальгирую по DTS - там вообще даже школьник мог разобраться и всё делалось встроенными средствами!
...
Рейтинг: 0 / 0
23.12.2019, 14:41
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39907082
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
medoed
Так что я всё же за 3-ий вариант. Ностальгирую по DTS - там вообще даже школьник мог разобраться и всё делалось встроенными средствами!
SSIS в общем похож на DTS, только вот средства разработки не встроенные, это минус. И вещи, чуть сложнее простого импорта из статического файлаЮ, делать сложнее.
Но в принципе, если писали DTS пакеты, то легко сможете написать и SSIS.
...
Рейтинг: 0 / 0
23.12.2019, 15:31
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39907124
Valery_B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valery_B
Участник
В зависимости от сложности, но чаще всего так:
1.
Код: sql
1.
2.
3.
4.
5.
DECLARE @Tmp TABLE
(
 ID Int,
 Name VARCHAR(20)
)


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


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


Но можно и через стандартный импорт во временную таблицу и потом уже работать с ней.
...
Рейтинг: 0 / 0
23.12.2019, 16:30
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39907139
Фотография medoed
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
medoed
Участник
Valery_B
В зависимости от сложности, но чаще всего так:
1.
Код: sql
1.
2.
3.
4.
5.
DECLARE @Tmp TABLE
(
 ID Int,
 Name VARCHAR(20)
)


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


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


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

Хм , а если 4-ре файла (каждый день новые, содержат дату в названии), каждый с 5-ю вкладками, на каждой вкладке может быть до 10 тысяч записей. И надо закачивать ежедневно по расписанию, во внерабочее время!
Ваш способ не применим, имхо...
...
Рейтинг: 0 / 0
23.12.2019, 16:45
| Ответить | Цитировать | Написать  
Импорт из Excel в таблику Ms Sql
    #39907170
AndrF
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AndrF
Участник
a_voronin
medoed,
С самостоятельным парзингом xml намучаетесь, а там есть готовая объектная модель.


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

Формат вывода для csv/txt файлов может задаваться так же файликом schema.ini. Тоже когда-то использовал...
...
Рейтинг: 0 / 0
23.12.2019, 17:20
| Ответить | Цитировать | Написать  
25 сообщений из 27, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Импорт из Excel в таблику Ms Sql
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=39906967&tid=1686729]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 30ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 63ms
get tp. blocked users:
 1ms
others: 14ms
total:  150ms
созд. / загр.: 150ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]