Список всех файлов в папке в Excel с помощью Power Query - Советы по Excel

Содержание

Смотреть видео

Сегодняшний вопрос: Марсии нужно получить список всех PDF-файлов налоговых накладных из папки в электронную таблицу Excel. Это легко сделать, если вы используете Excel 2016 на ПК с Windows с помощью новых инструментов «Получить и преобразовать данные».

Если у вас есть Excel 2010 для Windows или Excel 2013 для Windows, вам нужно будет загрузить бесплатную надстройку Power Query от Microsoft. Перейдите в свою любимую поисковую систему и введите «Загрузить Power Query», чтобы найти текущую ссылку. (Microsoft любит менять URL-адреса каждый квартал, а мой замечательный веб-парень ненавидит, когда наши ссылки устарели, поэтому я даже не собираюсь пытаться помещать здесь ссылку.)

Видео ниже покажет вам полные шаги, но вот обзор:

Начните с пустого листа
Данные, получить данные, из файла, из папки
Перейдите в папку
Нажмите Edit вместо Load
Откройте раскрывающийся список фильтров для типа файла и удалите все, что не является PDF.
Откройте фильтр в папке и удалите все подпапки с мусором.
Оставить только имя файла и папку - щелкните правой кнопкой мыши заголовок каждого столбца и выберите Удалить.
Перетащите заголовок папки влево от заголовка файла. Это позволяет слиянию работать.
Выберите оба столбца. Щелкните один заголовок. Shift + Щелкните другой заголовок.
Выберите «Добавить столбец», «Объединить столбцы», введите новое имя для столбца. Щелкните ОК.
Щелкните правой кнопкой мыши заголовок нового столбца и выберите Удалить другие столбцы.
Домой, закрыть и загрузить
Замечательная часть … вы можете обновить запрос позже. Щелкните значок «Обновить» на панели «Запросы и подключения».

Несмотря на то, что Power Query невероятно мощен, это одна из моих любимых задач. Я часто хочу запустить макрос VBA для каждого файла в папке. Получение списка всех PDF-файлов в папке - хорошая отправная точка.

Смотреть видео

Стенограмма видео

Изучите Excel из подкаста, выпуск 2181 - Список файлов папок в Excel!

Привет, добро пожаловать обратно в сеть, я Билл Джелен. Сегодняшний вопрос: у кого-то есть список PDF-файлов налоговых накладных в папке, и им нужно получить список всех этих имен файлов в Excel. Хорошо, и один из способов сделать это - ввести их все или скопировать и вставить из проводника Windows, но есть отличный инструмент, который может решить эту проблему. И мой первый вопрос был: «Ну какая у вас версия Excel?» Потому что, если у вас есть Excel 2016, у них будет эта потрясающая новая функция под названием «Получение и преобразование данных»! Теперь, в Office 365, он находится слева, я думаю, что в исходной версии Excel 2016 он был в третьей группе, хорошо, поэтому просто ищите Get & Transform. Если вы работаете в Excel 2010 или Excel 2013 для Windows, вы можете загрузить Power Query, и у вас будет собственная вкладка с именно этим материалом.

Теперь давайте быстро взглянем на эту папку, хорошо, я только что создал здесь поддельную папку с некоторыми поддельными данными. Вы увидите, что в этой папке и PDF-файлы есть файлы Excel, мне нужны только PDF-файлы, а также есть несколько подпапок, мне не нужны эти PDF-файлы, мне нужны только PDF-файлы в основной папке. Итак, C: Budgets, я скопирую это, а затем вернусь сюда, в Excel, и мы скажем, что хотим получить данные из файла, из всей папки, вот так, а затем введите путь к папке или используйте кнопку «Обзор». И когда вы получите этот первый экран, вы определенно захотите изменить, и теперь мы находимся в редакторе Power Query.

Хорошо, моя цель здесь - мне не нужен контент, поэтому я собираюсь щелкнуть правой кнопкой мыши и сказать «Удалить этот столбец». Вот мой список файлов, мне нужны только PDF-файлы, поэтому, если есть что-то, кроме PDF, мне нужны только PDF-файлы, нажмите OK, я вижу, что это только файлы PDF. А, а затем посмотрите сюда, видите, теперь они втягивают вещи только из исходной папки и из папки «Мусор», поэтому я открываю это и снимаю отметки со всего, что не является исходной папкой. Хорошо, теперь у меня есть хороший небольшой список, и этот список, знаете, что, 9 записей, но в реальной жизни я уверен, что у них, наверное, есть, знаете, десятки или сотни из них. Хорошо, сейчас мне больше ничего не нужно, поэтому я могу щелкнуть правой кнопкой мыши и удалить эти столбцы.

