IT-инфраструктура для бизнеса и творчества
Разработка
NTA

Как сформировать SQL запросы в Excel?

Представьте себе ситуацию, Вы получили целевую выборку из одной базы данных, но для полноты картины, как всегда, нужны дополнительные данные. Проблема может быть в том, что нужная информация хранится в другой базе данных и возможности создать на ней свою таблицу нет, подключиться используя link тоже нельзя, да и количество элементов, по которым нужно получить данные, несколько больше, чем допустимое на данном источнике. Вот и получается, что возможность написать SQL запрос и получить нужные данные есть, но написать придется не один запрос, а потом потратить время на объединение полученных данных.

Выйти из подобной ситуации поможет Excel.

Уверен, что ни для кого не секрет, что MS Excel имеет встроенный модуль VBA и надстройки, позволяющие подключаться к внешним источникам данных, то есть по сути является мощным инструментом для аналитики, а значит идеально подходит для решения подобных задач.

Для того чтобы обойти проблему, нам потребуется таблица с целевой выборкой, в которой содержатся идентификаторы, по которым можно достаточно корректно получить недостающую информацию (это может быть уникальный идентификатор, назовем его ID, или набор из данных, находящихся в разных столбцах), ПК с установленным MS Excel, и доступом к БД с недостающей информацией и, конечно, желание получить ту самую информацию.

Создаем в MS Excel книгу, на листе которой размещаем таблицу с идентификаторами, по которым будем в дальнейшем формировать запрос (если у нас есть уникальный идентификатор, для обеспечения максимальной скорости обработки таблицу лучше представить в виде одного столбца), сохраняем книгу в формате *.xlsm, после чего приступаем к созданию макроса.

Через меню «Разработчик» открываем встроенный VBA редактор и начинаем творить.

Sub job_sql() — Пусть наш макрос называется job_sql.

Пропишем переменные для подключения к БД, записи данных и запроса:

Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim sql As String

Опишем параметры подключения:

sql = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=Storoge.company.ru\ Storoge."

Объявим процедуру свойства, для присвоения значения:

Set cn = New ADODB.Connection cn.Provider = " SQLOLEDB.1" cn.ConnectionString = sql cn.ConnectionTimeout = 0 cn.Open

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

Организуем цикл:

For i = 2 To 1000

Как вы уже поняли конечное значение i=1000 здесь только для примера, а в реальности конечное значение соответствует количеству строк в Вашей таблице. В целях унификации можно использовать автоматический способ подсчета количества строк, например, вот такую конструкцию:

Dim LastRow As Long LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

Тогда открытие цикла будет выглядеть так:

For i = 2 To LastRow

Как я уже говорил выше MS Excel является мощным инструментом для аналитики, и возможности Excel VBA не заканчиваются на простом переборе значений или комбинаций значений. При наличии известных Вам закономерностей можно ограничить объем выгружаемой из БД информации путем добавления в макрос простых условий, например:

If Cells(i, 2) = "Ваше условие" Then

Итак, мы определились с объемом и условиями выборки, организовали подключение к БД и готовы формировать запрос. Предположим, что нам нужно получить информацию о размере ежемесячного платежа [Ежемесячный платеж] из таблицы [payments].[refinans_credit], но только по тем случаям, когда размер ежемесячного платежа больше 0

sql = "select [Ежемесячный платеж] from [PAYMENTS].[refinans_credit] " & _ "where [Ежемесячный платеж]>0 and [Номер заявки] ='" & Cells(i, 1) & "' "

Если значений для формирования запроса несколько, соответственно прописываем их в запросе:

"where [Ежемесячный платеж]>0 and [Номер заявки] = '" & Cells(i, 1) & "' " & _ " and [Дата платежа]='" & Cells(i, 2) & "'"

В целях самоконтроля я обычно записываю сформированный макросом запрос, чтобы иметь возможность проверить его корректность и работоспособность, для этого добавим вот такую строчку:

Cells(i, 3) = sql

в третьем столбце записываются запросы.

Выполняем SQL запрос:

Set rs = cn.Execute(sql)

А чтобы хоть как-то наблюдать за выполнением макроса выведем изменение i в статус-бар

Application.StatusBar = "Execute script ..." & i Application.ScreenUpdating = False

Теперь нам нужно записать полученные результаты. Для этого будем использовать оператор Do While:

j = 0 Do While Not rs.EOF For ii = 0 To rs.Fields.Count - 1 Cells(i, 4 + j + ii) = rs.Fields(0 + ii) '& ";"

Указываем ячейки для вставки полученных данных (4 в примере это номер столбца с которого начинаем запись результатов)

Next ii j = j + rs.Fields.Count s.MoveNext Loop rs.Close End If

— закрываем цикл If, если вводили дополнительные условия

Next i cn.Close Application.StatusBar = "Готово" End Sub

— закрываем макрос.

В дополнение хочу отметить, что данный макрос позволяет обращаться как к БД на MS SQL так и к БД Oracle, разница будет только в параметрах подключения и собственно в синтаксисе SQL запроса.

