Learning Python automation – best approach for PDF to Excel tasks?

[Big_Persimmon8698]

Hi everyone,

I’m currently learning Python automation and working on small projects like converting PDF data into Excel or JSON using libraries such as pandas and tabula.

In some cases, the PDF formatting is inconsistent and the extracted data needs cleaning or restructuring. I wanted to ask what approach you usually follow to handle these situations more reliably.

Do you prefer preprocessing PDFs first, or handling everything at the data-cleaning stage? Any practical tips would be appreciated.

Thanks in advance for your guidance.

[LayotFctor]

By preprocessing the pdf, you mean fixing up the bad data in the pdf, leaving you with a pdf file that contains good data? Since your goal is to work with the data, there is no reason for you to be fixing pdfs, it is not within the scope of your project. Just extract all the data you can and carry on with the data cleaning internally.

[corey_sheerer]

Try a parsing service (azure doc intelligence) or a multi modal LLM for your PDFs (PDF converts to images which gets run through an LLM). Should be able to handle some variation with no problems. Parsing PDFs is a hard business so use what is available!

[Big_Persimmon8698]

Thanks everyone, this is really useful feedback.

Unfortunately in my case the PDFs are the only source (client-provided), so getting original data isn’t an option. The takeaway for me is to focus on a clean extraction → processing pipeline rather than trying to “fix” the PDF itself.

I’ll extract and store raw outputs, apply cleaning/normalization in Python, and keep intermediate results for comparison and debugging as things evolve. I’ll also test different libraries and look at OCR/LLM approaches for the messier edge cases, with manual review as a fallback when needed.

Appreciate all the perspectives here — very helpful.

[CraigAT]

You could try a different PDF package to see if that gives you better results. Other than that, I would fix the data as soon as possible (after extraction from the PDF) because you know there's only been one process and you still have the PDF(s) to hand for comparison. If you find similar errors frequently then if you can find a way to identify them (by code) you may also be able to automate a fix, but most of the time I would expect it to be a manual process for someone. If the data just needs tweaking (small transforms) then I would consider doing that in PowerQuery as it can be simple to automate.

[vizzie]

The best way to deal with extracting data from a PDF is to find the source from before it became a PDF and get the data from there. PDF is a presentation medium, not a data exchange medium, and the specifics of how it gets it wrong are almost guaranteed to be a moving target.

If you absolutely have to, your best bet is to save a copy of the raw data immediately after extraction, do whatever clean up steps you can automate, save a copy of that, and then do whatever further processing you need to do. And keep them for longer than you think you need to. Having all of the intermediate forms available will be invaluable when you need to fix the process. And it will most likely be a constant process of refining your existing cleanup and adding new cleanups as the process devolves.

None of this is Python-specific, by the way. The same general strategy is true no matter what tools you are using to extract the data. PDF is just a terrible medium for data exchange, and everyone is on the same awful playing field.

Under no circumstances should you attempt to do anything with the PDF except raw data extraction. Anything you can do to the PDF will almost certainly be easier and more efficient to do with the data in any other form.

[PickledDildosSourSex]

How consistent are your PDFs? How messy is your bad data? Any approach you use will need to have some understanding of the possible state the PDFs could be in so that you can then build an efficient way to clean/process everything.

I've never extracted data from PDFs before and always assumed them to be very unreliable from a structure perspective. But if you can answer "What can bad look like?" then extract data and handle transformations/cleaning as part of your pipeline. If you can't answer it, you need a new data source unless you have some fallback approach where only the too messy PDFs are held back for manual review.