Импорт данных из PDF в Excel через Power Query

Задача переноса данных из таблицы в PDF-файле на лист Microsoft Excel - это всегда "весело". Особенно если у вас нет дорогих программ распознавания типа FineReader или чего-то подобного. Прямое копирование обычно ни к чему хорошему не приводит, т.к. после вставки скопированных данных на лист, они, скорее всего, "слипнутся" в один столбец. Так что их потом придется кропотливо разделять с помощью инструмента Текст по столбцам с вкладки Данные (Data - Text to Columns).

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

Но все не так грустно, на самом деле :)

Если у вас Office 2013 или 2016, то за пару минут без дополнительных программ вполне можно реализовать перенос данных из PDF в Microsoft Excel. А помогут нам в этом Word и Power Query.

Для примера, давайте возьмем вот такой PDF-отчет с кучей текста, формул и таблиц с сайта Европейской Экономической Комиссии:

Исходный PDF

... и попробуем вытащить из него в Excel, скажем первую таблицу:

Нужная таблица в PDF

Поехали!

Шаг 1. Открываем PDF в Word

Почему-то мало кто знает, но начиная с 2013 года Microsoft Word научился открывать и распознавать PDF файлы (даже отсканированные, т.е. без текстового слоя!). Делается это совершенно стандартным образом: открываем Word, жмем Файл - Открыть (File - Open) и уточняем PDF-формат в выпадающем списке в правом нижнем углу окна.

Затем выбираем нужный нам PDF-файл и жмем Открыть (Open). Word сообщает нам, что собирается запустить распознавание этого документа в текст:

Запуск распознавания PDF в Word

Соглашаемся и через несколько секунд увидим наш PDF открытым для редактирования уже в 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-файл:

Выбираем MHTML-файл для импорта в Excel

Обратите внимание, что импорт успешно не завершится, т.к. Power Query ждет от нас XML, а у нас, на самом деле, HTML-формат. Поэтому в следующем появившемся окне нужно будет щелкнуть правой кнопкой мыши по непонятному для Power Query файлу и уточнить его формат:

Уточняем настоящий формат файла

После этого файл будет корректно распознан и мы увидим список всех таблиц, которые в нем есть:

Выбираем таблицу для импорта

Посмотреть содержимое таблиц можно, если щелкать левой кнопкой мыши в белый фон (не в слово Table!) ячеек в столбце Data.

Когда нужная таблица определена, щелкните по зеленому слову Table - и вы "провалитесь" в её содержимое:

Нужная нам таблица

Останется проделать несколько простых действий, чтобы "причесать" ее содержимое, а именно:

  1. удалить ненужные столбцы (правой кнопкой мыши по заголовку столбца - Удалить)
  2. заменить точки на запятые (выделить столбцы, щелкнуть правой - Замена значений)
  3. удалить знаки равно в шапке (выделить столбцы, щелкнуть правой - Замена значений)
  4. удалить верхнюю строку (Главная - Удалить строки - Удаление верхних строк)
  5. удалить пустые строки (Главная - Удалить строки - Удаление пустых строк)
  6. поднять первую строку в шапку таблицы (Главная - Использовать первую строку в качестве заголовков)
  7. отфильтровать лишние данные с помощью фильтра

Когда таблица будет приведена в нормальный вид, ее можно выгрузить на лист командой Закрыть и загрузить (Close & Load) на Главной вкладке. И мы получим вот такую красоту, с которой уже можно работать:

Готовая таблица

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



03.06.2018 16:36:10
Спасибо, Николай! Очень актуальная тема. Возьмем на вооружение.
04.06.2018 08:19:15
Не за что, Никита!
04.06.2018 10:31:22
жалко  я это не увидел неделю назад ,когда монитор разбил от  злости)))

другими словами ,СПАСИБО !!!

и шалом из Iзраиля  
04.06.2018 11:10:46
Николай а можно в power query объеденить несколько "подколонок" с одну большую с общим заголовком
29.06.2018 17:37:24
PowerQuery может соединить данные из колонок в одну под общим заголовком, если же вам нужны несколько колонок под объединенной шапкой, то нет, PQ так не сделает. PowerQuery это инструмент обработки данных - он не заточен на их удобную визуализацию и красивый отчет . Для этого есть те же сводные таблицы - они прекрасно используют результат обработки PQ.
04.06.2018 12:06:40
Круто! Как всегда актуально и просто.
07.06.2018 00:05:11
Николай! Очередное спасибо Вам!
08.06.2018 17:25:31
Николай, спасибо за очередную идею!
Мне часто приходится из pdf в Excel перегонять. Причем на входе таблицы с очень кривой структурой. Использую PDF-Tools, утилита позволяет вывести распознанный слой в txt с одинарным пробелом между текстовыми блоками, или без пробелов, или автоматически попытается поставить оптимальное количество пробелов. При импорте в Excel не забыть поменять разделитель на тот, что у исходника. После импорта вернуть родную запятую.
28.06.2018 17:30:53
Николай, спасибо за полезную информацию!
Попытался переложить из PDF в Excel прайс лист на котором не нужный для анализа печати, подписи, наименование документа и т.п. Делал все в точности с предложенной Вами методой. К сожалению, не получилась. Подозреваю, что печати, подписи и т.п. как раз тому помеха. Или ошибка в чем-то другом?
28.06.2018 21:55:05
Да, скорее всего. Для сложных PDF с графикой Word как замена FineReader не очень :)
04.07.2018 15:57:25
Очень актуальная тема. Попробовал со своим pdf (выписка из росреестра), создал html, но в Excele ничего не получилось, выдал пустую таблицу. Николай, а FineReader насколько стабильно и корректно конвертирует PDF?
05.07.2018 09:11:39
Ну, FineReader делает это однозначно лучше, чем Word, но бывают такие PDF, где даже человек ничего разобрать не сможет :)
А как быть если бы нужно было все таблицы из документа перенести в Excel? Не могу разобраться. Не для каждой же запрос делать)
18.07.2021 15:10:12
Александр, при подгрузке PQ выдает список таблиц и в общем случае их может быть много и они могут быть совершенно разные (все зависит от исходника), поэтому придется каждую просматривать и для подгрузки каждой делать свой запрос в PQ. Вы, конечно, на втором шаге запроса можете развернуть эти все таблицы и в одном запросе со всеми полями из всех таблиц и даже все это выгрузить на лист Excel, но если таблицы разные, то получится "каша". Если же у них одинаковая структура, то, либо можно использовать консолидацию данных из этих таблиц автоматически через PQ, либо запрос сделать руками в том же PQ.
02.10.2018 16:01:39
Ничего не получается в моих случаях
Если смотреть на файл, открытый в любой программе для pdf-файлов, то видим все нормально. И там даже как текст в таблицах, а не как изображение.
Далее пытаемся открыть файл в Word (2016) по инструкции, то там где таблица, - видим символы. Или в виде крякозябров, или в виде прямоугольничков.
Все.
Всякие трансформации дальше не получаются, действия бесполезны.
Query не видит таблиц вообще в таком случае (все действия по инструкции)
02.11.2018 10:10:50
Николай, добрый день!
Спасибо большое за такую полезную статью.
Прошу помочь разобраться с преобразованием файла, при распознавании Power Query, отображается пустая таблица: , в чем может быть причина?