В приведенном примере для авторизации при подключении к БД используется доменная аутентификация.

А как быть если для аутентификации необходимо ввести логин и пароль? Ничего невозможного нет. Изменим часть макроса, которая отвечает за подключение к БД следующим образом:

sql = "Provider= SQLOLEDB.1;Password=********;User ID=********;Data Source= Storoge.company.ru\ Storoge;APP=SFM"

Но в этом случае при использовании макроса возникает риск компрометации Ваших учетных данных. Поэтому лучше программно удалять учетные данные после выполнения макроса. Разместим поля для ввода пароля и логина на листе и изменим макрос следующим образом:

sql = "Provider= SQLOLEDB.1;Password=" & Sheets("Лист аутентификации").TextBox1.Value & ";User ID=" & Sheets("Лист аутентификации ").TextBox2.Value & ";Data Source= Storoge.company.ru\ Storoge;APP=SFM"

Место для расположения текстовых полей не принципиально, можно расположить их на листе с таблицей в первых строках, но мне удобней размещать поля на отдельном листе. Чтобы введенные учетные данные не сохранялись вместе с результатом выполнения макроса в конце исполняемого кода дописываем:

Sheets(«Выгрузка»).TextBox1.Value = «« Sheets(»Выгрузка«).TextBox2.Value = »»

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

Вот такое вполне жизнеспособное решение, позволяющее сократить трудозатраты при получении и обработке данных, я использую. Надеюсь мой опыт применения SQL запросов в Excel будет полезен и вам в решении текущих задач.

