Extract Table from PDF with Regex Patterns for Rows

To extract a table from a PDF with regex, get the page as plain text, then write one pattern that matches a whole data row: a date at the start, an amount at the end, and the description between them. Each match becomes a row and each capture group a column; headers fail to match and drop out.

Why does regex suit text tables copied from a PDF?

Regex suits PDF text tables because a data row has a predictable shape even when the layout does not. A regular expression, or regex, is a pattern that describes text, such as "two digits, a slash, two digits". It does not care where a column sits on the page, only what the row looks like.

Table detectors work the other way. They look at where text sits on the page, and guess column boundaries from gaps and ruling lines. That works well on a neatly ruled table. It struggles when columns are uneven, when a description is long enough to push into the next column, or when the text has already been copied and lost its positions.

A pattern is a good fit when:

• Every data row starts or ends with something recognisable, such as a date or an amount.
• The table has three to six fields, with at most one free-text field.
• You already have the text, from a copy, a converter's text output or a command-line tool.

A pattern is a poor fit when the table has several free-text columns side by side, because a regex cannot tell where one description stops and the next starts. It cannot help with a scanned page either, until OCR has turned the image into text. Getting a PDF image into Excel covers that first step.

If the columns are already separated by a clear delimiter, such as a tab or two spaces, splitting may be simpler than matching. Splitting PDF data with TEXTSPLIT covers that route.

Building a row regex for date, description and amount

A row regex for a date, a description and an amount has three parts: a date pattern anchored at the start of the line, an amount pattern anchored at the end, and a lazy "anything" in the middle for the description. Anchoring both ends is what keeps the description from swallowing the amount.

Here is a pattern for a common layout, written out with comments:

^\s*                              # start of line, optional indent
(\d{2}/\d{2}(?:/\d{2,4})?)        # date: 03/14, 03/14/26 or 03/14/2026
\s+                               # at least one space
(.+?)                             # description, as short as possible
\s{2,}                            # two or more spaces before the amount
(-?\$?[\d,]+\.\d{2}-?)            # amount, with optional sign and dollar sign
\s*$                              # optional trailing space, end of line

Each part has a job:

• ^ and $ pin the match to the start and end of the line. Without them, the pattern can match a date and an amount buried inside a longer line, such as a summary sentence.
• (?:/\d{2,4})? makes the year optional. Many statements print only the month and day.
• (.+?) is lazy. It takes as few characters as it can while still letting the rest match. A greedy (.+) would grab as much as possible and could leave too little for the amount.
• \s{2,} is the separator between description and amount. Copied PDF text usually has a run of spaces there, while a description holds single spaces only.
• -? at both ends of the amount accepts a leading or a trailing minus.

If your amounts have a separate debit and credit column, the text will show a gap of different widths depending on which column holds the number. Match one amount and decide its column later from a balance column, or use word positions instead of text. A regex alone cannot see which column an amount was printed in.

Named groups in a PDF table regex

Named groups give each field in the regex a label, so the output columns come out named rather than numbered. In the PCRE2 style that Excel uses, a named group is written (?<date>...). In Python it is written (?P<date>...). The match then reports each field by name.

The Python re documentation describes (?P<name>...) as working like ordinary brackets, except that the matched text is also available under the group's name. Its groupdict() method returns every named group of a match as a dictionary keyed by name. That dictionary maps straight onto a table row.

The pattern above, with names:

^\s*(?P<date>\d{2}/\d{2}(?:/\d{2,4})?)\s+(?P<description>.+?)\s{2,}(?P<amount>-?\$?[\d,]+\.\d{2}-?)\s*$

Names cost nothing and pay off in three ways. A pattern with five unnamed groups is hard to read a month later. Adding a field in the middle renumbers every group after it, while names stay put. And when the output is a CSV or a data frame, the column headers come for free.

Python also has a verbose flag, re.VERBOSE, which ignores whitespace in the pattern and treats # as the start of a comment. Writing the pattern over several commented lines, as in the previous section, is the easiest way to keep it maintainable.

