Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Power Query. Загрузка данных из активного листа excel-файла
 
#1
24.07.2017 11:49:38
Доброго дня.

Есть задача: подгружать все файлы экслель из папки и обрабатывать их по определенным условиям. Все файлы однотипные в плане представленности данных, но при этом названия у вкладок не всегда одинаковые. Обрабатывать нужно ту вкладку, на которой файл был сохранен (которая была активной в момент сохранения).
Вопрос: Как задать в запросе выбор данных именно из активной вкладки?

Сейчас запрос на обработку выглядит так:
Код
let
    Источник = Folder.Files("C:\Users\SN\Desktop\Folder1"),
    #"Вызвать настраиваемую функцию1" = Table.AddColumn(Источник, "Преобразовать файл из Folder1", each #"Преобразовать файл из Folder1"([Content])),
    #"Переименованные столбцы1" = Table.RenameColumns(#"Вызвать настраиваемую функцию1", {"Name", "Source.Name"}),
    #"Другие удаленные столбцы1" = Table.SelectColumns(#"Переименованные столбцы1", {"Source.Name", "Преобразовать файл из Folder1"}),
    #"Столбец расширенной таблицы1" = Table.ExpandTableColumn(#"Другие удаленные столбцы1", "Преобразовать файл из Folder1", Table.ColumnNames(#"Преобразовать файл из Folder1"(#"Пример файла"))),
    #"Измененный тип" = Table.TransformColumnTypes(#"Столбец расширенной таблицы1",{{"Source.Name", type text}, {"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}})
in
    #"Измененный тип"
 
 
 
#2
24.07.2017 12:28:32
Доброе время суток.
Цитата
NattaN написал:
Как задать в запросе выбор данных именно из активной вкладки?
Никак. Таблица, получаемая функцией Excel.Workbook, не несёт никакой информации об активном рабочем листе книги. Это и в VBA не тривиальная задача - найти активный рабочий лист. Для версий файлов новых форматов xlsx, xlsm потребуется разбор данных xml файлов содержимого. Лучше реорганизуйте работу. Либо необходим некоторый идентификатор содержимого листа данных, по которому можно определить, что требуемый лист содержит необходимые данные.
 
 
 
#3
24.07.2017 12:47:45
Цитата
Андрей VG написал:
Лучше реорганизуйте работу.
Это мечта, над которой идет работа. Но не все поставщики информации достаточно дисциплинированны, чтобы присылать файлы в том виде, в каком они удобны для обработки.

Цитата
Андрей VG написал:
Либо необходим некоторый идентификатор содержимого листа данных, по которому можно определить, что требуемый лист содержит необходимые данные.
Спасибо за совет. Если есть возможность, подскажите, пожалуйста, ресурс, где про этот метод почитать можно?


А как вариант, есть ли возможность обозначить, что из подгружаемого файла нужно брать данные, к примеру, только из первого листа вне зависимости от того, сколько их в самом файле?
 
 
 
#4
24.07.2017 13:29:28
Цитата
NattaN написал:
олько из первого листа вне зависимости от того, сколько их в самом файле?
На картинке - данные о содержимом книги. Вы можете по этому содержимому определить, какой лист первый? В данном случае, да, по в верхней строке именно первый лист, так как они отображаются при её открытии. Но в документации по Excel.Workbook - нигде не гарантируется, что имена листов будут выводиться в порядке размещения. Попробуйте на ваших файлах, подставляя реальный путь и имя файла в функцию проверить - так ли это
Код
Excel.Workbook(File.Contents("d:\path\filename.xlsx")){0}[Name]
Если, да, то используйте.

Под идентификатором я подразумевал следующее. Для листа добавляем в имя листа, например в конец фарзу (этот) и фильтруем таблицу на картинке функцией Text.Contains([Name], "(этот)").
Аналогично по содержимому листа. Допустим пишем в A1 "(этот"). Тогда фильтруем таблицу на картинке по [Data]{0}[Column1] = "(этот)"
Соответственно, потом Data трансформируем, пропуска допустим три строки до заголовка, использовать первую строку в качестве заголовков, указание типов данных.
Как-то так.
 
 
 
#5
24.07.2017 13:49:11
Андрей VG, благодарю за идеи. Буду пробовать.  
 
 
 
#6
24.07.2017 14:15:50
Цитата
Андрей VG написал:
Но в документации по Excel.Workbook - нигде не гарантируется, что имена листов будут выводиться в порядке размещения.
Я пока определил такой порядок отображения:
  1. сначала листы по индексу (т.е. в порядке отображения)
  2. затем таблицы на листах в обратном порядке индекса ListObjects (в порядке индекса листов - сначала таблицы 1-го листа, затем 2-го и т.д.).
  3. именованные диапазоны - по алфавиту или по индексу (индекс имени почему-то равен алфавитному порядку имен).
Лучше, конечно, перепроверить, но пока других историй не встречал
F1 творит чудеса
 
 
 
#7
24.07.2017 16:08:07
Андрей VG, взялась за второй вариант. По таблицам выводятся только колонки "Name" и "Data". "Item", "Kind", "Hidden" - как-то особо нужно задавать, чтобы и они отображались? Или этот вопрос в отдельной теме нужно задавать?
Изменено: NattaN - 24.07.2017 16:26:28
 
 
 
