Как автоматизировать несколько книг Excel и выполнить анализ

Примените это пошаговое руководство, чтобы решить проблему с помощью PDI с Magical Metadata Injection.

Представьте, что вам поручили изучить поставщиков Mircosoft или Apple и провести анализ для принятия более правильного решения. Это означает изучение сотен поставщиков с разными форматами, структурой, названиями и т. Д. Итак, это очень трудоемкий процесс, чтобы сопоставить информацию от разных поставщиков, стандартизировать и провести анализ одного и того же, не так ли?

Кроме того, нам придется выполнять одно и то же действие с определенной периодичностью, чтобы понять новые тенденции.

Это хороший вариант использования для автоматизации и построения конвейера данных. Сегодня в промышленности используется несколько подходов. Некоторые пишут программы на python / java, некоторые используют макросы VBA, некоторые используют инструменты ETL и так далее, и так далее.

Мы будем использовать Pentaho Data Integration (Kettle) - мощный инструмент ETL для выполнения этой задачи. Если вы новичок в этом инструменте, я бы порекомендовал вам просмотреть сообщение о создании первого конвейера данных с использованием PDI, вот ссылка.

Истории пользователей - давайте определимся с нашим вариантом использования

Давайте напишем наши пользовательские истории для указанного варианта использования. Написание пользовательских историй и создание раскадровки - хорошая практика для отслеживания статуса или понимания всего потока, используя простой английский. Это также помогает нам в определении архитектуры.

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

• Я хочу прочитать файлы Excel от нескольких поставщиков.
• Я хочу обрабатывать разные структуры и настройки в одном потоке.
• Я хочу сохранить вывод в едином стандартизированном формате (xlsx).

Это три простые пользовательские истории. Однако это непростой вариант использования большинства существующих программ. Итак, давайте разберемся, как реализовать то же самое, используя приведенное ниже пошаговое руководство.

Входные данные

Давайте разберемся с нашими различными поставщиками и исходными данными. Это поможет нам спроектировать поток конвейера данных.

1. Поставщик-1 - У нас есть квитанция о счете в формате Excel от ABC Corp, где данные начинаются с седьмой строки и содержат пять столбцов.

1. Поставщик-2 - У нас есть еще одна квитанция о счете в формате Excel от XYZ Corp. Здесь данные начинаются с пятой строки и содержат шесть столбцов.

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

Тестовые кейсы

Давайте определим наши тестовые примеры для этого конвейера.

• Проверьте журнал на наличие ошибок.
• Проверьте выходной файл и его формат (xlsx).
• Перекрестная проверка двух случайных позиций счетов-фактур от обоих поставщиков в стандартизированном выходном файле.

Шаг 1: Настройка проекта

Создадим каркас для нашего проекта. Мы создадим следующие папки и файлы. Я предпочитаю хранить все, что связано с работой, в одной общей папке под названием Работа.

1. Входные данные - здесь мы будем хранить все файлы поставщиков. Мы можем создать две подпапки внутри папки ввода, а именно. Поставщик-1 и Поставщик-2 (это совершенно необязательно)
2. Вывод - очевидно, для хранения файлов вывода.
3. Метаданные - здесь мы будем хранить метаданные файла (информацию о структуре файла).
4. Преобразование - здесь мы будем хранить наши преобразования, нам нужно создать три преобразования: ReadInputFiles.ktr, InjectMetadata.ktr и ProcessEachInputFile.ktr.
5. Main.kjb - наш файл задания PDI для выполнения всех преобразований, упомянутых в пункте №4.

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

Если у вас возникли трудности с пониманием вышеуказанных шагов или Spoon (настольное приложение), попросите вас перейти по приведенной ниже ссылке.

Шаг - 2: Давайте прочитаем входные файлы.

Согласно истории, мы прочитали несколько файлов в нескольких папках. Итак, давайте сделаем то же самое. Идея состоит в том, чтобы перебирать каждый файл и выполнять операции на каждом уровне файла, имеет смысл?

1. Нам нужно открыть преобразование ReadInputFiles.ktr в Spoon (Desktop Application)
2. На вкладке Дизайн найдите плагин / библиотеку «Получить имена файлов» и перетащите его на холст.

Теперь дважды щелкните виджет или щелкните правой кнопкой мыши и выберите редактировать для добавления наших настраиваемых свойств. PDI дает нам множество вариантов конфигурации.

• Давайте изменим имя шага на «read_input_files»; соблюдать стандартное соглашение об именах.
• Мы можем просмотреть или напрямую вставить путь к папке ввода (D: \ Work \ AutomateMultipleExcel \ Input) в параметр Файл или каталог.
• Поскольку мы должны читать все файлы от разных поставщиков. Мы будем использовать регулярное выражение. Я подробно расскажу о том же, но это очень эффективный метод поиска текста. В нашем поле Подстановочный знак (RegExp) мы будем использовать регулярное выражение a *. *; это означает, что нужно включить весь текст, а затем нажать кнопку Добавить.
• После добавления нам нужно изменить логическое значение на «Y» в поле Включить подпапки.