Which PDF lines should the table regex skip?

The table regex should skip every line that is not one data row: column headings, page headers and footers, running totals, blank lines and the second line of a wrapped description. Anchored patterns skip most of these on their own. The risky ones are lines that look like data, such as a dated total.

The worked example below runs the pattern over eight made-up lines of statement text. All names and figures are examples.

Three lines need attention.

The continuation line under the payment is part of that row's description. Skipping it loses the reference number. To keep it, test each line that fails the main pattern against a second, looser pattern: no date, no amount, indented. If it fits, append it to the description of the previous matched row.

The total line is the real hazard. It has a date and an amount, so it matches, and it would double the money if summed. Exclude it with a negative lookahead after the date, such as (?!\s+Total\b), or filter the description column for words like "Total", "Balance forward" and "Subtotal" after matching. The second is easier to read and change.

The page footer and the header fail on their own. Keep a list of skipped lines anyway, so you can read them once and confirm nothing important was dropped.

Running the row regex in Excel and in Python

In Excel, REGEXEXTRACT applies the row regex to each line in a cell and can return every capture group at once. In Python, the re module loops over the lines and collects each match's named groups into a list of rows. Both approaches use the same pattern with a small change in the group syntax.

Excel

Microsoft's REGEXEXTRACT reference says a return mode of 2 returns the capture groups of the first match as an array, and that the function uses the PCRE2 regex flavour. It is available in Excel for Microsoft 365. With one line of text per cell in column A:

=IFERROR(REGEXEXTRACT(A2,"^\s*(\d{2}/\d{2}(?:/\d{2,4})?)\s+(.+?)\s{2,}(-?\$?[\d,]+\.\d{2}-?)\s*$",2),"")

The result spills into three cells: date, description, amount. Lines that do not match return an error, which IFERROR turns into blanks. Filter out the blank rows, and convert the amount column to numbers before summing.

Without Microsoft 365, Excel has no regex function. Flash Fill can learn a similar split from a couple of typed examples, and using Flash Fill on PDF data shows where it guesses well and where it does not.

Python

import re, csv

row = re.compile(r"""^\s*(?P<date>\d{2}/\d{2}(?:/\d{2,4})?)\s+
                    (?P<description>.+?)\s{2,}
                    (?P<amount>-?\$?[\d,]+\.\d{2}-?)\s*$""", re.VERBOSE)

rows, skipped = [], []
for line in open("statement.txt", encoding="utf-8"):
    m = row.match(line)
    if m:
        rows.append(m.groupdict())
    else:
        skipped.append(line)

with open("rows.csv", "w", newline="") as f:
    w = csv.DictWriter(f, fieldnames=["date", "description", "amount"])
    w.writeheader()
    w.writerows(rows)

In verbose mode, the spaces inside the pattern are ignored, so the literal spaces between groups must be written as \s. That is already the case here.

Testing a PDF table regex against the printed totals

Testing the regex against printed totals proves it caught every row and no extra ones. Sum the amount column and compare it with the total or the change in balance the PDF prints. Then count the matched rows and compare them with the lines you can count on one sample page.

A short test routine:

1. Sum the matched amounts after converting them to numbers.
2. Compare with the printed period total, or with closing balance minus opening balance.
3. If they differ, sort the matched rows by amount and look for the difference as a single figure. One missing row shows up that way.
4. Read the skipped-lines list from top to bottom. A data row with an unusual date or amount format will be there.
5. Run the pattern on a second month's file before trusting it. Layouts change, and a pattern that fits one file perfectly can miss a line style that only appears now and then.

A difference of exactly twice one amount means a sign went wrong: a trailing minus the pattern did not capture, or a credit written without one. A difference equal to a total line means the total matched as a row.

Regex is one of several ways to pull data out of PDF text. When you need a few single values rather than a table, label-based formulas for PDF fields are usually quicker to write.

For bank statements, FastBankConvert is being built so you do not have to write the pattern at all: it reads the layout and checks that the balances add up before you download. It is not open yet, and the get-started page takes your email for the launch.