Кейс

Автоматизация обработки таблиц в Excel и перенос данных в MySQL

Команда XLTools
31 октября, 2017
читать 10 мин
нет комментариев

Обзор кейса

Карло, ИТ консультант, работал над сложной, но довольно типичной задачей. Ему нужно было разработать систему, которая бы создавала отчеты для руководства на основе Excel файлов, полученных от порядка 10-20 исследовательских лабораторий.

Сначала эти отчеты создавали в Excel – в основном вручную – и направляли руководству и лабораториям в печатном виде или PDF.

процесс до автоматизации

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

«Первоначальный подход занимал около 20 дней ужасной работы. Нам приходилось иметь дело с ошибками после копирования огромного объема данных из одного сводного отчета в Excel в отдельные отчеты по каждой задействованной лаборатории. Форматы очень разные, поэтому не представлялось возможным автоматизировать этот процесс в Excel. Кроме того, клиенту нужны были графики, где можно было бы сравнить результаты, полученные много месяцев назад – такое возможно только при выгрузке данных в MySQL.»

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

Один из подходов к автоматизации – создать специальный макрос VBA. Но при таком большом объеме необходимых операций написание макроса с нуля займет неоправданно много времени. Кроме того, если вы хоть раз пробовали настроить интеграцию макроса с внешней базой данных, вы представляете, насколько утомительной может быть синхронизация вручную. Одно небольшое изменение в процессе (к примеру, другое название исходной таблицы) ведет к бесконечным правкам в скрипте.

Надстройка XLTools, с другой стороны, дала Карло гибкие инструменты для автоматизации всего процесса без VBA: извлечь, подготовить и экспортировать данные в базу данных MySQL.

процесс после автоматизации

XLTools Автоматизация – это мощный и многоцелевой инструмент. Последовательность всех операций записана на одном листе Excel, и все операции вручную сводятся к нажатию одной единственной кнопки.

«Мой клиент очень доволен результатами, потому что теперь мы можем выполнить всю работу за 2 часа вместо 20 дней. XLTools помог нам облегчить работу с текущей XLS структурой. Процесс открыт для правок в будущем – скрипт очень легко изменить. Сейчас мы переносим данные в базу данных за минуты, где уже формируем все нужные отчеты.»

Рассмотрим некоторые операции, автоматизированные в этом процессе.

Шаг 1. Автоматически собрать данные из файлов Excel

XLS файлы, полученные из исследовательских лабораторий, стандартизированы. Они всегда следуют одному шаблону. Нам нужно извлечь данные из этих исходных файлов и подготовить к дальнейшей обработке.

Именно это и выполняет команда XLTools.ExtractTable:
  • Она извлекает данные из внешних Excel или CSV файлов. Вы можете собрать все необходимые данные в одном месте и продолжить работу с полным объемом данных. По большому счету, это инструмент копирования-вставки.
  • Извлекая простой диапазон, он будет вставлен как именованная таблица. Только данные в формате таблицы могут служить источником данных для дальнейших операций, в т.ч. для выполнения SQL запросов в Excel.

Например, нам нужно извлечь эту таблицу с числовыми кодами, присвоенными лабораториям:

extract table before

Для этого, пропишем команду автоматизации XLTools.ExtractTable:
XLTools.ExtractTable
WorkbookFile:source.xls
Range:LABCODE!E:F
SkipTopRowsCount:4
ApplyTableName:LabCodes
Headers:lab,stat
PreserveFormat:TRUE
OutputTo:NewHiddenSheet[LabCodes]

Укажите диапазон данных, которые нужно извлечь:

  • Книга под названием “source.xls”. Обратите внимание, что исходные файлы должны находиться в той же папке, что и ваш файл автоматизации.
  • Диапазон E:F на листе “LABCODE”
  • Пропустить (Skip) 4 строки сверху, поскольку данные по факту начинаются со строки 5

Укажите, куда поместить извлеченные данные:

  • Назвать новую таблицу “LabCodes”

  • Присвоить заголовки “lab” and “stat”

  • Сохранить формат ячеек (PreserveFormat)

  • Поместить результат на новый скрытый лист “LabCodes”.

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

Выполнив эту команду, получим такую таблицу “LabCodes”, начиная с ячейки A1 на новом скрытом листе “LabCodes”:

Extract table after

Точно так же мы извлекли другие необходимые таблицы из файлов лабораторий. После этого мы готовы к работе с полным объемом данных.

