Excel выбор из базы данных
Представьте себе ситуацию, Вы получили целевую выборку из одной базы данных, но для полноты картины, как всегда, нужны дополнительные данные. Проблема может быть в том, что нужная информация хранится в другой базе данных и возможности создать на ней свою таблицу нет, подключиться используя link тоже нельзя, да и количество элементов, по которым нужно получить данные, несколько больше, чем допустимое на данном источнике. Вот и получается, что возможность написать SQL запрос и получить нужные данные есть, но написать придется не один запрос, а потом потратить время на объединение полученных данных.
Выйти из подобной ситуации поможет Excel.
Уверен, что ни для кого не секрет, что MS Excel имеет встроенный модуль VBA и надстройки, позволяющие подключаться к внешним источникам данных, то есть по сути является мощным инструментом для аналитики, а значит идеально подходит для решения подобных задач.
Для того чтобы обойти проблему, нам потребуется таблица с целевой выборкой, в которой содержатся идентификаторы, по которым можно достаточно корректно получить недостающую информацию (это может быть уникальный идентификатор, назовем его ID, или набор из данных, находящихся в разных столбцах), ПК с установленным MS Excel, и доступом к БД с недостающей информацией и, конечно, желание получить ту самую информацию.
Создаем в MS Excel книгу, на листе которой размещаем таблицу с идентификаторами, по которым будем в дальнейшем формировать запрос (если у нас есть уникальный идентификатор, для обеспечения максимальной скорости обработки таблицу лучше представить в виде одного столбца), сохраняем книгу в формате *.xlsm, после чего приступаем к созданию макроса.
Через меню «Разработчик» открываем встроенный VBA редактор и начинаем творить.
Sub job_sql() — Пусть наш макрос называется job_sql.
Пропишем переменные для подключения к БД, записи данных и запроса:
Опишем параметры подключения:
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
Вот теперь можно приступать непосредственно к делу.
Как вы уже поняли конечное значение i=1000 здесь только для примера, а в реальности конечное значение соответствует количеству строк в Вашей таблице. В целях унификации можно использовать автоматический способ подсчета количества строк, например, вот такую конструкцию:
Тогда открытие цикла будет выглядеть так:
Как я уже говорил выше MS Excel является мощным инструментом для аналитики, и возможности Excel VBA не заканчиваются на простом переборе значений или комбинаций значений. При наличии известных Вам закономерностей можно ограничить объем выгружаемой из БД информации путем добавления в макрос простых условий, например:
Итак, мы определились с объемом и условиями выборки, организовали подключение к БД и готовы формировать запрос. Предположим, что нам нужно получить информацию о размере ежемесячного платежа [Ежемесячный платеж] из таблицы [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) & "'"
В целях самоконтроля я обычно записываю сформированный макросом запрос, чтобы иметь возможность проверить его корректность и работоспособность, для этого добавим вот такую строчку:
Функция БИЗВЛЕЧЬ в Excel для удобства поиска информации в больших массивах данных. Она предназначена для поиска данных в таблице или базе данных на основе критериев, указанных в качестве ее параметров, и возвращает искомое значение, если оно было найдено, или код ошибки, если совпадения найдены не были или с учетом введенных критериев поиска были найдены 2 и более совпадений.
Пример работы функции БИЗВЛЕЧЬ при выборке данных из таблицы Excel
Как создать базу данных в Excel? Базой данных в программе Excel считается таблица, которая была создана с учетом определенных требований:
- Заголовки таблицы должны находиться в первой строке.
- Любая последующая строка должна содержать хотя бы одну непустую ячейку.
- Объединения ячеек в любых строках запрещены.
- Для каждой ячейки каждого столбца должен быть определен единый тип хранящихся данных.
- Диапазон базы данных должен быть отформатирован в качестве списка и иметь свое имя.
Таким образом, практически любая таблица в Excel может быть преобразована в базу данных. Ее строки являются записями, а столбцы – полями данных.
Функция БИЗВЛЕЧЬ хорошо работает с корректно отформатированными таблицами.
Примеры использования функции БИЗВЛЕЧЬ в Excel
Пример 1. В таблице, которую можно рассматривать как БД, содержатся данные о различных моделях смартфонов. Найти название бренда смартфона, который содержит процессор с минимальным числом ядер.
Вид таблиц данных и критериев:
В ячейке B2 запишем условие отбора данных следующим способом:
Данный вариант записи позволяет унифицировать критерий для поиска данных в изменяющейся таблице (если число записей будет увеличиваться или уменьшаться со временем).
В результате получим следующее:
В ячейке A4 запишем следующую формулу:
- A8:F15 – диапазон ячеек, в которых хранится БД;
- 1 – числовое указание номера поля (столбца), из которого будет выводиться значение (необходимо вывести Бренд);
- A2:F3 – диапазон ячеек, в которых хранится таблица критериев.
При изменении значений в таблице параметров условий мы будем автоматически получать выборку соответственных им результатов.
Пример составления запросов для выборки из базы данных в Excel
Пример 2. Используя таблицу из предыдущего примера выводить все данные для отдельных брендов, указанных в качестве критерия поиска (например, отобразить все характеристики модели бренда Apple и т. д.)
В данном случае для вывода данных о бренде Meizu запишем в ячейку A3 условие:
Для решения задачи можно в каждую ячейку ввести формулу БИЗВЛЕЧЬ, но это займет слишком много времени. Поэтому выделим диапазон ячеек A4:F4 и введем следующую формулу массива CTRL+SHIFT+Enter:
Запись СТОЛБЕЦ(A1:F1) интерпретируется как отдельные ссылки на ячейки из диапазона A1:F1, а функция СТОЛБЕЦ поочередно возвращает ссылки на поля данных исследуемой таблицы.
Получаем следующий результат:
Существенный недостаток функции БИЗВЛЕЧЬ – отсутствие возможности вывода сразу нескольких записей, если были найдены 2 и более совпадений. В таких случаях используют комбинации других функций Excel.
Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: MS SQL Server Management Studio, Aquafold Aqua Data Studio, DBeaver и т.п., а вероятность их установки в краткосрочной перспективе близка к нолю. В то же время, присутствует острая необходимость подключения к этой самой БД и работы с данными. Как оказалось, на помощь может прийти старый добрый MS Excel.
В моем случае требовалось подключиться к MS SQL Server, однако, MS Excel умеет устанавливать соединение не только с ним, но и с большинством современных БД: MySQL, PostgreeSQL, IBM DB2 и даже Oracle и Teradata, а также с файлами данных CSV, XML, JSON, XLS(X), MDB и другими.
Теперь немного о действиях, совершенных мной с целью подключения к базе:
В новой книге на ленте выбираем «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Из других источников» -> «(4) С сервера SQL Server».
Далее, в окне Мастера подключения к данным, заполняем «(1) Имя сервера» -> «(2) Учетные сведения»[ -> «(3) Имя пользователя» и «Пароль»]. Таким образом, мы сообщаем MS Excel, с каким сервером мы хотим установить соединение и какой метод аутентификации хотим использовать. Я использовал «проверку подлинности Windows», но возможно также указать учетные данные отличные от установленных в Windows.
Выбираем целевую «(1) Базу данных» -> «(2)(3) Определенную таблицу» или «Несколько таблиц» или же базу в целом (тогда оба «чекбокса» оставляем пустыми).
После всех проделанных манипуляций, Мастер подключения предложит сохранить файл подключения. Потребуется задать «(1) Имя файла». Желательно также указать «(2) Описание» и «(3) Понятное имя файла», чтобы спустя время было понятно какой файл подключения к какой базе или таблице обращается.
Теперь выбрать созданное подключение можно будет следующим образом: «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Существующие подключения».
Открыв только что созданное подключение, в случае если вы соединялись с базой в целом, MS Excel опять предложит выбрать одну или несколько конкретных таблиц:
Определив таблицы, MS Excel предложит выбрать «(1) Способ представления данных» и «(2) Куда следует поместить данные». Для простоты я выбрал табличное представление и размещение на уже имеющемся листе, чтобы не плодить новые. Далее следует нажать на «(3) Свойства».
В свойствах подключения, нужно перейти на вкладку «(1) Определение». Здесь можно выбрать «(2) Тип команды». Даже если требуется выгружать лишь одну таблицу без каких-либо связей, настоятельно рекомендую выбрать SQL команду, чтобы иметь возможность ограничить размер выгружаемой таблицы (например, с помощью TOP(n)). Так, если вы попытаетесь выгрузить целиком таблицу базы, это может привести в лучшем случае к замедлению работы MS Excel, а в худшем к падению программы, к тому же – это необоснованная нагрузка на сам сервер базы данных и на сеть. После того как «(3) Текст команды» будет введен и нажата кнопка «ОК», MS Excel предложит сохранить изменения запроса – отвечаем положительно.
Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению фильтра ). В отличие от фильтра отобранные строки будут помещены в отдельную таблицу.
Пусть имеется таблица с перечнем сотрудников (см. лист Сотрудники в файле примера ). Все сотрудники работают в дирекциях, а дирекции состоят из отделов и руководителей дирекций. В каждой строке таблицы содержится фамилия сотрудника, номер его телефона, номер его комнаты и наименование подразделения, к которому он относится.
Задача
Необходимо отобразить всех сотрудников выбранного отдела.
Решение с помощью стандартного фильтра
Это можно легко сделать с помощью стандартного фильтра EXCEL. Выделите заголовки таблицы и нажмите CTRL+SHIFT+L . Через выпадающий список у заголовка Отделы выберите нужный отдел и нажмите ОК.
Будут отображены все сотрудники выбранного отдела.
Решение с помощью трехуровневого Связанного списка
Другим подходом является использование трехуровневого Связанного списка с использованием элементов управления формы , где из исходной таблицы Сотрудники последовательно выбирая Дирекцию и Отдел, можно быстро отобразить всех сотрудников соответствующего отдела в отдельной таблице.
Преимущества использования трехуровневого Связанного списка – субъективны. Кому-то нравится работать с фильтром, кому-то со списками. Работать со списками несколько быстрее и информативнее (выбрав дирекцию, автоматически получим список всех ее отделов). Кроме того, в отличие от фильтра отобранные строки будут помещены в отдельную таблицу - своеобразный отчет, который можно форматировать в стиль отличный от исходной таблицы. В этот отчет можно вынести не все столбцы, а только нужные (хотя после применения фильтра ненужные столбцы можно скрыть).
Основной недостаток – сложность реализации трехуровневого Связанного списка . Но, единожды его создав и поняв принцип работы, этот недостаток в достаточной мере компенсируется.
Алгоритм создания запроса на выборку следующий:
ШАГ 1
Сначала создадим Лист Списки , в котором будут содержаться перечень дирекций и названия отделов (см. файл примера ).
Перечень дирекций (столбец А ) будет извлекаться формулой массива из исходной таблицы с перечнем сотрудников:
Подробности работы этой формулы можно прочитать в статье Отбор уникальных значений .
Перечень отделов (диапазон B 2: E 8 ) будет извлекаться аналогичной формулой массива в соответствующие столбцы на Листе Списки :
=ЕСЛИОШИБКА(ИНДЕКС(Сотрудники[Отдел]; ПОИСКПОЗ(0;ЕСЛИ(B$1=Сотрудники[Дирекция];0;1)+ СЧЁТЕСЛИ($B$1:B1;Сотрудники[Отдел]);0));"")
ШАГ 2
Теперь создадим Лист Просмотр , в котором будут содержаться перечень сотрудников выбранного отдела и два списка (дирекции и отделы), сформированных на основе Элемента управления форм Список .
Первый список создадим для вывода перечня дирекций. Источником строк для него будет созданный ранее динамический диапазон Дирекции. Свяжем его с ячейкой А1 .
Теперь создадим Динамический диапазон Выбранная_дирекция , который будет содержать название выбранной дирекции:
Также создадим Динамический диапазон Отделы , который будет содержать перечень отделов выбранной дирекции и служить источником строк для второго списка:
И, наконец, для вывода фамилий сотрудников (ячейка B 6 ), их номеров телефонов и комнат используем зубодробительную формулу:
Дано: .xls (Excel) файл с одним листом в 4 числовых колонки и 1000 строк.
Требуется: Загрузить его в SQL базу данных, таблица с соответствующими колонками имеется. Ну и, сперва, оценить время на решение.
Ну и мне стало интересно, сколькими максимально разнообразными и простыми способами я могу решить эту задачу, используя только то что есть у меня на компьютере.
Update: В коментариях рассказывают методы заполнения столбцов без «протягивания»: раз, два
0. Прежде чем приступить к работе
В условиях задачи есть два очень важных пункта:
- нам дают готовый файл с данными
- таблица в базе данных уже создана
Начну со второго. Находящиеся в таблице данные могут не дать записать те данные что есть у Вас. Ну, например, если какой-то столбец это unique id, а в имеющейся таблице такой id уже есть. Тут всё просто. Узнаёте что делать с данными и либо первой операцией очищаете таблицу, либо вместо INSERT делаете REPLACE.
А теперь про полученный файл. Вы вот прямо так будете гнать его в базу? Уверены? А вы уверены что вам туда ничего лишнего не напихали? Все 1000 строк глазами проглядывать будете?
Я сделал просто — прямо в редакторе XLS-файла (в моём случае — LibreOffice Calc) применил регулярные выражения для удаления всего кроме числовых значений.
В результате остались только цифры, разделитель «запятая» и знак «минус».
Дальше я сделал замену «запятая» на «точка» и при сохранении в CSV получал данные вот такого вида:
Теперь данные безопасны и SQL-friendly.
Я этому так много времени уделяю не из природного занудства, а потому что если данные не будут загружены из-за уже имеющихся или уничтожены из-за инъекции, то поставленная задача не будет выполнена.
Как говорил известный эксперт: «Лучше день потерять, зато потом за пять минут долететь!»
Итак, подготовительный этап завершён — полетели. В смысле, приступаем к выполнению задания различными способами.
Update 2: в комментариях навели на идею. Данные могут быть безопасны, но состоять из бессмысленного набора цифр, "-" и ",". В этом случае импорт сработает неполностью. Как поступаем:
— сперва делать прогон на тестовой таблице
— сразу в рабочую, но с роллбэком
?
1. Загружаю CSV в phpMyAdmin
Если есть phpMyAdmin (или аналог для используемого SQL), то:
- обеззараживаем данные (см. п.0)
- первой строкой в файле прописываем имена полей в SQL
- сохраняем CSV
- загружаем
2. SQL в веб-форму
Если нет веб-морды принимающей CSV, но есть принимающая просто SQL запросы то, казалось бы, это указанный в исходном тексте вариант:
Добавлю колонку в excel файле, куда во всех ячейках вставлю (растяну) «insert into» и дополнительные колонки с запятыми, получу sql скрипт. Сразу плюс, даже в оценке не нуждаемся.
А вот и нет. Ну правда же, протягивать колонку с INSERT INTO ещё можно, но протягивать запятые. На 1000 строк. И так три раза. Нафиг-нафиг.
Тут вариантов два.
- обеззараживаем данные (см. п.0)
- сохранить данные в CSV, используя разделитель «запятая»
- открыть CSV в code-based текстовом редакторе (в моём случае — Notepad++ )
- заменить перевод строк на
3. Клиент SQL
Виндового клиента MySQL у меня нет уже давно (ни гуёвого, ни консольного). Да и доступ извне к нему врядли дадут. Поэтому заливаю файл полученный в п. 2 на сервер и делаю там в консоли.
4. PHP-скрипт
Конечно же, идеальным вариантом будет написать скрипт на 10 строк, который будет делать fgetcsv(), формировать INSERT INTO и пулять всё это в базу.
Ну правда же, тот кто даёт Вам это тестовое задание в любой момент скажет «Ой, а мне надо что бы строчки у которых в третьем столбце стоят нечётные целые числа шли в другую таблицу» или «а в пятый столбец нужно было записывать кубический корень из произведения значений данных из всех 4 столбцов».
И у него не будет ответа на вопрос «чувак, а чё ты сам эти расчёты в Excel не сделал?». Всё что он сможет сказать «не я такой — жизнь такая».
Кстати, в этот скрипт можно вставить веб-форму с загрузкой CSV-файла, сделать обеззараживание данных и пусть автор задания сам всё грузит.
Хотя, конечно же, такой вариант не подходит. Этот скрипт с формочкой потом останется на сайте, про него забудут и будет какая-никакая, а дырка.
Поэтому, решаем задание так же как в п.2, только сохраняем всё в php-файл и вместо
Ну и mysql_connect в начале
5. У меня же теперь есть Linux!
После обретения Windows Subsystem for Linux жизнь прям заиграла новыми красками.
- обеззараживаем данные (см. п.0)
- сохранить данные в CSV, используя разделитель «запятая»
- и….
А вот сейчас будет кусок из-за которых этот текст не только в хабе «MySQL», но и в хабе «Разработка веб-сайтов».
Кроме приведённых ранее очевидных вариантах решения поставленной задачи есть ещё 3:
Читайте также: