Extract Data from PDF to Excel Python Script Walkthrough

To extract data from a PDF to Excel with Python, open the file with a PDF library, read each word with its position on the page, group the words into rows by height, put the rows in a pandas DataFrame, and write that to an .xlsx file. The script is short, and the tuning is the work.

Python libraries for PDF table extraction

Several free Python packages read PDFs, and they differ in what they give you. Start with pdfplumber, which returns text with coordinates and has a table finder. Add pypdf for plain text and page handling, and try Camelot or tabula-py if your tables have printed lines.

What each one is good for

Check each project's own documentation for current install steps and options, since versions and requirements change.

Which to try first

Try pdfplumber first, for two reasons. It works on digital PDFs without extra system programs, and it shows you the words and their positions, so when a table comes out wrong you can see why. A library that returns a finished table is faster when it works, and harder to debug when it does not.

A note on what these libraries cannot do

Every one of them reads text that is in the file. A scanned page has no text, only an image. The section on scanned pages below covers that. The idea of how table detection works in general is the same for all of them, and how software decides where a table is in a PDF explains it in plain terms.

Reading PDF words and positions in Python

Reading words with positions means asking the library for every word on a page along with its left edge, right edge, top and bottom. With these numbers you can rebuild the table yourself: words with nearly the same top belong to one row, and words whose left edges line up belong to one column.

What a word looks like

In pdfplumber, page.extract_words() returns a list of dictionaries, one per word. Each has the text and the coordinates x0, x1, top and bottom. The origin for top is the top of the page, and values grow downward. An example word looks like this:

{'text': 'CARD', 'x0': 54.0, 'x1': 82.5, 'top': 212.3, 'bottom': 221.1}

These numbers are examples. Real values depend on the page size and the font.

Grouping words into rows

Sort the words by top, then start a new row whenever the next word's top differs from the current row by more than a small tolerance, such as 3 points. A point is 1/72 of an inch, so 3 points is a small fraction of a line. Inside each row, sort by x0, left to right.

Assigning words to columns

For a table with a known layout, decide the column edges once. Read the header row, note the x0 of each heading, and treat each heading's x0 as the start of that column. A word goes into the column whose start is the greatest value not exceeding the word's x0. Right-aligned number columns are the exception: for those, compare x1 against the column's right edge.

A 15-line outline

This outline is a sketch, not a finished tool. It assumes a digital PDF, one table, a 3-point row tolerance, and column starts that you set by looking at the header.

import pdfplumber, pandas as pd
COLS = [("date", 0), ("description", 90), ("amount", 380), ("balance", 470)]
rows = []
with pdfplumber.open("statement.pdf") as pdf:
    for page in pdf.pages:
        words = sorted(page.extract_words(), key=lambda w: (w["top"], w["x0"]))
        line, last = [], None
        for w in words:
            if last is not None and w["top"] - last > 3:
                rows.append(line); line = []
            line.append(w); last = w["top"]
        rows.append(line)
col = lambda w: max((c for c in COLS if c[1] <= w["x0"]), key=lambda c: c[1])[0]
table = [{n: " ".join(w["text"] for w in r if col(w) == n) for n, _ in COLS} for r in rows if r]
pd.DataFrame(table).to_excel("statement.xlsx", index=False)

Run on a made-up statement page, the first rows of the output look like this:

On a real file the first rows are often headings, page titles and a summary block. You then skip rows until the header row and drop rows that do not start with a date.

Where the tolerance bites

A tolerance that is too small splits a row when a font differs slightly. Too large, and a wrapped line merges into the line above. Test it on the page with the most text. The row logic is the same one described in how table rows are found in a PDF.

Writing extracted PDF rows to xlsx with pandas

pandas writes a DataFrame to an Excel file with one call, to_excel. Before you write, convert the columns to proper types, because text that looks like a number will be saved as text and will not sum. The xlsx writer is provided by the openpyxl package, which you install alongside pandas.

Clean the types first

Amounts come out of the PDF as strings such as 1,850.00 or 220.00-. Convert them before saving:

1. Remove commas and currency symbols from the strings.
2. Move a trailing minus sign to the front.
3. Convert with pd.to_numeric(..., errors="coerce"), which turns anything it cannot read into an empty value that you can then find.
4. Convert dates with pd.to_datetime, giving the format your statement uses, so day-first and month-first dates are not mixed up.

Rows where the conversion produced an empty value are the ones to inspect by eye. They are usually wrapped description lines or page furniture.

Write the file

df.to_excel("out.xlsx", index=False) writes one sheet. For several tables, open an ExcelWriter and write each DataFrame to a named sheet. Setting index=False stops pandas from adding the row numbers as an extra first column.

Add a check before you trust it

A script that runs without errors can still return a wrong table. Add two checks at the end and print the results:

• The number of data rows against the number you counted on the PDF.
• For a statement, the opening balance plus the sum of the amounts against the closing balance.

If either check fails, stop and look. A script that reports "rows: 214, balance check: OK" is far more useful than one that only writes a file.

Does a Python PDF script handle scanned pages?

A Python PDF script does not handle scanned pages by itself, because a scanned page contains an image and no text. To read it, add optical character recognition (OCR), which turns the image into words. Expect more errors than from a digital PDF, and plan to check the numbers.

The usual add-on

Tesseract is a widely used open-source OCR engine, and Python wrappers such as pytesseract call it. A typical flow turns each page into an image, passes the image to the engine, and asks for words with their positions, so the row-grouping code from the earlier section still applies. Another route is OCR software that makes a searchable PDF first, after which your normal script can read it.

What changes in practice

• Setup is heavier: the engine is a program installed on your machine, apart from the Python package.
• Recognition errors appear in digits, such as a 5 read as a 6, which break balances quietly.
• Position values are noisier, so the row tolerance and column edges need more room.
• Results depend on the scan quality. A clean, straight, high-resolution scan helps more than any code.

Run the balance check on every scanned file. A misread digit in an amount changes the sum, so the check exposes it.

A sensible expectation

A script that works well on your digital PDFs may disappoint on scans, even with a good engine. Treat scanned input as a separate project with its own tests, and compare the output against the page for at least one file before you rely on it.

Can a converter's JSON output replace a Python PDF script?

A converter's JSON output can replace the reading half of a Python script. If a converter already returns each transaction as a field-labelled record, your script only has to load the JSON, build a DataFrame and write the xlsx, which takes a few lines and no layout logic.

What the script shrinks to

With JSON in hand, the code becomes something like loading the file with json.load, passing the list of records to pd.DataFrame, and calling to_excel. There are no coordinates, tolerances or column edges, and the hard part, reading the page, is done upstream.

When this fits

• You want the structure but not the maintenance of layout code.
• Your input is a kind of document the converter was built for.
• You want to post-process the data, for example to categorise it or to merge months.

For bank statements, FastBankConvert offers Excel, CSV and JSON downloads, and the JSON is meant for exactly this kind of script. It is not open yet, so check the get-started page for when you can try it. What fields a statement JSON should hold is covered in what JSON a bank statement parser should return.

When it does not fit

If your documents are not statements, or the converter's output is missing a field you need, you are back to reading the page yourself. A converter works best on the document type it targets.

When a Python PDF script costs more time than it saves

A Python PDF script costs more than it saves when the number of files is small or the layouts vary. The script has to be written, tested, kept working as layouts change, and checked every time. That is a real cost, and it only pays back across many similar files.

A back-of-the-envelope test

Estimate the hours to build and test the script, including edge cases like wrapped rows and page numbers. Add an hour or two each time a new layout appears. Compare that with the minutes you spend per file by hand, times the files per year. If the script costs a day and the manual job costs ten minutes a month, the script needs years to break even, and it will break before then.

Signs a script is a poor fit

1. You have fewer than about ten files, or one file.
2. Every file comes from a different source with a different layout.
3. Pages are scans of mixed quality.
4. Nobody besides you can maintain the code, and you may not remember it in six months.
5. The result feeds money decisions, and you have no time to build checks.

Cheaper ways to the same result

For one table, converting a single table out of a long PDF by hand or with a tool is faster. A script earns its place when the same layout arrives again and again and you can write the check once and trust it afterward.