To extract a table from a PDF in R, read the page text with the pdftools package, split each line into fields with a regular expression or by word position, and collect the rows into a data frame. Then fix the types, drop header lines, and write the frame to an Excel file with writexl.
R packages for reading PDF table text
The pdftools package is the usual starting point for reading a PDF table in R. It wraps the Poppler PDF library and offers two views of a page: plain text laid out as it looks, and a list of every word with its position. Everything else in this post builds on one of those two views.
The pdftools reference on rOpenSci describes them this way. pdf_text returns a character vector with one element per page, each element being that page's text drawn onto a text canvas. pdf_data is lower level and returns one data frame per page, with one row for each text box.
install.packages(c("pdftools", "writexl"))
library(pdftools)
pages <- pdf_text("report.pdf")
length(pages) # number of pages
cat(pages[3]) # print page 3 to see the layout
Print one page before writing any parsing code. The printout shows whether columns line up under each other, whether amounts are right-aligned, and which header and footer lines repeat on every page. Those three facts decide which method in the next two sections fits your table.
Other R packages read PDF tables too. tabulizer wraps the Java library Tabula and tries to detect table areas by itself, but it needs Java installed and set up for R. If the table has visible ruling lines and you would rather not write any parsing, a detector of that kind can be quicker. For text tables with no lines, pdftools and a few rules usually give more control. And if you only have one file, extracting a PDF table with an online tool may beat any script.
Splitting pdf_text lines into data frame columns
Splitting pdf_text lines means turning each line of page text into fields. Because pdf_text keeps the visual layout, columns are separated by runs of spaces. A regular expression that matches the shape of a data row picks out the fields and skips every line that does not fit, such as headers and page numbers.
A worked example
Here is a made-up page of transaction text, as cat(pages[1]) might print it:
Example Supply Co. Account activity
Date Description Amount
03/02/2026 Office chairs, order 4471 -1,240.00
03/05/2026 Customer payment INV-208 3,500.00
03/09/2026 Freight -86.50
Page 1 of 2
The data rows all start with a date and end with an amount. The description sits between them and may contain single spaces and commas. That gives a pattern:
lines <- unlist(strsplit(pages, "\n"))
pattern <- "^\\s*(\\d{2}/\\d{2}/\\d{4})\\s+(.+?)\\s{2,}(-?[0-9,]+\\.\\d{2})\\s*$"
hits <- regmatches(lines, regexec(pattern, lines, perl = TRUE))
hits <- hits[lengths(hits) == 4]
tx <- data.frame(
date = sapply(hits, `[`, 2),
description = sapply(hits, `[`, 3),
amount = sapply(hits, `[`, 4)
)
The key piece is \\s{2,} before the amount. It requires at least two spaces, so a comma or a single space inside the description never ends it early. The title line, the column header and the page footer do not start with a date, so they drop out on their own.
The result, still all text at this stage:
| date | description | amount |
|---|---|---|
| 03/02/2026 | Office chairs, order 4471 | -1,240.00 |
| 03/05/2026 | Customer payment INV-208 | 3,500.00 |
| 03/09/2026 | Freight | -86.50 |
Fixed-width splitting
When every column starts at the same character position on every page, you can cut by position instead. substr(lines, 1, 11) takes the date, substr(lines, 13, 50) the description, and so on. This is simpler than a pattern, but it breaks the moment a page is laid out a few characters differently. Check the positions on the first and the last page before relying on them.
Rows that wrap
A long description that wraps onto a second line produces a line with no date and no amount. The pattern drops it, and the description loses its tail. To keep it, mark lines that match neither the pattern nor a known header, and paste each one onto the description of the row above before building the frame.
When should R use word positions from pdf_data?
Use word positions from pdf_data when the columns do not line up as text, when cells are empty, or when two columns run close together. pdf_data gives every word its x and y coordinates, so you group words into rows by y and into columns by where x falls, which survives layouts that defeat a regex.
Empty cells are the classic case. A table with separate debit and credit columns has one of the two blank on every row. In the pdf_text output, the amount simply floats somewhere in the middle, and a regex cannot tell which column it belongs to. Its x position can.
library(dplyr)
words <- pdf_data("report.pdf")[[1]] # page 1: x, y, width, height, space, text
rows <- words |>
mutate(col = cut(x, breaks = c(0, 90, 330, 420, Inf),
labels = c("date", "description", "debit", "credit"))) |>
group_by(y, col) |>
summarise(text = paste(text, collapse = " "), .groups = "drop") |>
tidyr::pivot_wider(names_from = col, values_from = text)
The breaks are the column boundaries in PDF points, measured from the left edge. Read them off the x values of a few header words: the left edge of "Debit" and "Credit" tells you where each column starts. Right-aligned amounts are the one trap. Their left edge moves with the length of the number, so put the boundary between two amount columns near the middle of the gap, not at the header's left edge.
A table that runs over several pages needs the same code applied to every element of the list that pdf_data returns. Wrap the pipeline in a function, run it with lapply over the pages, and stack the results with dplyr::bind_rows(.id = "page"). The page column costs nothing and makes it easy to trace any odd row back to the sheet of paper it came from.
Words on one printed line can differ in y by a point or so when fonts differ. If a row splits in two, round y before grouping, for example mutate(y = round(y / 3) * 3).
Spanning headers and grouped labels are a separate problem from positions. A label printed once for a group of rows needs filling down after extraction. Flattening a PDF table with merged cells covers that pattern, and the same fill-down logic works with tidyr::fill() in R.
Tidying the R data frame before export
Tidying the data frame means making each column the right type and each row a real record. After splitting, every field is text. Amounts need their commas and symbols removed, dates need parsing, and any leftover header or total rows need dropping before the frame goes anywhere near Excel.
tx <- tx |>
mutate(
amount = readr::parse_number(amount),
date = as.Date(date, format = "%m/%d/%Y")
) |>
filter(!is.na(date))
readr::parse_number strips the thousands commas and currency symbols and keeps the minus sign. It does not treat brackets, as in (86.50), or a trailing minus, as in 86.50-, as negative, so those amounts come back positive. If your PDF uses either style, turn them into a leading minus first with sub("^\\((.*)\\)$", "-\\1", amount) or a similar substitution.
Date formats deserve one check against the page. as.Date with the wrong format string returns NA rather than an error. Filtering out NA dates then quietly drops rows. Count rows before and after the filter, and compare the count with the number of lines on the printed page.
Finally, check the total. If the PDF prints a total or a closing balance, sum(tx$amount) should match it. A mismatch means a row was missed or doubled, and it is far cheaper to find now than after the file has been shared.
Writing the R data frame to an Excel file
Writing the data frame to Excel takes one call to write_xlsx from the writexl package. The writexl reference says it writes a data frame to an xlsx file, and that passing a named list of data frames creates one named sheet per frame.
library(writexl)
write_xlsx(
list(Transactions = tx, Skipped = data.frame(line = skipped)),
path = "report.xlsx"
)
A second sheet of skipped lines is worth the extra line of code. Collect every line that matched neither your data pattern nor a known header or footer, and write it out. When someone asks why a row is missing, the answer is on that sheet.
Dates written from an R Date column arrive in Excel as real dates, and numbers as numbers. If an amount column is still text, Excel shows it left-aligned and will not sum it. That is a sign the tidy step missed something, not a problem to fix in Excel.
If the destination is Google Sheets rather than Excel, the same file uploads cleanly, and moving PDF tables into Google Sheets covers the import options there.
Is an R script worth it for one PDF table?
An R script is worth it when you will run it again: a monthly report, a batch of similar files, or a table you need to rebuild each time the source changes. For a single small table, copying it out of the PDF, or using a converter, is usually faster than writing and testing parsing rules.
A rough guide:
- One table, under a page. Copy it. Copying a PDF table into Excel and fixing the paste shows how to repair the columns afterwards.
- One long table, once. A converter, then a check of the totals.
- The same layout every week or month. An R script, kept with the data it produces, so anyone can rerun it.
- Many layouts. A detector such as Tabula, or a converter, with R only for the tidying.
The script also earns its keep when the analysis already lives in R. Reading the table straight into a data frame saves a round trip through a spreadsheet and keeps the steps in one place.
If your PDFs are bank statements, FastBankConvert is being built to return them as Excel, CSV or JSON, and jsonlite::fromJSON reads JSON into R directly. It is not open yet; the get-started page takes your email for when trial conversions start.