وارد کردن داده ها از PDF به Excel از طریق Power Query

وظیفه انتقال داده ها از یک صفحه گسترده در یک فایل PDF به یک صفحه مایکروسافت اکسل همیشه "سرگرم کننده" است. به خصوص اگر نرم افزار تشخیص گران قیمتی مانند FineReader یا چیزی شبیه به آن ندارید. کپی مستقیم معمولاً به هیچ چیز خوبی منجر نمی شود، زیرا. پس از چسباندن داده‌های کپی شده بر روی صفحه، به احتمال زیاد در یک ستون به هم می‌چسبند. بنابراین باید با استفاده از یک ابزار به دقت جدا شوند متن به ستون از زبانه داده ها (داده ها - متن به ستون).

و البته کپی فقط برای آن دسته از فایل های پی دی اف امکان پذیر است که لایه متنی وجود داشته باشد، یعنی با سندی که به تازگی از کاغذ به پی دی اف اسکن شده است، در اصل کار نمی کند.

ولی خیلی هم غمگین نیست، واقعا 🙂

اگر Office 2013 یا 2016 دارید، پس از چند دقیقه، بدون برنامه های اضافی، انتقال داده ها از PDF به Microsoft Excel کاملاً امکان پذیر است. و Word و Power Query در این امر به ما کمک خواهند کرد.

به عنوان مثال، بیایید این گزارش PDF را با مجموعه ای از متن، فرمول ها و جداول از وب سایت کمیسیون اقتصادی اروپا بگیریم:

... و سعی کنید آن را در اکسل بیرون بکشید، جدول اول را بگویید:

بیا بریم!

مرحله 1. PDF را در Word باز کنید

بنا به دلایلی، تعداد کمی از مردم می دانند، اما از سال 2013 مایکروسافت ورد یاد گرفته است که فایل های PDF را باز و تشخیص دهد (حتی اسکن شده، یعنی بدون لایه متن!). این کار به روشی کاملا استاندارد انجام می شود: Word را باز کنید، کلیک کنید فایل - باز کردن (پرونده - باز کردن) و فرمت PDF را در لیست کشویی در گوشه سمت راست پایین پنجره مشخص کنید.

سپس فایل PDF مورد نیاز خود را انتخاب کرده و کلیک کنید باز کن (باز کن). Word به ما می گوید که OCR را روی این سند به متن اجرا می کند:

ما موافقت می کنیم و در عرض چند ثانیه PDF خود را برای ویرایش در Word باز می کنیم:

البته، طراحی، سبک‌ها، فونت‌ها، سرصفحه‌ها و پاورقی‌ها و غیره تا حدی از سند خارج می‌شوند، اما این برای ما مهم نیست - ما فقط به داده‌های جداول نیاز داریم. در اصل، در این مرحله، وسوسه انگیز است که به سادگی جدول را از سند شناسایی شده در Word کپی کنید و به سادگی آن را در اکسل قرار دهید. گاهی اوقات کار می کند، اما بیشتر اوقات منجر به انواع تحریف داده ها می شود - برای مثال، اعداد می توانند به تاریخ تبدیل شوند یا متن باقی بمانند، مانند مورد ما، زیرا. PDF از غیر جداکننده ها استفاده می کند:

پس بیایید گوشه ها را کوتاه نکنیم، بلکه همه چیز را کمی پیچیده تر کنیم، اما درست است.

مرحله 2: سند را به عنوان یک صفحه وب ذخیره کنید

برای بارگیری داده های دریافتی در اکسل (از طریق Power Query)، سند ما در Word باید در قالب صفحه وب ذخیره شود - این فرمت در این مورد، نوعی مخرج مشترک بین Word و Excel است.

برای این کار به منو بروید فایل - ذخیره به عنوان (فایل - ذخیره به عنوان) یا کلید را فشار دهید F12 در صفحه کلید و در پنجره باز شده نوع فایل را انتخاب کنید صفحه وب در یک فایل (صفحه وب - تک فایل):

پس از ذخیره سازی، باید فایلی با پسوند mhtml دریافت کنید (اگر پسوند فایل را در Explorer مشاهده کردید).