Шаг 2. Объединить две таблицы Excel c помощью автоматизированного SQL запроса

В самом начале проекта, Карло составил схему, как конкретные записи должны быть выгружены из Excel в MySQL. Используя эту схему, мы помогли автоматизировать подготовку данных к экспорту.

Для обработки данных мы рекомендуем команду XLTools.SQLSelect. Мы очень любим и сами часто используем эту команду:
  • SQL запросы – первый выбор, если нужно объединить две или более таблиц по общему полю;
  • XLTools позволяет применять SQL SELECT запросы напрямую к таблицам Excel и быстро подготовить данные;
  • SQL позволяет выполнять несколько сложных задач за один раз – можно реорганизовать и отформатировать данные всего за пару запросов.

Например, нам нужно было объединить эти две таблица и переименовать столбцы. Кроме того, нам нужно было обработать положительные/отрицательные значения: столбец “sign” указывает, что значения в столбце “d” положительные или отрицательные.

merge tables before

Для этого, мы используем команду XLTools.SQLSelect:
XLTools.SQLSelect
SQLQuery:SELECT
tbl2.[lab] as [ic_code],
tbl1.[Nb] as [rank],
tbl1.[%] as [percent],
tbl1.[N°] as [lab_code],
CASE WHEN tbl1.[sign] = '-' THEN -1 * tbl1.[d] ELSE tbl1.[d] END as [dev],
tbl1.[Sd] as [s_dev],
tbl1.[D2] as [dist],
tbl1.[Method] as [method],
FROM [source.xls].[Table1]tbl1
LEFT JOIN [LabCodes]tbl2 ON tbl1.[Nb] = tbl2.[stat]
PreserveFormat:TRUE
ApplyTableName:data
OutputTo:NewSheet[data]

Укажите, какие поля нужно выбрать (SELECT):

  • Взять столбец “lab” из таблицы 2 и назвать “ic_code”
  • Взять столбец “Nb” из таблицы 1 и назвать “rank”
  • Взять столбец “%” из таблицы 1 и назвать “percent”
  • Взять столбец “N°” из таблицы 1 и назвать “lab_code”
  • Взять столбец “sign” из таблицы 1 и назвать “dev”. В то же время добавляем условие (CASE WHEN): если значение в столбце “sign” равно “-”, тогда умножить значение в столбце “d” на -1
  • Взять столбец “Sd” из таблицы 1 и назвать “s_dev”
  • Взять столбец “D2” из таблицы 1 и назвать “dist”
  • Взять столбец “Method” из таблицы 1 и назвать “method”
  • Найти и выбрать таблицу “Table1” из (FROM) книги “source.xls”
  • Объединить таблицы по значениям (LEFT JOIN ON) в столбцах “Nb” и “stat”

Укажите, как выгрузить результат запроса:

  • Сохранить формат ячеек (PreserveFormat)
  • Присвоить новой таблице название “data”
  • Поместить результат на новый лист “data”

Как только вы выполните эту команду, через секунду будет сгенерирована таблица результата:

SQL Query merge result

Обратите внимание, что все столбцы имеют новые названия. Значения в столбце “dev” либо положительные, либо отрицательные. XLTools.SQLSelect – отличный инструмент для автоматизации обработки таблиц Excel. Практически все сложные манипуляции в проекте Карло мы выполнили с помощью SQL запросов.

Шаг 3. Автоматически экспортировать данные из Excel в базу данных MySQL

В проекте Карло было немало сложных и вдохновляющих задач (СПАСИБО)! Если извлечение данных и SQL запросы уже давно включены в Автоматизацию XLTools, то экспорт из Excel в MySQL – новая разработка.

После того, как мы подготовили и нужным образом отформатировали данные, создаем команду XLTools.ExportToMySQL:
XLTools.SQLSelect
Tables:zscore-fix,zscore-pt,outliers,repeatability,data
Server:Имя сервера или IP адрес сервера
Database:Имя вашей БД
Uid:Имя пользователя для доступа в вашу БД
Pwd:Пароль для доступа в вашу БД
ApplyTableName:ExportResults
OutputTo:NewSheet[ExportResults]

Укажите, какие таблицы нужно экспортировать в базу данных:

  • Взять таблицы “zscore-fix”, ”zscore-pt”, ”outliers”, ”repeatability”, ”data”