#8
24.07.2017 17:03:20
Андрей VG написал:
Цитата
Аналогично по содержимому листа. Допустим пишем в A1 "(этот"). Тогда фильтруем таблицу на картинке по [Data]{0}[Column1] = "(этот)"
Ну, если ТС утверждает, что структура файлов одинаковая (хотя дисциплина и хромает) то значит, он  знает в какой по счету строке каждого файла находится строка с заголовками, ему  остается только искать в листах по строкам заголовки, и сравнивать их с каким-то эталонным заголовком. Тогда не придется обучать сторонних людей отправляющих информацию писать в названия листа или в его содержимое слово "этот".

З.Ы. Попробовал сам - получилось. Запрос по вытаскивал таблицы с разноименных листов, на которых совпадали шапки отчетов. Сравнивал две записи: Строку с заголовком из листа и строку из умной таблицы с эталоном, которую слепил сам.
Соответственно, косяк может быть только если кто-то поломает названия заголовков. Если в одном файле будет найдено два листа с корректными заголовками, то я сортирую список и оставляю первый лист в списке.
ТС, вот код запроса-примера, который я слепил в конструкторе на коленке. Этот код нужно вставлять в запрос, который генерит сам Power Query, и на примере которого он для вас собирает файлы. В третьей строке он ссылается на таблицу "Заголовок_Эталон", такую таблицу вам нужно будет создать самостоятельно на любом листе файла.
Обращаю ваше внимание, что при создании таблицы "Заголовок_Эталон" наименования эталонных заголовков должны быть не в заголовках, а в первой строке!
И спасибо за пинок, давно уже сам хотел эту проблему разрешить, но как-то было в лом, а тут чегой-то прям зацепило.
Код
let
    Источник = Excel.Workbook(#"Параметр файла примера1", null, true),
    Шапка_из_листа = Table.AddColumn(Источник, "Пользовательская", each [Data]{0}),
    Шапка_из_эталона = Table.AddColumn(Шапка_из_листа, "Пользовательская.1", each Excel.CurrentWorkbook(){[Name="Заголовок_Эталон"]}[Content]{0}),
    Сравниваем_шапки = Table.AddColumn(Шапка_из_эталона, "Пользовательская.2", each [Пользовательская]=[Пользовательская.1]),
    Убираю_ошибки = Table.ReplaceErrorValues(Сравниваем_шапки, {{"Пользовательская.2", false}}),
    Оставляю_только_листы_с_правильным_заголовками = Table.SelectRows(Убираю_ошибки, each ([Пользовательская.2] = true)),
    Сортированные_строки = Table.Sort(Оставляю_только_листы_с_правильным_заголовками,{{"Name", Order.Ascending}}),
    Оставляю_только_первую_строку = Table.FirstN(Сортированные_строки,1),
    Удаляем_столбец_Item = Table.RemoveColumns(Оставляю_только_первую_строку,{"Item"}),
    Создаем_столбец_Item_с_правильным_названием_листа = Table.AddColumn(Удаляем_столбец_Item, "Item", each "Лист1"),
    Достаем_содержимое_листа = Создаем_столбец_Item_с_правильным_названием_листа{[Item="Лист1",Kind="Sheet"]}[Data],
    Повышенные_заголовки = Table.PromoteHeaders(Достаем_содержимое_листа, [PromoteAllScalars=true])
in
    Повышенные_заголовки
Изменено: PooHkrd - 24.07.2017 18:45:34
Вот горшок пустой, он предмет простой...
 
 
 
#9
25.07.2017 09:04:10
PooHkrd, спасибо вам огромное!  
 
 
 
#10
25.07.2017 09:24:36
Доброе время суток
Цитата
PooHkrd написал:
Тогда не придется обучать сторонних людей отправляющих информацию писать в названия листа или в его содержимое слово "этот".
Не принципиально - структуру входа ТС не предоставил. В качестве "этот" вполне может, как вы реализовали выступать некоторый заголовок эталон. Единственный вопрос, если в книге более одного листа с одинаковыми заголовками, что должен брать PQ?
 
 
 
#11
25.07.2017 09:25:49
NattaN, предлагаю поменять название темы, ибо помочь вам получилось, но не совсем так как в названии темы. Вы можете попросить об этом модераторов.
Например, Power Query. Объединение данных из папки по разным наименованиям листов. Или что-то в таком роде.
Так остальным форумчанам будет проще находить решение аналогичной проблемы.
Изменено: PooHkrd - 25.07.2017 09:27:43
Вот горшок пустой, он предмет простой...
 
 
 
#12
25.07.2017 09:32:57
Андрей VG,у меня данный вопрос решается топорно - сортируем листы как нравится и оставляем только верхнюю строку. There can be only one! :)
Здесь, универсального решения быть точно не может, мало ли что там в названиях листов.
В любом случае, судя по отзыву, человеку мы помогли. Вам спасибо за идею и примеры обращения к содержимому листов, без них я бы так быстро запрос не сляпал.
Вот горшок пустой, он предмет простой...
 
 
 
#13
25.07.2017 10:47:17
Цитата
PooHkrd написал:
предлагаю поменять название темы
Резонно. Запрос на смену названия отправила.
 
 
 
#14
25.07.2017 11:58:32
Если есть возможность правки файлов-источников данных, то можно реализвать процедуру на VBA, которая при закрытии книги будет добавлять определенный префикс в название нужного листа и контролировать отсутствие данного префикса в остальных листах. Ну а далее все просто.
 
 
 
#15
25.07.2017 14:45:48
IlyaK, увы, вариант - не вариант. Файлы регулярно формируются новые. К каждому прикручивать процедуру - много сложнее, чем с предобработке по шаблону их собирать.
 
 
 
Страницы: 1
Наверх