Как собрать информацию из нескольких эксель файлов в один

Сборка таблиц с разными шапками из нескольких книг

Имеем несколько файлов (в нашем примере — 4 шт., в общем случае — сколько угодно) в одной папке Отчеты:
Внутри эти файлы выглядят вот так:
При этом:

• Нужный нам лист с данными всегда называется Отчет, но может находиться в книге в любом месте.
• Помимо листа Отчет в каждой книге могут быть и другие листы.
• Таблицы с данными имеют разное количество строк и могут начинаться с разной строки на листе.
• Названия одних и тех же столбцов в разных таблицах может различаться (например, Количество = Кол-во = Qty).
• Столбцы в таблицах могут располагаться в разном порядке.

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

Шаг 1. Готовим справочник названий столбцов

Первое, что придётся сделать — подготовить справочник со всеми возможными вариантами названий столбцов и их правильной интерпретацией:

Конвертируем этот список в динамическую «умную» таблицу кнопкой Форматировать как таблицу на вкладке Главная (Home — Format as Table) или сочетанием клавиш Ctrl + T и загружаем в Power Query командой Данные — Из таблицы/диапазона (Data — From Table/Range) . В последних версиях Excel её переименовали в С листа (From sheet) .

В окне редактора запросов Power Query традиционно удаляем шаг Измененный тип (Changed Type) и добавляем вместо него новый шаг, нажав на кнопку f_x в строке формул (если её не видно, то включить её можно на вкладке Просмотр) и вводим туда формулу на встроенном в Power Query языке М:

Эта команда преобразует загруженную на предыдущем шаге Источник таблицу-справочник в список, состоящий из вложенных списков (List), каждый из которых, в свою очередь, представляет собой пару значений Было-Стало из одной строки:

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

Закончив преобразование, выбираем команды Главная — Закрыть и загрузить — Закрыть и загрузить в. и вид импорта Только создать подключение (Home — Close&Load — Close&Load to. — Only create connection) и возвращаемся обратно в Excel.

Шаг 2. Грузим из всех файлов всё как есть

Теперь давайте загрузим содержимое всех наших файлов из папки — пока в том виде, как есть. Выбираем команды Данные — Получить данные — Из файла — Из папки (Data — Get Data — From file — From folder) и затем папку, где лежат наши исходные книги.

В окне предварительного просмотра жмем Преобразовать (Transform) или Изменить (Edit) :

А затем разворачиваем содержимое всех загруженных файлов (Binary) кнопкой с двойными стрелками в шапке столбца Content:

Power Query на примере первого файла (Восток.xlsx) спросит нас имя листа, который мы хотим взять из каждой книги — выберем Отчет и нажмём ОК:

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

1. Power Query возьмет первый файл из папки (у нас это будет Восток.xlsx — см. Пример файла) в качестве примера и импортирует его содержимое, создав запрос Преобразовать пример файла. В этом запросе будет несколько простых шагов типа Источник (обращение к файлу), Навигация (выбор листа) и, возможно, поднятие заголовков. Этот запрос умеет загружать данные только из одного конкретного файла Восток.xlsx.
2. На основе этого запроса будет создана связанная с ним функция Преобразовать файл (обозначена характерным значком f_x), где файл-источник будет уже не константой, а переменной величиной — параметром. Таким образом, эта функция может извлекать данные из любой книги, которую мы ей подсунем в качестве аргумента.
3. Функция будет применена по очереди к каждому файлу (Binary) из столбца Content — за это отвечает шаг Вызвать настраиваемую функцию в нашем запросе, который добавляет к списку файлов столбец Преобразовать файл с результатами импорта из каждой книги:

Шаг 3. Шлифуем

На предыдущем скриншоте хорошо видно, что прямая сборка «как есть» получилась некачественной:

• Столбцы перепутаны.
• Много лишних строк (пустых и не только).
• Шапки таблиц не воспринимаются как шапки и перемешаны с данными.

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

Открыв запрос Преобразовать пример файла, добавляем в него шаги по фильтрации ненужных строк (например, по столбцу Column2) и поднятие заголовков кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) . Таблица станет выглядеть уже гораздо лучше.

Чтобы впоследствии столбцы из разных файлов автоматически встали друг под друга — они должны называться одинаково. Выполнить такое массовое переименование по созданному заранее справочнику, можно одной строкой М-кода. Снова нажмём на кнопку f_x в строке формул и добавим функцию для переменования:

