5 Best Ways to Convert PDF Files to Excel Files Using Python

by

💡 Problem Formulation: In many business and research scenarios, there’s a need to extract data from PDF files and transfer it into an Excel spreadsheet for further analysis or modification. For example, one might need to take financial statements in PDF format and convert them into Excel to perform calculations.

Method 1: Using Tabula-py

This method involves using the Tabula-py library, a Python wrapper for tabula-java, which can extract tables from PDFs and output them in a DataFrame format compatible with Excel. Tabula-py is ideal for PDFs with clear, well-defined tables.

Here’s an example:

from tabula import read_pdf
from tabulate import tabulate

# Read PDF file
df = read_pdf('input.pdf', pages='all', multiple_tables=True)

# Convert the dataframe to Excel file
df.to_excel('output.xlsx', index=False)

The output is an Excel file with the data from the PDF tables.

In the provided code snippet, the read_pdf() function reads all pages of ‘input.pdf’ and extracts all tables, then the DataFrame is converted to an Excel file ‘output.xlsx’ without the index column.

Method 2: Using PyPDF2 and Openpyxl

PyPDF2 can be used for reading PDF files, while Openpyxl allows you to write to Excel files. This combination is useful when you need to extract text from a PDF and control how it’s written to Excel.

Here’s an example:

from PyPDF2 import PdfFileReader
from openpyxl import Workbook

pdf = PdfFileReader('input.pdf')
wb = Workbook()
ws = wb.active

for page_num in range(pdf.numPages):
    text = pdf.getPage(page_num).extractText()
    ws.append([text])

wb.save('output.xlsx')

The output is an Excel file with each PDF page’s text in a separate row.

The above code opens ‘input.pdf’, extracts text from each page, and appends it as a new row in an Excel spreadsheet before saving it as ‘output.xlsx’.

Method 3: Using PDFplumber and pandas

PDFplumber offers precise PDF text extraction and pandas is excel at manipulating tabular data. This method provides highly accurate data extraction from PDFs, even when dealing with sophisticated layouts.

Here’s an example:

import pdfplumber
import pandas as pd

with pdfplumber.open('input.pdf') as pdf:
    pages_data = []
    for page in pdf.pages:
        # Extracting tables
        pages_data.append(page.extract_table())

# Convert list of tables to pandas DataFrame
df = pd.concat([pd.DataFrame(table[1:], columns=table[0]) for table in pages_data if table])

# Write to Excel
df.to_excel('output.xlsx', index=False)

The output is a consolidated Excel file containing all extracted tables from the PDF.

This script reads ‘input.pdf’, extracts tables from each page, then transforms these tables into a pandas DataFrame, which is then written to ‘output.xlsx’.

Method 4: Using Slate and XlsxWriter

The Slate library allows for easy text extraction from PDFs and XlsxWriter is a powerful Python library for creating Excel files. Combine them if you need to customize your Excel output heavily.

Here’s an example:

import slate3k as slate
import xlsxwriter

with open('input.pdf', 'rb') as f:
    pdf = slate.PDF(f)

workbook = xlsxwriter.Workbook('output.xlsx')
worksheet = workbook.add_worksheet()

for i, page in enumerate(pdf.text()):
    worksheet.write_string(i, 0, page)

workbook.close()

The output is an Excel file with each page’s text in a separate cell arranged in a column.

This code opens ‘input.pdf’, extracts text from each page with Slate, writes each page’s text into a separate cell in an Excel file using XlsxWriter, and then saves the file as ‘output.xlsx’.

Bonus One-Liner Method 5: Using Camelot and One-Liner pandas

Camelot is specialized in extracting tables from PDFs with minimal fuss. In combination with pandas, you can convert PDF tables to Excel files in just one line of code after setting up your environment.

Here’s an example:

import camelot
camelot.read_pdf('input.pdf').df.to_excel('output.xlsx')

The output is a neatly formatted Excel file containing tables from ‘input.pdf’.

The one-liner uses Camelot to read all tables from ‘input.pdf’ and pandas to immediately convert the resulting DataFrame to an Excel file ‘output.xlsx’.

Summary/Discussion

  • Method 1: Tabula-py. Good for well-defined tables. Requires Java. May struggle with complex layouts.
  • Method 2: PyPDF2 and Openpyxl. Offers control over the content layout in Excel. Might require additional parsing for structured results.
  • Method 3: PDFplumber and pandas. Highly accurate table extraction. Handles sophisticated PDF layouts. Requires additional code to manage non-table data.
  • Method 4: Slate and XlsxWriter. Enables heavy customization of Excel output. Slate’s text extraction may not be as accurate for tables.
  • Bonus Method 5: Camelot and pandas. Straightforward for table extraction. Depends on the quality of the PDF and may require Camelot’s additional settings adjustment for complex tables.