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

Откуда: Москва
Сообщений: 564
Добрый день! Нужна помощь профессионалов. Думаю данный пример будет полезен и не только мне. Делаю инструмент, который позволит пользователям вносить корректировки в таблицы MSSQL, через Excel

Ситуация следующая:
1) есть в MSSQL таблица [КЛИЕНТЫ] - c полями (кодТТ, название_клиента, адрес ... и т.д).
2) есть файл Excel - в котором находится именованный диапазон [Корректировки] - c полями (кодТТ, новый_адрес, ... и т.д)

Необходимо обновить данные в таблице [КЛИЕНТЫ] (mssql) т.е заменить старые значение на новые из диапазона [Корректировки] (excel).


теперь собственно сам код, как я начал делать.
    Dim cnn As New ADODB.Connection
    Dim cnnex As New ADODB.Connection

    Dim rst As New ADODB.Recordset
    Dim rstexAs New ADODB.Recordset

   'создаю подключение к активной книге excel
    With cnnex
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Mode = adModeReadWrite
        .ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";" & _
        "Extended Properties=Excel 8.0"
        .Open
    End With

  'создаю подключение к mssql
    cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OLAP_DWH_DEV;Data Source=wruistb008"
    cnn.Open

    'запрос к таблице excel
    sqltekst_ex = "OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=" & ActiveWorkbook.FullName & "', 'SELECT кодТТ, новый_адрес FROM [Корректировки]'")

    sqltekst ="UPDATE [КЛИЕНТЫ] LEFT JOIN " &  sqltekst_ex  &" ON [Корректировки].КодТТ=[КЛИЕНТЫ].КодТТ SET [КЛИЕНТЫ].Адес=[Корректировки].Новый_адрес " & _
"WHERE  [Корректировки].КодТТ Is Not Null"

    cnn.Execute sqltekst 
    cnn.close
    cnnex.close

    Set rst = Nothing
    Set rstex = Nothing


Выскакивает следующая ошибка. Где я не правильно делаю. Может быть есть другой способ решения задачи?

К сообщению приложен файл. Размер - 26Kb
16 дек 14, 10:35    [17002926]     Ответить | Цитировать Сообщить модератору
 Re: Обновление данных в MSSQL из Excel  [new]
Glory
Member

Откуда:
Сообщений: 104751
msleg
Где я не правильно делаю.

Непонятно, что вы хотите сделать
- то ли сервер должен сам подключиться к вашему локальному Excel через OPENROWSET
- то ли вы сами хотите подключиться к этому же Excel через 'создаю подключение к активной книге excel

А ошибка скорее всего связана с тем, что сервер у вас 64битный, а провайдер Microsoft.Jet.OLEDB.4.0 - 32битный
16 дек 14, 10:57    [17003030]     Ответить | Цитировать Сообщить модератору
 Re: Обновление данных в MSSQL из Excel  [new]
msleg
Member

Откуда: Москва
Сообщений: 564
Glory,
хочу написать макрос в книге Excel, т.е пользователь вносит корректировки в excel по ТТ. Запускает макрос и он обновляет значения в таблице MSSQL.

Я вот тоже стал задумываться насчет битности. У меня Excel 32-бит. MSSQL 64-бит
16 дек 14, 11:11    [17003097]     Ответить | Цитировать Сообщить модератору
 Re: Обновление данных в MSSQL из Excel  [new]
msleg
Member

Откуда: Москва
Сообщений: 564
Нашел интересную статью по теме - http://dbasimple.blogspot.ru/2012/02/excel-ms-sql-server.html
16 дек 14, 11:14    [17003114]     Ответить | Цитировать Сообщить модератору
 Re: Обновление данных в MSSQL из Excel  [new]
Glory
Member

Откуда:
Сообщений: 104751
msleg
хочу написать макрос в книге Excel, т.е пользователь вносит корректировки в excel по ТТ. Запускает макрос и он обновляет значения в таблице MSSQL.