Этот плагин предоставит нам список папок и файлов в указанном каталоге; в нашем случае это каталог Input.

Давайте быстро проверим, дает ли он желаемый результат, нажав кнопку Предварительный просмотр строк. Строки предварительного просмотра должны выглядеть, как показано ниже. Если он совпадает, вы можете нажать Закрыть в предыдущих строках и ОК на шаге, чтобы сохранить изменения.

Как вы могли заметить, мы видим и файлы, и папки. Для дальнейшей обработки нам нужны только файлы. Итак, давайте отфильтруем то же самое, используя плагин «Фильтровать строки»; снова тот же процесс, найдите то же самое на вкладке Дизайн и дважды щелкните, чтобы напрямую создать прыжок.

PDI назвал плагины наилучшим образом, чтобы в них не было двусмысленности.

«Фильтровать строки» - это шаг, используемый для применения условной логики, и в нашем случае мы хотим передать только строки, которые являются файлами, а не папками. Если вы видите вывод Предварительный просмотр строк на нашем предыдущем шаге (получение имен файлов), PDI дает нам столбец «тип» для каждой строки; мы можем использовать то же самое для нашей логики.

• Нам нужно изменить имя, чтобы оно соответствовало нашему соглашению об именах «filter_only_files».
• Нам нужно выбрать столбец «тип» в качестве первой переменной, затем «равно» (у нас также есть много других вариантов), а затем, наконец, ввести жестко запрограммированное слово «файл» в параметре значения, как показано на изображении ниже.

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

Здесь нам нужно использовать плагин / шаг шага «Копировать строки в результат», чтобы скопировать список в память. Найдите имя шага и дважды щелкните его. Никаких дополнительных настроек не требуется.

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

Шаг - 3: Создайте шаблон

Нам нужно создать пустой шаблон со всеми шагами / плагинами, которые мы хотим использовать для наших манипуляций с данными. Итак, для нашего варианта использования нам требуется всего два шага: один - программа для чтения в Excel, а другой - для записи в Excel. Здесь нам нужно использовать файл ProcessEachInputFile.ktr и выполнить следующие шаги.

Для этого мы будем использовать плагины «Microsoft Excel input», «Select value» и «Microsoft Excel writer».

• Давайте переименуем шаги в input, rename_and_remove и output вместо ввода Microsoft Excel, выбора значения и записи Microsoft Excel соответственно.
• В подключаемом модуле Microsoft Excel input измените механизм на Excel 2007 XLSX (Apache POI) в поле Тип электронной таблицы (механизм). На вкладке Дополнительные поля вывода добавьте SupplierName в поле для короткого имени.

• В подключаемом модуле Microsoft Excel writer и на вкладке Файл и лист добавьте путь к выходному файлу (D: \ Work \ AutomateMultipleExcel \ Output \ SupplierOutputFile), измените расширение на xlsx [ Excel 2007 и более поздние версии] в поле Расширение выберите Использовать существующий файл для записи в поле Если выходной файл существует и выберите записать в существующий лист в Если лист существует в выходном файле.
• В подключаемом модуле Microsoft Excel Writer на вкладке Содержание выберите сдвигать существующие ячейки вниз в разделе При написании строк установите флажок Начать запись в конце листа (добавляемые строки) и установите флажок Не указывать заголовок.
• Нам не нужно касаться шага «Выбрать значения».

Готово, шаблон готов. Нам не нужно добавлять никаких дополнительных свойств, мы будем вводить такие свойства, как путь к файлу, имя листа, имена полей и т. Д. Во время выполнения.

Обратите внимание, что все плагины PDI не поддерживают внедрение метаданных. Вы можете проверить поддерживаемые плагины / шаги здесь.

Шаг - 4: Создайте метаданные

Нам нужно создать метаданные для чтения и записи файлов. Метаданные - это «данные, которые описывают другие данные», если речь идет об информации, описывающей наши входные и выходные данные в Excel. Мы будем хранить все настраиваемые свойства в файле Excel (его также можно получить из базы данных).

Мы создадим файл Metadata.xlsx в нашей папке метаданных с двумя вкладками, как указано ниже.

1. Мастер - здесь мы будем хранить всю основную информацию, такую ​​как начальная строка формата файла, имя листа и т. Д.
2. SourceFileMeta - мы будем хранить всю информацию на уровне поля о каждом исходном файле. Эти столбцы будут частично совпадать с вкладкой Поля на этапе ввода Microsoft Excel.
3. TargetFileMeta - мы будем хранить все необходимые поля вывода.

Обратите внимание, что PDI читает файлы в соответствии с индексом строки и столбца, начиная с 0. Поэтому нам нужно убедиться, что информация метаданных верна в соответствии с входным файлом.

