Extract Data from PDF to Excel (Table of Contents)
How to Extract Data from PDF to Excel?
In the present world, most of the data is available online in a Portable Document Format (PDF). But, it is hard to perform calculations or analysis on the data available pdf since there is no effective option to import the pdf data into the Excel. It needs to extract the pdf data into the Excel sheet to perform required operations on it. The best solutions available include GoNitro and Able2Extract.
Explanation
Over the internet, many of the free services are available to convert the PDF data freely to Excel. But, this solution requires uploading the PDF file to the website. For a business organization, it is not a good practice as sensitive may be stored in PDF files. To avoid such a problem, many people enter the data manually into the Excel sheets. But, this requires a lot of time and efforts from the people. Mainly two types of options are available as explained below
- First Option: This option involves importing data from the PDF to the Excel simply copy and paste
- Second Option: This option involves in importing the data to excel through Microsoft word
- Third Option: This option involves in use of the “text to column” feature in Excel
- Fourth Option: This option involves in use of the Adobe Reader commercial version software
Selecting the right option is required to extract data from PDF to Excel in with fewer efforts.
Use of Extract Data from PDF to Excel
The data extracted from the PDF to Excel can be used for various purposes.
- We can apply multiple operations such as sorting, filtering, conditional formatting, creating charts, developing pivot tables, and performing calculations
- Easy to interpret and conduct analysis on the data applying various functions and formulae
- Modification and manipulation of the data if any errors are presented in the data
- We can easily export the data to Microsoft word and access database
- We can apply every kind of Excel functions that applied for numerical and non-numerical data
Examples of Extract data from PDF to Excel
Below are some examples to extract data from pdf to excel.
Example #1 – Extract data from PDF to Excel Using the Copy and Paste Option
The following steps are followed to extract the data using simple commands copy and paste.
Step 1: Select the PDF file and open it in Adobe Acrobat reader software as shown in the figure.
Step 2: Select and copy the data presented in the table.
Step 3: Go to Excel and select the drop-down section of the “paste” and click on the “Paste Special”.
It opens the “paste special” dialog box as shown in the figure.
Step 4: Select paste option as “text” and click on “OK”.
Step 5: The data extracted into the Excel sheet is shown in the below-mentioned screenshot.
As shown in the figure, data is entered into the only one column.
Step 6: Select the data and click on “Text to column” option in the “Data” tab.
It opens the “convert text to columns” wizard as shown in the figure.
Step 7: Choose the file type as “Delimited” and click on “Next” button and it opens the following screenshot.
Step 8: Choose the Delimiters as “Space” and click on “Next”.
Step 9: After clicking next, the following wizard will be opened.
Step 10: Click on “Finish” to close the wizard and then the text is converted to columns as shown in the figure.
Step 11: Clean up the data to ensure that “blue LED value” into one cell, “green LED value” into one cell”, and “red LED value” into one cell.
Example #2 – Extract data from PDF to Excel Using the Microsoft Word
The following steps are followed to extract the data using the Microsoft word.
Step 1: Select the PDF file and open it in Adobe Acrobat Reader software.
Step 2: Select and copy the data presented in the table.
Step 3: Open Microsoft Word document and paste data into it as shown in the figure.
Step 4: Again copy the table and paste now it into an Excel sheet and it will be displayed as.
Step 5: Arrange the table headers properly as shown in the PDF file by applying the merging and unmerging on cells.
Example #3 – Extract data from PDF to Excel using Adobe Reader Software
The following steps are followed to extract the data using the Adobe reader
Step 1: Select the PDF file and open it in Adobe Acrobat reader software as shown in the figure.
Step 2: Go to the “File” menu and select the “convert to Excel, word, or powerpoint” option.
After clicking on the convert option, the following page will be displayed.
Step 3: Select the “convert to” drop-down menu and choose the “Microsoft Excel Document (*.xlsx) format.
Step 4: Then, click on the “Export to Excel” option.
Step 5: The exported data into the Excel is displayed as shown in the figure.
Step 6: Apply cleaning to arrange the data properly into the rows and columns to change the design of the table.
Things to Remember
- Extracting data from PDF to Excel is possible only when we are able to copy the data in a table in PDF file.
- The data directly copy-pasted directly from the PDF to Excel will unwind into an only column or one cell. This requires clean-up activities to arrange the data properly.
- To extract data using the Adobe Reader software, a user account is necessary with the adobe.com.
Recommended Articles
This has been a guide to Extract Data from PDF to Excel. Here we discuss how to extract data from pdf to excel using 3 methods which includes 1) Copy and Paste Option, 2) Microsoft word and 3) Adobe Reader along with some examples. You may learn more about excel from the following articles –
- Auditing Tools in Excel
- Top Excel Hacks and Tricks
- How to Export Excel into PDF?
- Power Bi Tutorial
- Use Power Query in Excel
- Switch Columns to Rows in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
