@g_avinash
In some cases, you can use Power Query (prerequisite, of course, that your excel version offers this possibility) to import data from a PDF file. Power Query allows you to connect to various data sources, including PDF files. Open Excel, go to the "Data" tab, click on "From File" and select "From PDF." Follow the prompts to import the data from the PDF file.
If there isn't that much data, I would recommend converting it to Excel beforehand (see inserted file in Excel) and then using the data through the Excel sheet. Otherwise, when there is many data, you should pay attention to a few things when importing data from PDF.
- PDFs with structured tables: If the PDF file contains tables with clear column and row structures, Power Query can recognize and extract the tabular data efficiently. You can select the table in the PDF and import it using Power Query, which will create a table in Excel with the extracted data.
- PDFs with text-based content: Power Query can also handle PDF files that contain text-based content, such as reports, invoices, or receipts. It can extract the text and convert it into a structured format in Excel. You may need to define the delimiters or use specific transformations to split the text into columns and rows.
- PDFs with consistent formatting: If the PDF files you're working with have consistent formatting across different pages, Power Query can be set up to apply the same transformations to each page automatically. This is useful when dealing with multipage PDF documents or recurring reports.
- PDFs with selectable text: Power Query requires selectable text to extract data accurately. If the PDF file contains scanned images or non-selectable text, the extraction process might not work properly. In such cases, you may need to use optical character recognition (OCR) tools to convert the scanned text into selectable text before using Power Query.
- PDFs with multiple layouts: Power Query allows you to define and apply different extraction rules for PDF files with varying layouts. You can create custom queries and transformations to handle different page structures and extract the required data accordingly.
The success of using Power Query to import data from PDF files depends on the quality and structure of the PDF file itself. All this is roughly described because my knowledge of PQ is modest.
