Импорт данных из PDF в Excel через Power Query
Задача переноса данных из таблицы в PDF-файле на лист Microsoft Excel - это всегда "весело". Особенно если у вас нет дорогих программ распознавания типа FineReader или чего-то подобного. Прямое копирование обычно ни к чему хорошему не приводит, т.к. после вставки скопированных данных на лист, они, скорее всего, "слипнутся" в один столбец. Так что их потом придется кропотливо разделять с помощью инструмента Текст по столбцам с вкладки Данные (Data - Text to Columns).
И само-собой, копирование возможно только для тех PDF-файлов, где есть текстовый слой, т.е. с только что отсканированным с бумаги в PDF документом это не сработает в принципе.
Но все не так грустно, на самом деле :)
Если у вас Office 2013 или 2016, то за пару минут без дополнительных программ вполне можно реализовать перенос данных из PDF в Microsoft Excel. А помогут нам в этом Word и Power Query.
Для примера, давайте возьмем вот такой PDF-отчет с кучей текста, формул и таблиц с сайта Европейской Экономической Комиссии:
... и попробуем вытащить из него в Excel, скажем первую таблицу:
Поехали!
Шаг 1. Открываем PDF в Word
Почему-то мало кто знает, но начиная с 2013 года Microsoft Word научился открывать и распознавать PDF файлы (даже отсканированные, т.е. без текстового слоя!). Делается это совершенно стандартным образом: открываем Word, жмем Файл - Открыть (File - Open) и уточняем PDF-формат в выпадающем списке в правом нижнем углу окна.
Затем выбираем нужный нам PDF-файл и жмем Открыть (Open). Word сообщает нам, что собирается запустить распознавание этого документа в текст:
Соглашаемся и через несколько секунд увидим наш PDF открытым для редактирования уже в Word:
Само-собой, у документа частично слетит дизайн, стили, шрифты, колонтитулы и т.п., но для нас это не важно - нам нужны только данные из таблиц. В принципе, на этом этапе уже возникает соблазн дальше просто скопировать таблицу из распознанного документа в Word и просто вставить ее в Excel. Иногда это срабатывает, но чаще приводит ко всевозможным искажениям данных - например числа могут превратиться в даты или остаться текстом, как в нашем случае, т.к. в PDF используется не российские разделители:
Так что давайте не будем срезать углы, а сделаем все чуть сложнее, но правильно.
Этап 2. Сохраняем документ как веб-страницу
Чтобы потом загрузить полученные данные в Excel (через Power Query), наш документ в Word нужно сохранить в формате веб-страницы - этот формат является, в данном случае, неким общим знаменателем между Word'ом и Excel'ем.
Для этого идем в меню Файл - Сохранить как (File - Save As) или жмем клавишу F12 на клавиатуре и в открывшемся окне выбираем тип файла Веб-страница в одном файле (Webpage - Single file):
После сохранения должен получиться файл с расширением mhtml (если у вас в Проводнике видны расширения файлов).
Этап 3. Загружаем файл в Excel через Power Query
Можно открыть созданный MHTML-файл в Excel напрямую, но тогда мы получим, во-первых сразу все содержимое PDF вместе текстом и кучей ненужных таблиц, а, во-вторых, опять потеряем данные из-за неправильных разделителей. Поэтому импорт в Excel мы будем делать через надстройку Power Query. Это совершенно бесплатная надстройка, с помощью которой можно загружать в Excel данные практически из любых источников (файлов, папок, баз данных, ERP-систем) и всячески затем полученные данные трансформировать, придавая им нужную форму.
Если у вас Excel 2010-2013, то скачать Power Query можно с официального сайта Microsoft - после установки у вас появится вкладка Power Query. Если у вас Excel 2016 или новее, то качать ничего не нужно - весь функционал уже встроен в Excel по-умолчанию и находится на вкладке Данные (Data) в группе Загрузить и преобразовать (Get & Transform).
Так что идем либо на вкладку Данные, либо на вкладку Power Query и выбираем команду Получить данные или Создать запрос - Из файла - Из XML. Чтобы были видны не только XML-файлы - меняем в выпадающем списке в правом нижнем углу окна фильтры на Все файлы (All files) и указываем наш MHTML-файл:
Обратите внимание, что импорт успешно не завершится, т.к. Power Query ждет от нас XML, а у нас, на самом деле, HTML-формат. Поэтому в следующем появившемся окне нужно будет щелкнуть правой кнопкой мыши по непонятному для Power Query файлу и уточнить его формат:
После этого файл будет корректно распознан и мы увидим список всех таблиц, которые в нем есть:
Посмотреть содержимое таблиц можно, если щелкать левой кнопкой мыши в белый фон (не в слово Table!) ячеек в столбце Data.
Когда нужная таблица определена, щелкните по зеленому слову Table - и вы "провалитесь" в её содержимое:
Останется проделать несколько простых действий, чтобы "причесать" ее содержимое, а именно:
- удалить ненужные столбцы (правой кнопкой мыши по заголовку столбца - Удалить)
- заменить точки на запятые (выделить столбцы, щелкнуть правой - Замена значений)
- удалить знаки равно в шапке (выделить столбцы, щелкнуть правой - Замена значений)
- удалить верхнюю строку (Главная - Удалить строки - Удаление верхних строк)
- удалить пустые строки (Главная - Удалить строки - Удаление пустых строк)
- поднять первую строку в шапку таблицы (Главная - Использовать первую строку в качестве заголовков)
- отфильтровать лишние данные с помощью фильтра
Когда таблица будет приведена в нормальный вид, ее можно выгрузить на лист командой Закрыть и загрузить (Close & Load) на Главной вкладке. И мы получим вот такую красоту, с которой уже можно работать:
другими словами ,СПАСИБО !!!
и шалом из Iзраиля
Мне часто приходится из pdf в Excel перегонять. Причем на входе таблицы с очень кривой структурой. Использую , утилита позволяет вывести распознанный слой в txt с одинарным пробелом между текстовыми блоками, или без пробелов, или автоматически попытается поставить оптимальное количество пробелов. При импорте в Excel не забыть поменять разделитель на тот, что у исходника. После импорта вернуть родную запятую.
Попытался переложить из PDF в Excel прайс лист на котором не нужный для анализа печати, подписи, наименование документа и т.п. Делал все в точности с предложенной Вами методой. К сожалению, не получилась. Подозреваю, что печати, подписи и т.п. как раз тому помеха. Или ошибка в чем-то другом?
Если смотреть на файл, открытый в любой программе для pdf-файлов, то видим все нормально. И там даже как текст в таблицах, а не как изображение.
Далее пытаемся открыть файл в Word (2016) по инструкции, то там где таблица, - видим символы. Или в виде крякозябров, или в виде прямоугольничков.
Все.
Всякие трансформации дальше не получаются, действия бесполезны.
Query не видит таблиц вообще в таком случае (все действия по инструкции)
Спасибо большое за такую полезную статью.
Прошу помочь разобраться с преобразованием файла, при распознавании Power Query, отображается пустая таблица:
Ссылка на файлы:
Видимо в этом и проблема. очень жаль , было бы фантастически круто! Ну и Офис у меня10..... выходят одни квадратики
ТОчки на запятые сдедал, ...заменил.
Знак равно не хочет ни на что менять) в чем проблема? кривой файл или кривые руки?
Со сканами не работает. Оставляет картинкой
Power BI Desktop в июле 2020 появился коннектор PDF, работает просто отлично!!!
А можно ли его как-то активитировать в Excel Power Querry или он может есть в более поздних версия Excel? (Я использую Office 365 сборка 1908)
При попытке открыть этот PDF-файл возникла проблема. В нем превышен максимальный размер страницы, поддерживаемый в MW
Имеется НЕтабличный пдф.
В нем в определенном порядке содержаться данные: Артикул, восьмизначный код, количество, цена, стоимость, единицы измерения и т.д.
Есть таблица в экселе, в которой в столбик стоят артикулы, в том же порядке. Необходимо вытащить из ПДФ остальную информацию соответствующую каждому артикулу.
А как можно из PDF файла вытащить только определённые строки с информацией в таблицу в excel? (парсинг)
А почему у меня в Excel 2019 на панели Данные - "Получить данные" - Из Файла - нет опции выбора pdf файла?
Я работаю на Windows.
C чем это может быть связано?
Спасибо
Подскажите, как быть если файлов PDF несколько, каждый переводить в ворд и загружать по отдельности - затратно по времени. Может есть какая-то групповая обработка?
Подскажите, пожалуйста. У меня стоит задача в автоматическом режиме получать данные из PDF в Excel.
Из одного файла все получается хорошо.
Подскажите, пожалуйста, как подключить папку, чтобы сразу много файлов обрабатывалось и вносилось в одну таблицу.
просто фантастика! Спасибо огромнейшее! 🌹