Вопрос был не про то, что вы хотите сделать, а как вы хотите сделать
вариант 1
вы запускаете запрос на сервере, который делает все

вариант 2
вы открываете Excel в своем коде, читаете его построчно и для каждой строки формируете текст запроса, который передаете серверу
16 дек 14, 11:21    [17003159]     Ответить | Цитировать Сообщить модератору
 Re: Обновление данных в MSSQL из Excel  [new]
msleg
Member

Откуда: Москва
Сообщений: 564
Скорее всего 2 вариант (макрос запускается из активной книги excel). Данная команда (cnn.Execute sqltekst ), если я не ошибаюсь, как раз и передают на сервер запрос для выполнения.

Обычно я через recordset построчно передавал данные в БД. Но в данной задачи, кол-во строк достигает несколько тысяч. И построчно очень долго ждать. Поэтому сейчас хочу попробовать сразу массивом грузить
16 дек 14, 11:38    [17003295]     Ответить | Цитировать Сообщить модератору
 Re: Обновление данных в MSSQL из Excel  [new]
Glory
Member

Откуда:
Сообщений: 104751
msleg
Данная команда (cnn.Execute sqltekst ), если я не ошибаюсь, как раз и передают на сервер запрос для выполнения.

Да передает. Только это не запрос варианта 2. Это запрос варианта 1
16 дек 14, 11:41    [17003324]     Ответить | Цитировать Сообщить модератору
 Re: Обновление данных в MSSQL из Excel  [new]
msleg
Member

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

у меня вопрос если устанавливать Microsoft.ACE.OLEDB.12.0., то мне какой версии ставить 32 или 64? Office-32 SQL-64.
Ссылка для скачивания - http://www.microsoft.com/en-us/download/details.aspx?id=13255
16 дек 14, 11:45    [17003351]     Ответить | Цитировать Сообщить модератору
 Re: Обновление данных в MSSQL из Excel  [new]
Glory
Member

Откуда:
Сообщений: 104751
msleg
то мне какой версии ставить 32 или 64?

Вы что серьезно думате, что продукт одной разрадности может работать с продуктом другой разрадности ?
16 дек 14, 11:52    [17003425]     Ответить | Цитировать Сообщить модератору
 Re: Обновление данных в MSSQL из Excel  [new]
msleg
Member

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

Ну например через Recordset - получается передавать данные из 32 в 64. Может быть и с помощью этого драйвера все заработает. В теории мне интересно OPENROWSET - это сервер подключается к данными в excel, т.е получается драйвер должен быть 64 бит, как и MSSQL?

Какие варианты тогда решения задачи? Переустанавливать office на 64 не вариант. Очень много компов. MSSQL даже не рассматриваю
16 дек 14, 12:02    [17003491]     Ответить | Цитировать Сообщить модератору
 Re: Обновление данных в MSSQL из Excel  [new]
Glory
Member

Откуда:
Сообщений: 104751
msleg
Ну например через Recordset - получается передавать данные из 32 в 64.

Зачем тогда устанавливать какой-то драйвер на сервере ?

msleg
Может быть и с помощью этого драйвера все заработает. В теории мне интересно OPENROWSET - это сервер подключается к данными в excel,

Невозможно подключиться к уже открытому кем-то файлу.
Подключение к удаленному Excel тоже не тривиальная задача.

msleg
Какие варианты тогда решения задачи?

Вы точно читаете ответы ? Все ? До конца ?
16 дек 14, 12:06    [17003511]     Ответить | Цитировать Сообщить модератору
 Re: Обновление данных в MSSQL из Excel  [new]
StarikNavy
Member

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

Эксель - "данные" - "из других источников" - "с сервера SQL сервер" - настраиваете подключение и получаете свою таблицу для редактирования. Правда кнопочки не будет
16 дек 14, 13:57    [17004274]     Ответить | Цитировать Сообщить модератору
 Re: Обновление данных в MSSQL из Excel  [new]
msleg
Member

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