Введите данные для подключения к MySQL:

  • Введите учетные данные для входа в вашу БД: сервер, база данных, имя пользователя и пароль. Это позволит XLTools связаться с вашей базой данных.

Укажите, где поместить отчет об экспорте данных:

  • Присвоить таблице имя “ExportResults”
  • Поместить результат на новый лист “ExportResults”

По завершении выполнения, команда выгрузит данные в MySQL и создаст сводный отчет об экспорте:

export to my sql report

Отчет показывает, сколько записей (строк) из каждой таблицы Excel было экспортировано в БД. В случае ошибок во время экспорта, они будут указаны с последнем столбце. Кстати, описание ошибок будет приведено на языке вашей базы данных. Наш клиент Карло работает в Риме, и ошибки будут выводиться на итальянском.

В данном случае, экспорт двух записей в таблице “zscore-fix” не был успешен. Похоже, некоторые ячейки в этой таблице были пустыми – а такие записи не могут быть экспортированы в MySQL. Теперь легко вернуться и исправить ошибки.

Привязать выполнение всего процесса к одной кнопке

XLTools Автоматизация также позволяет назначать пользовательские кнопки для этих команд. В примерах, рассмотренных выше, мы выполняли каждую команду отдельно. Но мы можем выделить всю последовательность команд и привязать их к собственным кнопкам на вкладке XLTools.

Напр., команды Карло привязаны к трем кнопкам. “Prepare & Export” запускает весь процесс (извлечение, подготовка и экспорт данных) по нажатию всего одной кнопки:

export custom buttons

У вас есть процессы в Excel, которые можно автоматизировать?

Свяжитесь с нами! Готовы помочь.
Обсудим вашу задачу и убедимся, что XLTools может с ней справиться. Помощь с настройкой кастомизированной автоматизации включена в стоимость лицензии.

Что если в XLTools нет (пока нет) функционала, нужного для вашей конкретной задачи? Мы постараемся разработать его для вас. В основе многих новых надстроек – предложения наших пользователей. Благодаря проекту Карло, Автоматизация теперь поддерживает Экспорт из Excel в MySQL.

«Мы выбрали XLTools, потому что команда очень быстро отреагировала на наш запрос. Они были готовы обсуждать варианты, помогли нам с кастомным решением и были всегда на связи.»
Карло Мелис, ИТ консультант

Благодарность: Карло Мелис (Carlo Melis), ИТ консультант, работает по заданию международного негосударственного органа по сертификации пищевой продукции, Рим, Италия.

Надстройки в фокусе: Автоматизация без VBA (извлечение таблиц, SQL запросы, экспорт из Excel в MySQL). Поддерживается в версии XLTools 5.0.0.762 и выше.
Тэги:
Автоматизация Excel SQL запросы Подготовка данных
Вопросы или комментарии?
Андрей АкуленкоЗдравствуйте! Я так и не понял. В надстройке есть функция экспорта таблиц в mysql? Например, как это реализовано в надстройке от devart.
Декабрь 12, 2021 в 11:15
Анастасия Здравствуйте! Срочно нужна ваша помощь. Не совсем понятно как это происходит. Вы могли бы наглядно продемонстрировать с файлами, в живую. Что-бы можно было пощупать, так сказать. Можно отправить на почту
Февраль 7, 2018 в 08:30
Maria Balobanova

Анастасия, спасибо за интерес к кейсу! К сожалению, не могу направить вам исходные файлы – это конфиденциальные материалы заказчика. В статье в целях примера данные изменены. Принцип работы надстройки XLTools Автоматизация описан здесь, там же можно скачать пособие с примерами и комментариями. Если у вас есть конкретная задача, напипишите нам на support @ xltools.net (по возможности с примером – какие данные есть, что в итоге хотите получить). Мы посмотрим, можно ли Ваш процесс автоматизировать средствами XLTools.

Февраль 8, 2018 в 11:04
Интересные статьи:
Вебинар: обзор надстроек XLTools [Видео] | Блог о продуктивности в Excel
Вебинар: обзор надстроек XLTools [Видео] | Блог о продуктивности в Excel
12 января, 2014
смотреть 20 мин
Прогноз предстоящих дивидендов по акциям российских компаний [Бесплатный шаблон Excel] | Блог о продуктивности в Excel
Прогноз предстоящих дивидендов по акциям российских компаний [Бесплатный шаблон Excel] | Блог о продуктивности в Excel
22 декабря, 2020
читать 10 мин
Узнайте первыми