Convert PDF to Excel in Python

This article is maintained by the team at commabot.

Install Required Libraries

First, install the necessary Python libraries. Open your command prompt or terminal and run:

pip install PyPDF2 pandas tabula-py

Extract Text from PDF

We'll start by extracting text from the PDF. If your PDF contains tables, tabula-py can be particularly effective.

import tabula

# Replace 'your_file.pdf' with your PDF file path
file_path = 'your_file.pdf'

# This function extracts tables from the PDF and returns a list of DataFrame objects
tables = tabula.read_pdf(file_path, pages='all', multiple_tables=True)

Convert to Excel

Once you have your data in a DataFrame (or multiple DataFrames), you can easily export it to Excel.

# Loop through each table and save it as a separate Excel file
for i, table in enumerate(tables):
    table.to_excel(f'output_table_{i}.xlsx', index=False)

Handling Complex PDFs

If your PDF isn't being converted accurately, it might require a more customized approach. You might need to specify additional parameters in tabula.read_pdf() such as area, columns, guess, etc., to fine-tune the extraction.

Merging Multiple Tables

If you have multiple tables and want to merge them into a single Excel file, you can do so using pandas.

# Concatenate all tables into a single DataFrame
merged_table = pd.concat(tables)

# Export the merged table to Excel
merged_table.to_excel('merged_output.xlsx', index=False)

Note: the more structured and table-like your PDF is, the easier it will be to convert. For highly graphical or complex PDFs, you might need to explore more advanced parsing techniques or consider manual data entry.