а как там настроить, чтобы изменения в excel вносились в БД
16 дек 14, 14:46    [17004594]     Ответить | Цитировать Сообщить модератору
 Re: Обновление данных в MSSQL из Excel  [new]
sposad
Member

Откуда:
Сообщений: 62187
Блин,
1. Пишете процедуру с параметрами на сервере, которая, собственно и будет заниматься изменением данных.
2. Описываете в модуле excel объекты Connection и Command применительно к процедуре п.1
3. Бежите по ключам в полях на листе Excel и запускаете процедуру п.1 с соответствующими параметрами.
17 дек 14, 10:52    [17009106]     Ответить | Цитировать Сообщить модератору
 Re: Обновление данных в MSSQL из Excel  [new]
msleg
Member

Откуда: Москва
Сообщений: 564
sposad,
База MSsql и файл excel располагаются на разных компьютерах. Как удаленно можно запускать процедуры MSSQL? Можешь привести примеры кода для данных пунктов?
2. Описываете в модуле excel объекты Connection и Command применительно к процедуре п.1
3. Бежите по ключам в полях на листе Excel и запускаете процедуру п.1 с соответствующими параметрами.
17 дек 14, 11:49    [17009630]     Ответить | Цитировать Сообщить модератору
 Re: Обновление данных в MSSQL из Excel  [new]
Glory
Member

Откуда:
Сообщений: 104751
msleg
Как удаленно можно запускать процедуры MSSQL?

а это что в вашем коде
  'создаю подключение к mssql
    cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OLAP_DWH_DEV;Data Source=wruistb008"
    cnn.Open

    'запрос к таблице excel
    sqltekst_ex = "OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=" & ActiveWorkbook.FullName & "', 'SELECT кодТТ, новый_адрес FROM [Корректировки]'")

    sqltekst ="UPDATE [КЛИЕНТЫ] LEFT JOIN " &  sqltekst_ex  &" ON [Корректировки].КодТТ=[КЛИЕНТЫ].КодТТ SET [КЛИЕНТЫ].Адес=[Корректировки].Новый_адрес " & _
"WHERE  [Корректировки].КодТТ Is Not Null"

    cnn.Execute sqltekst 
    cnn.close
    cnnex.close


msleg
Можешь привести примеры кода для данных пунктов?

Это в форум по оффису/бейсику
17 дек 14, 11:52    [17009643]     Ответить | Цитировать Сообщить модератору
 Re: Обновление данных в MSSQL из Excel  [new]
sposad
Member

Откуда:
Сообщений: 62187
msleg
sposad,
База MSsql и файл excel располагаются на разных компьютерах. Как удаленно можно запускать процедуры MSSQL? Можешь привести примеры кода для данных пунктов?
2. Описываете в модуле excel объекты Connection и Command применительно к процедуре п.1
3. Бежите по ключам в полях на листе Excel и запускаете процедуру п.1 с соответствующими параметрами.

да я и не предполагал, что они на одном компе:)
Dim con As New ADODB.Connection
con.ConnectionString = "строка подключения"
Dim cmd As New ADODB.Command
con.Open
cmd.ActiveConnection = con 
cmd.CommandText = "имя процедуры"
cmd.CommandType = adCmdStoredProc

Dim p0 As New ADODB.Parameter
Set p0 = cmd.CreateParameter("@p0", adInteger, adParamInput, 20, значение)
cmd.Parameters.Append p0
  
Dim p1 As New ADODB.Parameter
Set p1 = cmd.CreateParameter("@p1", adInteger, adParamInput, 20, значение)
cmd.Parameters.Append p1

cmd.Execute


как-то так приблизительно
17 дек 14, 12:05    [17009735]     Ответить | Цитировать Сообщить модератору
 Re: Обновление данных в MSSQL из Excel  [new]
msleg
Member

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

Спасибо. Нашел инфу по данной теме - http://www.askit.ru/custom/vba_office/m9/09_06_ado_command.htm
17 дек 14, 12:59    [17010177]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить