DEV Community

jelizaveta
jelizaveta

Posted on

Unlocking PDF Data: Converting PDF to Excel with Free Python APIs

Transforming PDF documents into Excel spreadsheets is a critical process for tasks like data analysis, reporting, and automating workflows. This guide presents two effective methods for harnessing free Python libraries to accomplish this task:

  • Converting complete PDF pages or entire documents to Excel format
  • Extracting tables from PDF files and exporting them into Excel

By comparing these methods, you’ll gain insights to select the best approach tailored to your requirements.

Necessary Libraries to Install

To get started, you need to install the following Python libraries:

  • [ Free Spire.PDF for Python ]: This powerful library provides tools for handling PDF files, including the ability to convert PDF content to Excel and extract tabular data.
  • [ openpyxl ]: A well-known open-source library that facilitates reading, writing, and modifying Excel files.

You can install both libraries using pip:

pip install spire.pdf.free openpyxl
Enter fullscreen mode Exit fullscreen mode

After installing the libraries, we can delve into the methods for conversion.

Method 1: Full PDF Document to Excel Conversion

This approach entails converting the entire content of a PDF—text, images, and layout—into an Excel spreadsheet, preserving the original design.

Essential Conversion Settings

The XlsxLineLayoutOptions class specifies how the conversion from PDF to Excel will occur. This class takes the following parameters:

  • convertToMultipleSheet (bool) : If set to True, each page of the PDF becomes a separate worksheet; if False, only the first page is converted.
  • rotatedText (bool) : Controls whether to show rotated text found in the PDF.
  • splitCell (bool) : If True, it splits multi-line text into separate cells in Excel; if False, it retains all text in a single cell.
  • wrapText (bool) : Activates text wrapping within Excel cells.
  • overlapText (bool) : Determines how overlapping text is handled.

Sample Code Implementation 

from spire.pdf.common import *
from spire.pdf import *

# Instantiate the PdfDocument class
doc = PdfDocument()

# Load the desired PDF file
doc.LoadFromFile("C:\\Users\\Administrator\\Desktop\\input.pdf")

# Configure the XLSX conversion options
options = XlsxLineLayoutOptions(False, False, False, True, False)

# Apply these options to the document
doc.ConvertOptions.SetPdfToXlsxOptions(options)

# Save the output as an XLSX file
doc.SaveToFile("output/ToExcel.xlsx", FileFormat.XLSX)

# Close the document
doc.Close()
Enter fullscreen mode Exit fullscreen mode

Evaluating Pros and Cons

Pros:

  • Preserves the original layout of the PDF, including text placement and formatting.
  • Effective for documents with complex layouts.

Cons:

  • May lead to cell merging or unwanted adjustments in row and column sizes.
  • Text might lose coherence with content spread across various cells.

Method 2: Table Data Extraction from PDF to Excel

This method is focused on extracting tabular data from a PDF and exporting it to Excel, ensuring the output is coherent and well-structured.

Code Example for Table Extraction 

from spire.pdf import *
from openpyxl import Workbook
from openpyxl.utils import get_column_letter

# Create a PdfDocument object
doc = PdfDocument()

# Load the PDF file 
doc.LoadFromFile("C:/Users/Administrator/Desktop/AI.pdf")

# Set up a table extractor for the PDF document
extractor = PdfTableExtractor(doc)

# Create a new Excel workbook and remove the default sheet
workbook = Workbook()
workbook.remove(workbook.active)

# Extract tables across all pages
for page_index in range(doc.Pages.Count):
    tables = extractor.ExtractTable(page_index)
    for t_index, table in enumerate(tables):
        # Create a new worksheet for each extracted table
        sheet = workbook.create_sheet(title=f"Page {page_index + 1}_Table {t_index + 1}")

        # Track maximum lengths of each column
        max_lengths = [0] * table.GetColumnCount()

        for row in range(table.GetRowCount()):
            for col in range(table.GetColumnCount()):
                text = table.GetText(row, col).replace("\n", " ").strip()
                sheet.cell(row=row + 1, column=col + 1, value=text)
                max_lengths[col] = max(max_lengths[col], len(text))

        # Automatically adjust column widths
        for col in range(table.GetColumnCount()):
            adjusted_width = (max_lengths[col] + 2)  # Add buffer space
            sheet.column_dimensions[get_column_letter(col + 1)].width = adjusted_width

# Save the completed workbook to an Excel file
workbook.save("output/TableData.xlsx")
Enter fullscreen mode Exit fullscreen mode

Advantages and Disadvantages

Advantages:

  • Focuses solely on extracting table data, resulting in a clear and organized output.
  • Improves readability in Excel (avoids issues with merged cells).

Disadvantages:

  • Does not retain any content that is not structured as a table (e.g., images or comments).
  • Requires a clearly defined table structure in the PDF for accurate extraction.

Method Comparison

Method Data Integrity PDF Layout Retention Excel Clarity Best Situations
Full Page Conversion Comprehensive data High (maintains layout) Moderate (potential formatting issues) Complex-formatted documents
Table Extraction Limited to table data Low (non-table formatting lost) High (structured output) PDFs with well-defined tables

Conclusion

  • Opt for the full page conversion method if preserving the original layout (including images and free text) is critical, especially in formal documents.
  • Choose the table extraction method when only structured data is desired for analysis.

Each method presents its own advantages; your choice should align with the specific requirements of your project.

Top comments (0)