Ссылка на файлы: https://drive.google.com/drive/folders/1EVrqPYy90ovXbGaIyQUa4vJSuYIrnPcM?usp=sharing
07.12.2018 07:31:07
Мария, сделайте конвертацию в excel через онлайн сервис ilovepdf  таблица выходит намного лучше чем через Ворд. Ну а дальше уже на ваше усмотрение.    https://www.ilovepdf.com/ru
27.12.2018 10:05:33
Евгений, спасибо большое! отличный сервис!
29.12.2018 11:38:51
А я хотел просто забрать таблицы в exel  из договора в wordе, с помощью Power Query, не получилось! Договор написан естественно на русском.
Видимо в этом и проблема. очень жаль , было бы фантастически круто! Ну и Офис у меня10..... выходят одни квадратики  
05.09.2019 15:57:26
у меня на русском: получилось две строки из 19...
05.09.2019 15:59:30
Еще вопрос Николаю: не получается удалить знаки "равно" через правую кнопку\заменить.
ТОчки на запятые сдедал, ...заменил.
Знак равно не хочет ни на что менять) в чем проблема? кривой файл или кривые руки?
05.09.2019 16:00:12
эксель 16-й
06.09.2019 11:56:10
Цитата:  "(даже отсканированные, т.е. без текстового слоя!)."
Со сканами не работает. Оставляет картинкой
08.09.2019 09:06:45
На более-менее вменяемом скане - работает, проверено.
06.09.2019 13:34:02
Можно через OneNote
29.08.2020 13:30:41
Есть вопрос:
Power BI Desktop в июле 2020 появился коннектор PDF, работает просто отлично!!!
А можно ли его как-то активитировать в Excel Power Querry или он может есть в более поздних версия Excel? (Я использую Office 365 сборка 1908)

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-pdf
06.04.2021 12:46:46
Данный коннектор появился в О365 начиная с версии 2008, сборка 13127.21348. Кнопку правда не добавили, но сама функция добавлена и отлично работает.
23.07.2021 13:57:27
Ошибка на 1-м шаге.
При попытке открыть этот PDF-файл возникла проблема. В нем превышен максимальный размер страницы, поддерживаемый в MW
20.10.2021 18:43:37
Подскажите пожалуйста как лучше реализовать.
Имеется НЕтабличный пдф.
В нем в определенном порядке содержаться данные: Артикул, восьмизначный код, количество, цена, стоимость, единицы измерения и т.д.
Есть таблица в экселе, в которой в столбик стоят артикулы, в том же порядке. Необходимо вытащить из ПДФ остальную информацию соответствующую каждому артикулу.
01.11.2021 10:50:25
Нетабличный можно открыть только в Word, преобразовать текст в таблицу и перенести её потом в Excel, наверное. Точнее сказать сложно, т.к. не вижу файла.
17.11.2021 17:53:59
Если бы оно преобразовывалось  в таблицу вопросов бы не было. Куда можно файл положить??
09.08.2022 21:56:26
Добрый день!
А как можно из PDF файла вытащить только определённые строки с информацией в таблицу в excel? (парсинг)
25.02.2023 19:09:08
Николай, здравствуйте.
А почему у меня в Excel 2019 на панели Данные -  "Получить данные" - Из Файла  -  нет опции выбора pdf файла?
Я работаю на Windows.

C чем это может быть связано?

Спасибо
25.04.2023 11:06:55
Николай здравствуйте.
Подскажите, как быть если файлов PDF несколько, каждый переводить в ворд и загружать по отдельности - затратно по времени. Может есть какая-то групповая обработка?
27.07.2023 17:12:22
Добрый день!
Подскажите, пожалуйста. У меня стоит задача в автоматическом режиме получать данные из PDF в Excel.
Из одного файла все получается хорошо.
Подскажите, пожалуйста, как подключить папку, чтобы сразу много файлов обрабатывалось и вносилось в одну таблицу.
10.02.2024 10:30:09
Это
просто фантастика! Спасибо огромнейшее! 🌹
Наверх