Шаг - 5: введите метаданные

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

Здесь я хотел бы познакомить вас с чрезвычайно мощной функцией PDI ; Внедрение метаданных ETL.

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

Мы будем использовать файл InjectMetadata.ktr для ввода метаинформации, как показано ниже.

• Если вы помните, мы передали список файлов, но не получили его. Здесь мы будем получать эту информацию по каждому файлу за раз. Следовательно, наш первый плагин - «Получать строки из результатов». Давайте быстро переименуем его в соответствии с нашим соглашением об именах на «get_file_information».
• Нам нужно добавить два поля, а именно. filename и short_filename в Получить строки из шага результата.

• Нам потребуется несколько этапов «Ввод в Microsoft Excel» и «Соединение строк» ​​для чтения метаданных, основного файла и вывода. Идея состоит в том, чтобы получить только релевантную информацию из файла метаданных Excel во время выполнения.

• Здесь нам нужно подключить несколько переходов. Обратите внимание на направление хмеля. Кроме того, при подключении нескольких переходов из одного шага вы увидите предупреждение ниже, нажмите кнопку Копировать.

• На этапе master_information нам нужно найти Metadata.xlsx в файле или каталоге. На вкладке Таблицы нам нужно выбрать Мастер в качестве имени листа и 0 в качестве Начало строка и начальный столбец. На вкладке Поля просто нажмите кнопку Получить поля из строк заголовков….

• На этапах excel meta_information и select_value нам нужно снова найти Metadata.xlsx в файле или каталоге. На вкладке Таблицы нам нужно выбрать SourceFileMeta в качестве имени листа и тот же 0 в качестве нашего Начальная строка и Начальный столбец. На вкладке Поля просто нажмите кнопку Получить поля из строк заголовков….

• На этапах excel target_information нам нужно снова найти Metadata.xlsx в файле или каталоге. На вкладке Таблицы нам нужно выбрать TargetFileMeta в качестве имени листа и тот же 0 в качестве нашего Начало строка и начальный столбец. На вкладке Поля просто нажмите кнопку Получить поля из строк заголовков….
• Мы будем использовать Excel 2007 XLSX (Apache POI) на всех этапах в качестве нашего механизма.
• В join_meta, join_select_values ​​ и join_master мы добавим следующее условие. Это необходимо для фильтрации строк из файла Metadata.xlsx. Мы использовали short_filename в качестве нашего уникального идентификатора (мы не получим таких привилегий в реальном сценарии).

• Теперь давайте выполним поиск и перетащим плагин ETL Metadata Injection на холст. Нам нужно найти наш файл шаблона (D: \ Work \ AutomateMultipleExcel \ Transformation \ ProcessEachInputFile.ktr).
• Он автоматически прочитает преобразование нашего шаблона и перечислит все настраиваемые свойства. Теперь нам просто нужно сопоставить соответствующие поля метаданных со свойствами. Ниже приведены сопоставления.

Это должно быть все для трансформации инжектора. Давайте объединим и выполним то же самое.

Шаг - 6: Соедините точки и создайте основную петлю.

Наконец, мы подошли к самому последнему этапу соединения различных частей и выполнения основной работы. Как показано выше, мы будем использовать файл задания (Main.kjb), чтобы сделать то же самое.

Задания всегда должны начинаться с шага / плагина с названием «Начало» и заканчиваться шагом «Успех». Давайте объединим эти шаги двумя шагами «Преобразования»; один для ReadInputFiles.ktr, а другой для InjectMetadata.ktr (поскольку мы использовали наш ProcessEachInputFile.ktr на этапе инжектора). О, да! Теперь я могу связать отношения.

• В первом преобразовании найдите преобразование ReadInputFiles (D: \ Work \ AutomateMultipleExcel \ Transformation \ ReadInputFiles.ktr).
• Во втором преобразовании найдите преобразование InjectMetadata (D: \ Work \ AutomateMultipleExcel \ Transformation \ InjectMetadata.ktr) и установите флажок Выполнить каждую входную строку.

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

Давайте приступим к работе и запустим конвейер данных.

Теперь мы можем проверить наш файл в выходном каталоге. Давайте проведем тестирование на тестовых примерах.

Теперь мы можем анализировать данные в соответствии с нашими требованиями.

Заключение

Автоматизация нескольких таблиц Excel может быть забавным занятием. Вышеупомянутая реализация не идеальна, но решит нашу постановку проблемы. Мы можем еще больше обобщить этот конвейер, добавив переменные вместо жестко запрограммированных значений. Мы можем аналогичным образом построить / настроить этот конвейер в соответствии с нашими требованиями. Далее мы рассмотрим загрузку данных из сценария использования электронной почты.

Увидимся в следующем посте. Счастливый ETL

Если вам нравится похожий контент, я бы порекомендовал вам подписаться, используя ссылку ниже.