= Table.RenameColumns(#»Повышенные заголовки», Заголовки, MissingField.Ignore)

Эта функция берёт таблицу с предыдущего шага Повышенные заголовки и переименовывает в ней все столбцы по вложенному списку-справочнику Заголовки. Третий аргумент MissingField.Ignore нужен, чтобы на тех заголовках, которые есть в справочнике, но при этом отсутствуют в таблице, не возникала ошибка.

Вернувшись в запрос Отчеты мы увидим уже совсем другую картинку — гораздо приятнее предыдущей:

Ссылки по теме

• Что такое Power Query, Power Pivot, Power BI и зачем они нужны пользователю Excel
• Сбор данных из всех файлов заданной папки
• Сборка данных со всех листов книги в одну таблицу

Power Query Базовый №6. Объединить все файлы из папки по вертикали

В этом уроке вы узнаете как объединить все таблицы, которые находятся в разных книгах Excel из одной директории. Например, данные по продажам каждого месяца находятся в отдельном файле. Всего таких файлов довольно много. Вам нужно предварительно каждый файл обработать, а потом все файлы объединить. Делать это вручную очень долго, мучительно и может повлечь за собой много ошибок. В Power Query решить такую задачу проще простого. Смотрите видео и повторяйте за мной.

В этом видео вы узнаете:

• Как объединить все таблицы в одной папке с Power Query
• Как сделать консолидацию всех файлов в папке в Excel
• Как объединить по вертикали все файлы в одной папке

Решение

Разберем 2 примера. Первым пример будет простым. Мы объединим файлы без предварительной обработки.

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

Объединить файлы из одной папки без предварительной обработки

Если предварительная обработка не требуется, то задача решается в 2 логических этапа:

1. На первом этапе мы подключимся к папке и оставим только нужный нам столбец и строки с необходимыми данными
2. Развернем табличный столбец и почистим данные

Объединить файлы из одной папки с предварительной обработкой

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

Сначала нужно подключиться к папке с файлами и развернуть столбец Content, нажав на кнопку:

После нажатия на кнопку Power Query автоматически создаст запросы, функции и параметры:

Все, что вы проделаете с запросом «Пример файла» автоматически применится к каждому файлу в папке.

То, что находится в данном примере находится в запросе sales — это итоговая результирующая таблица.

Примененные функции

• Folder.Files
• Table.SelectColumns
• Table.AddColumn
• Csv.Document
• Table.ExpandTableColumn
• Table.PromoteHeaders
• Table.RemoveRowsWithErrors
• Table.TransformColumnTypes
• Int64.Type
• Table.Skip
• Table.SelectRows
• Table.RenameColumns
• Table.ColumnNames
• Excel.CurrentWorkbook

Код

Без предварительной обработки

let // Подключаемся к папке и выбираем файлы для объединения source = Folder.Files(path & "Котировки csv"), cols_select_1 = Table.SelectColumns(source, ), col_add = Table.AddColumn(cols_select_1, "Таблица", each Csv.Document([Content])), cols_select_2 = Table.SelectColumns(col_add, ), // Развернуть табличный столбец и почистить данные col_expand = Table.ExpandTableColumn( cols_select_2, "Таблица", , < "Таблица.Column1", "Таблица.Column2", "Таблица.Column3", "Таблица.Column4", "Таблица.Column5", "Таблица.Column6", "Таблица.Column7" >), headers_promote = Table.PromoteHeaders(col_expand, [PromoteAllScalars = true]), rows_remove_errors = Table.RemoveRowsWithErrors(headers_promote, ), types_1 = Table.TransformColumnTypes( rows_remove_errors, , > ), types_2 = Table.TransformColumnTypes( types_1, < , , , , >, "en-US" ) in types_2

С предварительной обработкой

Код «Пример файла»:

let source = Folder.Files(path_folder), rows_select = Table.SelectRows(source, each ([Extension] = ".txt")), get_file = rows_select[Content] in get_file

Код «Параметр файла примера1»:

#"Пример файла" meta [ IsParameterQuery = true, BinaryIdentifier = #"Пример файла", Type = "Binary", IsParameterQueryRequired = true ]

Код «Преобразовать пример файла из Продажи»:

let source = Csv.Document( #"Параметр файла примера1", [Delimiter = " ", Columns = 26, Encoding = 65001, QuoteStyle = QuoteStyle.None] ), rows_skip = Table.Skip(source, 4), headers = Table.PromoteHeaders(rows_skip, [PromoteAllScalars = true]), rows_select = Table.SelectRows(headers, each ([Дата] <> "Итого")) in rows_select

Код «Преобразовать файл из Продажи»:

let fn_append = (#"Параметр файла примера1" as binary) => let source = Csv.Document( #"Параметр файла примера1", [ Delimiter = " ", Columns = 26, Encoding = 65001, QuoteStyle = QuoteStyle.None ] ), rows_skip = Table.Skip(source, 4), headers = Table.PromoteHeaders(rows_skip, [PromoteAllScalars = true]), rows_select = Table.SelectRows(headers, each ([Дата] <> "Итого")) in rows_select in fn_append

Код результирующей таблицы:

let source = Folder.Files(path_folder & "Продажи\"), fn_append = Table.AddColumn( source, "Преобразовать файл из Продажи", each #"Преобразовать файл из Продажи"([Content]) ), cols_rename = Table.RenameColumns( fn_append, ), cols_select = Table.SelectColumns( cols_rename, ), col_expand = Table.ExpandTableColumn( cols_select, "Преобразовать файл из Продажи", Table.ColumnNames(#"Преобразовать файл из Продажи"(#"Пример файла")) ) in col_expand

Этот урок входит в Базовый курс Power Query

Номер урока	Урок	Описание
1	Зачем нужен Power Query. Обзор возможностей	Этот урок сам по себе является мини-курсом. Здесь вы узнаете для каких видов операций с данными создан Power Query.
2	Подключение Excel	Подключаемся к файлам Excel. Импортируем данные из таблиц, именных диапазонов, динамических именных диапазонов.
3	Подключение CSV/TXT, таблиц, диапазонов	Подключаемся к к файлам CSV/TXT, Excel.
4	Объединить таблицы по вертикали	Учимся объединять две таблицы по вертикали — combine.
5	Объединить по вертикали все таблицы одной книги друг за другом	Как объединить по вертикали все таблицы одной книги, находящиеся на разных листах Excel.
6	Объединить по вертикали все файлы в папке	Объединяем по вертикали таблицы, которые находятся в разных файлах в одной папке.
7	Объединение таблиц по горизонтали	Учимся объединять таблицы по горизонтали — JOIN, merge.
8	Объединить таблицы с агрегированием	Объединить таблицы по горизонтали и сразу выполнить группировку с агрегированием — JOIN + GROUP BY.
9	Анпивот (Unpivot)	Изучаем операцию Анпивот — из сводной таблицы делаем таблицу с данными.
10	Многоуровневый анпивот (Анпивот с подкатегориями)	Более сложный вариант Анпивота — в строках находится несколько измерений.
11	Скученные данные	Данные собраны в одном столбце, нужно правильно его разбить на несколько.
12	Скученные данные 2	Разбираем еще один пример скученных данных.
13	Ссылка на другую строку	Как сослаться на другую строку.
14	Ссылка на другую строку 2	Как сослаться на другую строку, используя объединение по горизонтали.
15	Виды объединения таблиц по горизонтали	Изучаем виды объединения таблиц по горизонтали — LEFT JOIN, FULL JOIN, INNER JOIN, CROSS JOIN.
16	Виды объединения таблиц по горизонтали 2	Изучаем анти-соединение и соединение таблицы с ней же самой — ANTI JOIN, SELF JOIN.
17	Группировка	Изучаем операцию группировки с агрегированием — GROUP BY.
18	Консолидация множества таблиц пользовательской функцией	Объединяем по вертикали множество таблиц с предварительной обработкой при помощи пользовательской функции.
19	Деление на справочник и факт	Разделим один датасет на два датасета: справочник и факт.
20	Создание параметра	Мы можем ввести значение в какую-то ячейку Excel, а потом передать это значение в формулу Power Query.
21	Таблица параметров	Создадим целую таблицу параметров и будем их использовать в запросах Power Query.
22	Объединение таблиц по вертикали, когда не совпадают заголовки столбцов	Как объединить две таблицы по вертикали, если названия столбцов не совпадают.
23	Поиск ключевых слов	Научимся искать ключевые слова в текстовом поле.
24	Поиск ключевых слов 2	Будем искать ключевые поля в текстовом поле и присваивать этому значению какую-то категорию.

Power Query Базовый №6. Объединить все файлы из папки по вертикали was last modified: 13 мая, 2022 by Admin

Как объединить несколько файлов Excel в один

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

Объединим несколько файлов Excel в один, воспользовавшись силой скрипта VBA

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

Как и в методе «Как объединить несколько файлов Ворд в один», во-первых, прежде чем дать команду объединения двух или нескольких документов в один в Excel, вам нужно их собрать в отдельную папку. Посмотрите на скриншоте, как я это сделал.

Теперь запустим программу VBA. Прочитайте в «Запуск скрипта VBA в Word», потому что принцип в Excel тот же самый.

Теперь, когда вы готовы, вот сам код скрипта:

Sub GetSheets() Path = "Укажите пусть до папки с файлами Excel" Filename = Dir(Path & "*.xls") Do While Filename <> "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Next Sheet Workbooks(Filename).Close Filename = Dir() Loop End Sub

Прошу обратить внимание на две строчки.

1. Path = «Укажите пусть до папки с файлами Excel». Конечно, надпись в кавычках нужно заменить. Например, я заменил на … и вот, что у меня получилось: Path = » D:mrUnrealistDocumentsНовая папка»
2. Filename = Dir(Path & «*.xls»). В кавычках указан формат файла. В Excel их, обычно, два: .xls и .xlsx. Нажмите на файл правой кнопкой мыши и посмотрите его «Свойства». В скобках указан правильный тип файла.несколько таблиц в одну excel

Читайте также:
Как выполнить сортировку в Excel

Этот код подойдет, если нужно соединить все листы в один файл Эксель. Но, если вам необходимо объединить определенные листы некоторых файлов, переходите к следующему способу.

Функция «Переместить/скопировать» поможет объединить несколько листов Excel в один файл

В этом методе мы научимся копировать листы из одного файла Excel в другие.

Все указанные в статье методы работают так же и в Microsoft Office365.

1. Откройте все файлы, из которых вы собираетесь копировать листы, и тот файл (это может быть и новая пустая книга Эксель), в котором будут эти листы собраны.
2. Теперь откройте книгу, из которой будете копировать. Выберите те листы, которые вам нужны. Для множественного выбора держите зажатой клавиши CTRL (для выбора отдельных листов), либо SHIFT (для выбора всех вместе листов).
3. Нажмите по имени листа правой кнопкой мыши и в контекстном меню выберите пункт «Переместить/скопировать».
4. В окне «Переместить или скопировать» выберите из списка «Переместить выбранные листы в книгу» нужную вам книгу. Т.е. ту, где вы собираете все листы вместе. А в списке «Перед листом» укажите место, где эти листы будут вставлены.
   Если вы не желаете, чтобы ваши листы пропали из открытой книги, поставьте галочку «Создать копию».
5. Нажмите на кнопку «ОК» и выбранные листы будут перемещены или скопированы.
6. Повторяйте со второго пункта до тех пор, пока вы не получите должного результата.

На этом все. Подписывайтесь, вступайте в группу вКонтакте или ОК, комментируйте, и не забывайте делиться с другими!

Немного юмора:

Кто у трезвого на уме, тот у пьяного в исходящих

Объединение множества Excel файлов в один

Нужно объединить более сотни .xlsx файлов в один. Файлы .xlxs однородны, 1 строка — шапка, далее данные с одинаковым кол-вом столбцов. Сейчас так:

import os import pandas as pd import glob import xlsxwriter in_3 = 'my_path/concat_excel/*.xlsx' files = glob.glob(in_3) # print(files) df = pd.concat([pd.read_excel(f, header=None) for f in files], ignore_index=True) df.to_excel('final.xlsx', engine='xlsxwriter') with pd.ExcelWriter('final.xlsx') as writer: df.to_excel() 

На выходе ошибка:

File «C:—path—\Python\Python37-32\lib\site-packages\xlrd\book.py», line 616, in biff2_8_load raise XLRDError(«File size is 0 bytes») xlrd.biffh.XLRDError: File size is 0 bytes

Как решить?
Может быть не на Python, или есть варианты проще это сделать?
Отслеживать
51.6k 203 203 золотых знака 65 65 серебряных знаков 250 250 бронзовых знаков
задан 25 мар 2020 в 11:34
405 3 3 серебряных знака 11 11 бронзовых знаков
Скажите, вам ясен текст ошибки?
25 мар 2020 в 11:37

Изначально нет, после ознакомления с вашим решением начинает проясняться. Вероятно, он не мог обработать пустой файл, коим являлся final.xlsx, находящийся в этой директории