зміст
Завдання перенесення даних з електронної таблиці у файлі PDF на аркуш Microsoft Excel завжди «веселе». Особливо, якщо у вас немає дорогого програмного забезпечення для розпізнавання, як-от FineReader чи щось подібне. Пряме копіювання зазвичай ні до чого доброго не призводить, т.к. після вставки скопійованих даних на аркуш вони, швидше за все, «злипнуться» в один стовпець. Тож потім їх доведеться ретельно відокремити за допомогою інструменту Текст по колонках із вкладки дані (Дані — текст у стовпці).
І звичайно, копіювати можна тільки ті PDF-файли, де є текстовий шар, тобто з тільки що відсканованим документом з паперу в PDF це не спрацює в принципі.
Але це не так сумно, насправді 🙂
Якщо у вас Office 2013 або 2016, то за пару хвилин, без додаткових програм, цілком можливо перенести дані з PDF в Microsoft Excel. А допоможуть нам у цьому Word і Power Query.
Для прикладу візьмемо цей PDF-звіт із купою тексту, формул і таблиць із веб-сайту Європейської економічної комісії:
… і спробуйте отримати з неї в Excel, скажімо, першу таблицю:
Ходімо!
Крок 1. Відкрийте PDF у Word
Чомусь мало хто знає, але з 2013 року Microsoft Word навчився відкривати і розпізнавати PDF-файли (навіть відскановані, тобто без текстового шару!). Робиться це абсолютно стандартним способом: відкриваємо Word, натискаємо Файл – Відкрити (Файл — Відкрити) і вкажіть формат PDF у випадаючому списку в нижньому правому куті вікна.
Потім вибираємо потрібний нам файл PDF і натискаємо відкритий (Відчинено). Word повідомляє нам, що він збирається запустити розпізнавання тексту в цьому документі:
Ми погоджуємось і через кілька секунд ми побачимо наш PDF відкритий для редагування вже в Word:
Звичайно, дизайн, стилі, шрифти, колонтитули і т. д. частково злетять з документа, але для нас це не важливо - нам потрібні тільки дані з таблиць. В принципі, на цьому етапі вже є спокуса просто скопіювати таблицю з розпізнаного документа в Word і просто вставити в Excel. Іноді це спрацьовує, але частіше це призводить до всіляких спотворень даних – наприклад, цифри можуть перетворитися на дати або залишитися текстом, як у нашому випадку, т.к. PDF використовує нероздільники:
Тому давайте не будемо зрізати кути, а зробимо все трохи складніше, але правильно.
Крок 2. Збережіть документ як веб-сторінку
Щоб потім завантажити отримані дані в Excel (через Power Query), наш документ у Word необхідно зберегти у форматі веб-сторінки – цей формат, у даному випадку, є свого роду спільним знаменником Word і Excel.
Для цього зайдіть в меню Файл – Зберегти як (Файл — Зберегти як) або натисніть клавішу F12 на клавіатурі та у вікні, що відкриється, вибрати тип файлу Веб-сторінка одним файлом (Веб-сторінка — один файл):
Після збереження ви повинні отримати файл із розширенням mhtml (якщо ви бачите розширення файлів у Провіднику).
Етап 3. Завантаження файлу в Excel через Power Query
Ви можете відкрити створений файл MHTML безпосередньо в Excel, але тоді ми отримаємо, по-перше, весь вміст PDF-файлу відразу, разом з текстом і купою непотрібних таблиць, а, по-друге, ми знову втратимо дані через некоректність сепаратори. Тому ми зробимо імпорт до Excel через надбудову Power Query. Це абсолютно безкоштовне доповнення, за допомогою якого ви можете завантажити дані в Excel практично з будь-якого джерела (файлів, папок, баз даних, ERP-систем), а потім всіляко трансформувати отримані дані, надаючи їм потрібну форму.
Якщо у вас Excel 2010-2013, то ви можете завантажити Power Query з офіційного сайту Microsoft – після встановлення ви побачите вкладку PowerQuery. Якщо у вас Excel 2016 або новішої версії, то вам не потрібно нічого завантажувати – весь функціонал вже вбудований в Excel за замовчуванням і знаходиться на вкладці дані (Дата) В групі Завантажити та конвертувати (Отримати та трансформувати).
Тож переходимо або до вкладки дані, або на вкладці PowerQuery і вибрати команду Щоб отримати дані or Створити запит – З файлу – З XML. Щоб зробити видимими не лише XML-файли, змініть фільтри у випадаючому списку в нижньому правому куті вікна на Всі файли (Всі файли) і вкажіть наш файл MHTML:
Зверніть увагу, що імпортування не завершиться успішно, тому що. Power Query очікує від нас XML, але насправді ми маємо формат HTML. Тому в наступному вікні потрібно клацнути правою кнопкою миші на незрозумілому для Power Query файлі і вказати його формат:
Після цього файл буде правильно розпізнаний і ми побачимо список усіх таблиць, які він містить:
Переглянути вміст таблиць можна, клацнувши лівою кнопкою миші на білому тлі (не в слові Таблиця!) комірок стовпця Дані.
Коли потрібну таблицю визначено, клацніть на зеленому слові таблиця – і ви «провалюєтесь» у його вміст:
Залишилося зробити кілька простих кроків, щоб «причесати» його вміст, а саме:
- видаліть непотрібні стовпці (клацніть правою кнопкою миші на заголовку стовпця – Видалення)
- замініть крапки комами (виділіть стовпці, клацніть правою кнопкою миші – Заміна значень)
- видалити знаки рівності в заголовку (виділіть стовпці, клацніть правою кнопкою миші – Заміна значень)
- прибрати верхній рядок (Головна – Видалити рядки – Видалити верхні рядки)
- видалити порожні рядки (Домашня сторінка – Видалити рядки – Видалити порожні рядки)
- підняти перший рядок до заголовка таблиці (Домашня сторінка – використовуйте перший рядок як заголовки)
- відфільтрувати непотрібні дані за допомогою фільтра
Коли таблиця приведена до нормального вигляду, її можна вивантажити на аркуш за допомогою команди закрити і завантажити (Закрити та завантажити) on Головний вкладка. І вийде така краса, з якою вже можна працювати:
- Перетворення стовпця на таблицю за допомогою Power Query
- Розбиття закріпленого тексту на стовпці