{ "author_name": "NTA", "author_type": "editor", "tags": ["selectel_\u0438\u043d\u0441\u0442\u0440\u0443\u043a\u0446\u0438\u044f"], "comments": 4, "likes": 0, "favorites": 16, "is_advertisement": false, "subsite_label": "dev", "id": 158807, "is_wide": true, "is_ugc": false, "date": "Wed, 16 Sep 2020 21:16:38 +0300", "is_special": false }
(function () { let cdnUrl = `https://specialsf378ef5-a.akamaihd.net/SelectelBranding/images/` let previousArticleNumber = null let currentArticleNumber = 0 let platform = 'Desktop' let articles = [ { name: 'camera', url: `${cdnUrl}CameraCat`, text: 'умную камеру для\u00A0наблюдения за\u00A0котиками', link: 'https://vc.ru/selectel/306690', num: 3 }, { name: 'chill', url: `${cdnUrl}ChillCat`, text: 'трекер, который подскажет, когда пора отдохнуть', link: 'https://vc.ru/promo/288561-eye-tracker', num: 1 }, { name: 'cloud', url: `${cdnUrl}CloudCat`, text: 'котика: даёшь ему «пять», а\u00A0он делает бэкап в облако', link: 'https://vc.ru/dev/294799-maneki-neko', num: 2 } ] let buttonCycle = document.querySelector('.button--cycle') let buttonChoose = document.querySelector('.button--choose') let buttonMobile = document.querySelector('.button--mobile') let textField = document.querySelector('.selectel-footer-subtitle') let imageAgent = document.querySelector('.image--agent') let banner = document.querySelector('.selectel-footer') buttonCycle.addEventListener('click', cycleClick) buttonChoose.addEventListener('click', () => sendEvent(`Promo ${articles[currentArticleNumber].num} Left`, 'Click')) buttonMobile.addEventListener('click', () => sendEvent(`Promo ${articles[currentArticleNumber].num} Left`, 'Click')) let media = window.matchMedia("(max-width: 570px)") media.addEventListener('change', matchMedia) function matchMedia() { if (media.matches) { platform = 'Mobile' } else { platform = 'Desktop' } update() } matchMedia() function cycleClick(event) { sendEvent(`Promo ${articles[currentArticleNumber].num} Right`, 'Click') if (event) { event.preventDefault() event.stopPropagation() } window.open('https://vc.ru/tag/selectelDIY', '_blank') //cycle(event) } function cycle(event) { // incrementArticleNumber() textField.innerHTML = generatedText() imageAgent.src = articles[currentArticleNumber].url + platform + '.svg?3' imageAgent.setAttribute("class", "") imageAgent.classList.add('image--agent', articles[currentArticleNumber].name) banner.href = articles[currentArticleNumber].link } function update() { banner.href = articles[currentArticleNumber].link imageAgent.src = articles[currentArticleNumber].url + platform + '.svg' textField.innerHTML = generatedText() } function incrementArticleNumber() { previousArticleNumber = currentArticleNumber if (currentArticleNumber >= articles.length - 1) { currentArticleNumber = 0 } else { currentArticleNumber++ } } const sendEvent = (label, action = 'Click') => { const value = `SelectelDIY — loc: Footer — ${label} — ${action}`; if (window.dataLayer !== undefined) { window.dataLayer.push({ event: 'data_event', data_description: value, }); } }; function generatedText() { let defaultText if (platform === 'Desktop') { defaultText = `Мы тут собрали %text%. Хотите научим?` } else { defaultText = `Мы тут собрали %text%.` } return defaultText.replace('%text%', articles[currentArticleNumber].text) } function getRandom(min, max) { min = Math.ceil(min) max = Math.floor(max) return Math.floor(Math.random() * (max - min + 1)) + min } (function create() { currentArticleNumber = getRandom(0, articles.length - 1) cycle() let page = document.querySelector('.page--entry') if (page) { function insertAfter() { let parents = page.querySelectorAll('[data-id="7"]') let referenceNode = parents[0] referenceNode.parentNode.insertBefore(banner, referenceNode.nextSibling); loaded() } setTimeout(() => insertAfter(), 0) } }()) function loaded() { banner.classList.add('loaded') } loadImages([ `${cdnUrl}CameraCatDesktop.svg`, `${cdnUrl}ChillCatDesktop.svg`, `${cdnUrl}CloudCatDesktop.svg`, `${cdnUrl}CameraCatMobile.svg`, `${cdnUrl}ChillCatMobile.svg`, `${cdnUrl}CloudCatMobile.svg?3`, ]) function loadImages(urls) { return Promise.all(urls.map(function (url) { return new Promise(function (resolve) { var img = document.createElement('img'); img.onload = resolve; img.onerror = resolve; img.src = url; }); })); } }())
0
4 комментария
Популярные
По порядку

Дополню: в конце статьи по ссылке ниже - несколько макросов, которые позволяют "переводить" запрос sql в переменную VBA и обратно

https://ytikhonov.wordpress.com/2015/10/12/excel-vba-sql/ ред.

2
Музыкальный парфюмер

Это 2000 год? Зачем макросы , если Давно уже есть ms query , который превратился в powerquery. С 2007 excel все это делается без кода

1

Зачем вообще подключаться к удалённому экселю и пытаться его использовать как БД, используя для этого SQL? Я, может быть, выскажу непопулярное мнение, но имея данные в excel таблице куда проще их слить в БД и работать нативными инструментами аналитики, нежели городить костыли с макросами. Времени опять же меньше уйдет. 

0

Использование PQ не освобождает от использования SQL, равно как в некоторых случаях связка VBA + SQL является оптимальным решением.

Знаю одну компанию, где сделали простенькую CRM на базе Excel с этой связкой. На PQ такое не провернуть - им нужно редактирование и занесение записей в БД через привычный интерфейс

2
Читать все 4 комментария
Бизнес-филантропия и благотворительность. Почему это не затраты, а вложения? Рассказываем на примере CloudPayments

Социальная ответственность составляет важную часть ДНК компании CloudPayments. Делимся инсайтами, почему мы поддерживаем НКО, создаем благотворительные проекты и как транслируем принципы филантропии внутри команды.

«В магазин должны идти не за пивом, а к тебе»: предприниматель открывает пивные магазины для других Статьи редакции

Давид Аракелян начал с торговли в ларьках в 90-е, работал в игорном бизнесе, строил маршруты для вывоза мусора и торговал пивом. Сейчас он открывает для других несколько пивных магазинов в месяц и берёт за каждый 150 тысяч рублей.

Ассортимент одного из магазинов
Участвуйте в IPO Softline Group!

С 18 октября открыта книга заявок на участие в IPO глобальной IT-корпорации Softline Group.

СберМегаМаркет отказывается публиковать отзыв на товар с браком

Приобрели товар на СберМегаМаркете не так давно.
Код товара: 600001207021

Рыночная капитализация хардфорка Chia превысила 1,9 млн. $

На текущий момент монета является самой прибыльной, по расчетам калькулятора, доход с 1,01 Tib в более чем в 2 раза выше дохода Chia. Что делает Chives привлекательным для майнеров Chia, которые активно подключаются к проекту с целью вернуть свои вложения.

Онлайн-кинотеатр IVI начал съёмки самого правдивого сериала про современный российский наркобизнес
Стоимость акций социальной сети Дональда Трампа выросла на 275% за сутки

Бывший президент США Дональд Трамп анонсировал запуск собственной социальной сети Truth Social и сразу запустил её на биржу, где она показывает высокий рост.

«Майним цельный белок из воды и света» — Как BiOVOLF делает технологии для сельского хозяйства будущего
Горизонтальный биореактор BIOVOLF 4
Цифровая трансформация банков: что это такое?
Автор: Ксения Борбачева
заместитель генерального лиректора Агентства инноваций Москвы
НЕ КЛИЕНТООРИЕНТИРОВАННОСТЬ И БЮРОКРАТИЯ СЕРВИСА ELAMA. УЖЕ МЕСЯЦ ПЫТАЕМСЯ ВЕРНУТЬ СВОИ 530 000 РУБЛЕЙ
С книжной полки на рынок мобильных приложений. История Laoshi.io

Продолжение истории компании Лаоши. С 2011 года мы занимаемся преподаванием китайского языка. За это время делали сеть школ китайского языка и онлайн-школу, создавали платформу для изучения китайского онлайн, издавали учебник и прописи. Сейчас разрабатываем мобильное приложение для англоязычного рынка.

Приложение для изучение китайских слов и иероглифов Laoshi.io
null