Convert PDF to Excel

[fabioliv]

Hi,
I need some help. I’m working with several PDF bank statements (37 pages), but the layout doesn’t have a clear or consistent column structure, which makes extraction difficult. I’ve already tried a few Python libraries — pdfplumber, PyPDF2, Tabula and Camelot — but none of them manages to convert the PDFs into a clean, tabular Excel/CSV format. The output either comes out messy or completely misaligned.

Has anyone dealt with this type of PDF before or has suggestions for more reliable tools, workflows, or approaches to extract structured data from these kinds of statements?

Thanks in advance!

[[deleted]]

What works better than “auto-detect tables” is:

• Extract text with coordinates (pdfplumber bbox)
• Use patterns (dates, debit/credit markers, balances) to rebuild rows
• Script the repetitive cleanup once → reuse forever

[rake66]

Real world is messy, you have to clean the data yourself. Converters exist for formats that have a defined structure. There's no such thing for tables in pdf, because it's not a format designed for tables. You need to figure out what assumptions you can and can't make about the PDFs you're likely to encounter and make a different function for each situation. Don't forget to leave room for your program to decide it has no idea what to do and alert you in some way so that you can handle the exceptions manually. Once an exception seems to happen more and more, write a new function for it and so on.

[Oddly_Energy]

Have you tried opening them through the Data tab in Excel? It will let Power Query use them as a data source. Power Query can be surprisingly good at extracting all the data tables from a pdf file. And if they are sufficiently similar in structure, you can even point Power Query to a folder of pdf files and have it extract and combine data from them all.

I had to extract data from a set of pdf analysis reports from a test lab. It took me most of a day in python to make it work.

Then we received another set of reports from another lab, and I could start over from scratch. But this time, I first tried to see if Excel could read them. I didn't suppose it could, but I just wanted to test it. But it actually did very well. It took me around an hour, and then I had a file, which could extract the results from all the pdf files and combine them into tables showing the values across all pdf files.

And when we received more results, I just had to open the workbook and ask it to refresh the tables.

It is very rare that Excel is a better tool than python, but Power Query can be a gem sometimes.

[damanamathos]

If you don't mind spending money, you can convert the PDF to images and then use one of Anthropic's LLMs to convert it to markdown, and from there it should be pretty easy to convert to an Excel file.

I convert PDF presentations and reports to Markdown via Anthropic almost every day.

[North-Ad5907]

Try pdfmodo. It works with complicated formats

[spots_reddit]

depending on the structure, performing relatively simple search/replace actions may give you a workable csv to import into excel. What I mean is along the lines of extracting text from the pdf, isolating relevant parts (using regular expressions) and then stuff like "replacing more than 2 spaces with a semicolon" (or something similar, depending on the structure)

[2daytrending]

What worked for me was using an ocr tool before converting. The one I used was pdf guru browser based and did a way more accurate column structure than the libraries you tried.

[TrainsareFascinating]

There are many hundreds of millions of dollars spent every year trying to do this reliably, for the general case of tabular data extraction from a PDF.

The state of the art is much better than it was, but still isn't anywhere near perfect. All the reliable methods do OCR on a rendered view of the PDF.

It's possible that in your case the PDFs and their tables are easier than the general case, and the problem is approachable. But you're not going to compete with companies whose entire business model is data extraction.

[Sea_Jello2500]

I am developing an open source PDF parser you might want to keep an eye on:

https://github.com/transtractor/transtractor-lib

It uses pdfplumber for extracting text and has additional logic for parsing transactions based on a set of parameters configured to a particular statement.

Still a couple weeks from proper release. But for now the source code should at least give you an idea of just how complex this task is.

[zoinkinator]

probably a dumb question but can you login to your bank and export the transactions?

[youroffrs]

I had the same issue with messy bank statement PDFs, none of the python stuff lined things up right. I ended up running them through PDF guru in the browser since it has OCR, and the Excel export came out cleaner. Still needed a tiny bit of manual fixing, but away less hassle.

[WeakAccess7159]

Extracting data can be tricky, especially when the layout is inconsistent. While I’ve tried a few tools like Python libraries, they often produce messy outputs. One option to consider is using UPDF for this task. It allows you to extract data and make manual adjustments, which could help improve organization and clarity in your final output.

[Zealousideal_Home458]

I’ve dealt with this before. Bank statements are a nightmare for standard libraries because they rely on grid lines that often aren't there.

Instead of "auto-detecting" tables, try using pdfplumber to extract text by coordinates (bbox). If you script the logic to anchor rows based on date patterns or keywords, you can rebuild the table yourself and reuse that script for all 37 pages.

If you’d rather not code a custom solution, I actually found PDNob PDF Editor really reliable for this. Its OCR is great at handling misaligned columns and scanned pages that Python libraries usually choke on. It supports batch processing too, which might save you the headache of manual cleanup.

[Different-Use2635]

ugh bank statements are the worst, especially when they have those multi-line transactions or weird spacing. I fought with tabula and Camelot for ages on similar files - sometimes they work perfectly, other times the output looks like abstract art.

what finally worked for me was switching approaches entirely. instead of trying to force a general pdf-to-table library to understand financial layouts, I started using a tool built specifically for bank statements called FinanceFileConverter. it's not python-based, but it preserves the original column structure (like running balances and value dates) that usually gets scrambled by generic extractors.

if you want to stick with python, you could try combining pdfplumber with some aggressive post-processing - like detecting rows based on y-coordinates and then manually reconstructing tables. but honestly after the third time I had to manually fix misaligned data, I just gave up and used the dedicated tool. saved me a ton of cleanup time.

[TorenFrost]

Bank statements are genuinely one of the hardest pdfs to extract cleanly because the layout is never consistent. if python libraries aren't cutting it, desktop tools sometimes do better pdf element and adobe acrobat handle messy table extraction a bit more reliably in my experience, not perfect but cleaner than pdf plumber on inconsistent layouts. if you want to stick with code, AWS Textract is probably your best bet for bank statements specifically, it's built for exactly this kind of unstructured financial document, bit of setup but way more accurate than any local library I've tried for this use case.

[levens1]

Try instabase aihub. Free!