مرحله 3. آپلود فایل در اکسل از طریق Power Query

شما می توانید فایل MHTML ایجاد شده را مستقیماً در اکسل باز کنید، اما پس از آن، اولاً همه محتویات PDF را به همراه متن و تعدادی جداول غیر ضروری به طور همزمان دریافت می کنیم و ثانیاً به دلیل نادرست دوباره داده ها را از دست خواهیم داد. جداکننده ها بنابراین، ما از طریق افزونه Power Query وارد اکسل را انجام خواهیم داد. این یک افزونه کاملاً رایگان است که با استفاده از آن می توانید تقریباً از هر منبعی (فایل ها، پوشه ها، پایگاه های داده، سیستم های ERP) داده ها را در اکسل آپلود کنید و سپس داده های دریافتی را به هر طریق ممکن تغییر دهید و شکل دلخواه را به آن بدهید.

اگر اکسل 2010-2013 دارید، می توانید Power Query را از وب سایت رسمی مایکروسافت دانلود کنید - پس از نصب، یک برگه را مشاهده خواهید کرد. پرس و جو برق. اگر اکسل 2016 یا جدیدتر دارید، پس نیازی به دانلود چیزی ندارید - تمام عملکردها به طور پیش فرض در اکسل ساخته شده اند و در برگه قرار دارند. داده ها (تاریخ) در گروه دانلود و تبدیل کنید (دریافت و تبدیل).

بنابراین ما به یکی از برگه ها می رویم داده ها، یا روی برگه پرس و جو برق و یک تیم انتخاب کنید برای دریافت داده or ایجاد پرس و جو – از فایل – از XML. برای اینکه نه تنها فایل های XML قابل مشاهده باشند، فیلترهای موجود در لیست کشویی در گوشه سمت راست پایین پنجره را به تمام فایل های (تمام فایل های) و فایل MHTML خود را مشخص کنید:

لطفاً توجه داشته باشید که واردات با موفقیت انجام نمی شود، زیرا. Power Query از ما XML انتظار دارد، اما ما در واقع یک فرمت HTML داریم. بنابراین، در پنجره بعدی که ظاهر می شود، باید بر روی فایل نامفهوم برای Power Query کلیک راست کرده و فرمت آن را مشخص کنید:

پس از آن، فایل به درستی شناسایی می شود و ما لیستی از تمام جداول موجود در آن را می بینیم:

با کلیک بر روی دکمه سمت چپ ماوس در پس زمینه سفید (نه در کلمه Table!) سلول های ستون Data می توانید محتویات جداول را مشاهده کنید.

وقتی جدول مورد نظر تعریف شد روی کلمه سبز رنگ کلیک کنید جدول - و شما به محتویات آن "می افتید":

باقی مانده است که چند مرحله ساده برای "شانه کردن" محتویات آن انجام دهیم، یعنی:

1. حذف ستون های غیر ضروری (روی سربرگ ستون راست کلیک کنید – برداشتن)
2. نقطه ها را با کاما جایگزین کنید (ستون ها را انتخاب کنید، کلیک راست کنید - جایگزینی مقادیر)
3. علائم مساوی را در هدر حذف کنید (ستون ها را انتخاب کنید، کلیک راست کنید - جایگزینی مقادیر)
4. خط بالایی را بردارید (صفحه اصلی – حذف خطوط – حذف خطوط بالا)
5. خطوط خالی را حذف کنید (صفحه اصلی – حذف خطوط – حذف خطوط خالی)
   
6. سطر اول را به عنوان جدول بالا ببرید (صفحه اصلی - از خط اول به عنوان عنوان استفاده کنید)
7. با استفاده از فیلتر، داده های غیر ضروری را فیلتر کنید

هنگامی که جدول به شکل عادی خود رسید، می توان آن را با دستور بر روی صفحه بارگذاری کرد ببندید و دانلود کنید (بستن و بارگیری) on اصلی برگه و ما چنین زیبایی را به دست خواهیم آورد که می توانیم با آن کار کنیم:

• تبدیل یک ستون به یک جدول با Power Query
• تقسیم متن چسبنده به ستون