Хорошо, теперь мне действительно нужно указать путь к папке и имя файла вместе. Хорошо, я собираюсь взять FolderPath, перетащить его влево и бросить туда, а затем волшебный шаг здесь: в обычном Excel нам нужно будет выполнить конкатенацию для этого, но я собираюсь сделать следующее: Объединяю столбцы. Итак, я собираюсь добавить столбец и выбрать объединить столбцы, разделитель будет None, новый столбец будет называться FileName, и нажмите OK, хорошо, поэтому у нас есть имя папки, косая черта и имя файла. , как это. На самом деле это единственное, что нам нужно, поэтому я собираюсь щелкнуть правой кнопкой мыши и сказать «Удалить другие столбцы», а затем, наконец, «Домой», «Закрыть и загрузить», и мы получим новый лист с нашими данными. Хорошо, теперь он представлен в виде таблицы, поэтому я просто скопирую это, Ctrl + C,а затем подойдите сюда, туда, где мне действительно нужны данные, и вставьте специальные значения, нажмите OK. Теперь это больше не таблица, это просто мои чистые данные, вот так, и вот действительно прекрасная вещь в этом.

Итак, мы настроили это один раз, и ничего себе, это заняло менее 3 минут, но давайте вернемся в эту папку с бюджетами и переместим кое-что. Давайте возьмем одну из этих записей мусора, и мы скопируем ее в основную папку, Ctrl + V, хорошо, теперь здесь больше вещей, есть 10 файлов PDF вместо 9. Если я подойду сюда, где находится запрос, и в правой части экрана, в разделе «Запросы и подключения», возможно, вам придется сделать это шире, я уже сделал свой шире, вы увидите наши бюджеты с загруженными 9 строками. Я собираюсь щелкнуть здесь маленький значок «Обновить», и очень быстро теперь в Бюджетах загружено 10 строк. Итак, он собирает новые записи, вы настраиваете это один раз, а затем вы сможете просто обновить, чтобы получить новые данные.

Ну, это тот момент в подкасте, где я обычно прошу вас купить мою книгу, но вместо этого сегодня я собираюсь попросить вас купить эту книгу Кена Пулса и Мигеля Эскобара «М для (ДАННЫЕ) ОБЕЗЬЯНЫ». УДИВИТЕЛЬНАЯ книга, которая научит вас использовать Power Query или Get & Transform Data, все, что я узнал о Power Query, я узнал из этой книги.

Хорошо, завершаем этот эпизод: наша цель - как импортировать список имен файлов в Excel, если у вас есть Excel 2016, вы можете использовать новую функцию «Получить и преобразовать данные». Если у вас нет 2016 года, но у вас есть настоящая версия Excel, работающая под Windows, вы можете загрузить бесплатную надстройку Power Query для Excel 2010 или Excel 2013. Она не будет работать на вашем телефоне Android или ваш iPad, или ваш iPhone, или Surface RT, или ваш Mac, верно, это только для версий Excel для Windows. Итак, мы собираемся начать с пустого листа, «Данные», «Получить данные», «Из файла», «Из папки», ввести имя папки или «Обзор», не забудьте нажать «Изменить» вместо «Загрузить». Затем в фильтре отфильтруйте тип файла, чтобы избавиться от всего, кроме PDF, отфильтруйте имя папки, чтобы избавиться от всех подпапок с мусором. Оставьте только имя файла и папку,поэтому щелкните их правой кнопкой мыши и произнесите «Удалить столбец», затем перетащите заголовок папки влево от файла, чтобы слияние сработало. Выберите оба столбца, затем на вкладке «Добавить столбец» выберите «Объединить столбцы», введите новое имя, нажмите «ОК», щелкните правой кнопкой мыши этот новый столбец и выберите «Удалить другие столбцы», «Домой», «Закрыть и загрузить», и вы получите свой список. Самое замечательное, что вы можете обновить запрос позже, используя этот значок «Обновить» в разделе «Запросы и подключения».вы можете обновить запрос позже, используя этот значок «Обновить» в разделе «Запросы и подключения».вы можете обновить запрос позже, используя этот значок «Обновить» в разделе «Запросы и подключения».

Что ж, я хочу поблагодарить вас за то, что заглянули, увидимся в следующий раз на другом сетевом трансляции от!