Импорт данных из 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? Не могу разобраться. Не для каждой же запрос делать)
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
Наверх