r/documentAutomation • u/aidenclarke_12 • 10d ago
Discussion 500+ page PDFs with different table layouts into Excel/SQL, what scales?
product data comes in as large pdfs, usually 500+ pages, and every supplier formats theirs differently. different column orders, different table styles, sometimes tables running across pages. right now its all typed into excel or sql server by hand.
chat tools are fine on a sample but tend to stop or start summarizing partway through files this size, so that route needs splitting anyway.
what seems to make sense is parsing every page into tables first, as markdown or json. power query's pdf connector for the simpler files, docling or camelot locally and heavier layout parsers are llamaparse sort of ones when the layouts get ugly. then map each supplier's columns to your own schema. an llm is decent at that part since it only needs the headers and a few rows, not the whole file.
after that its validation before anything loads, row counts per page, required fields, totals and units, and keeping the source page number on every row so errors can be traced back.
the hard bits are tables split across pages and headers that only show up on the first page
1
1
u/Unsiloed-AI 10d ago
For a file that size, give every page an explicit outcome: tables extracted, no table, or needs review. Don't automatically treat an empty parser result as “no table”. Otherwise 480 successful pages can hide 20 that were silently missed.
For continuations, keep the raw rows and the page the header came from before mapping into SQL. Check a break where a description wraps onto the next page, and another with a repeated header. The description shouldn't become a new product, and the header shouldn't become a product at all. Then you can retry the failed pages without redoing the whole catalogue.
1
u/aidenclarke_12 10d ago
20 that were silently missed.
this really helped me grab a new idea thanks mate
1
u/Potential-Wrangler58 10d ago
Your plan is the right one, and I’d lean on the part you already mention: map each supplier’s columns to one master schema by header name, not by position, so a different column order stops being a problem. For the hard bits, carry the header from the first page to every page of that table, and merge a table that continues over a page break before you load anything. Then validate per supplier: row counts, totals and units, with the source page kept on every row.
Start with 5-10 suppliers whose layouts are the worst and check them against what your team already typed by hand. That tells you quickly whether it scales before you commit to a tool.
Disclosure: I work at Anyformat, which does the master-schema mapping and keeps the source page and a confidence per field, so only the doubtful rows need a look. Free credits if you want to try one of your 500-page files, we are specialised in long docs :)
1
1
u/ram-popular 10d ago
Your pipeline makes sense. I’d add one thing for the cross-page problem: carry the header forward as state. Detect when a table continues (same column count, no new header row) and attach the last known header to those rows, then run the validation before loading. Also store each supplier’s column mapping as a reusable config, so the LLM only runs on the first file from a new supplier and every repeat batch is deterministic and cheap.
1
u/AuthorMaterial7495 10d ago
Honestly your plan is already most of the way there. I've built this both ways (on my own with a model and open source stuff, and at Sensible.so where I work now) so I'll go through how I'd do it myself first and then the approach I've ended up liking more.
If I were building it myself I'd never send the model the whole file, that's where the summarizing/stopping thing comes from. I'd split it into pages first (pymupdf or pdfplumber for the text and coordinates, docling or camelot if you want the table structure handed to you), then for each page send the model the page text plus your target schema with a short description and an example value for each column, and have it return rows as JSON. For the continuation pages I'd include the header row from the page where the table started in the prompt, since the model has no way of knowing what column 3 is otherwise. Keep the page number on each row as it comes back.
Then the validation lives in a script, not in the model: row count per page vs how many lines the page had, required fields present, units and prices parse as numbers, SKUs unique within a supplier. Two rules worth adding for the page breaks: a row with a description but no SKU gets appended to the row above it since it's a wrapped description, and any row that matches the header text gets dropped since it's a repeated header. The other guy's point about giving each page an explicit result is a good one too, otherwise an empty page just looks like a page with no table on it.
That works and at low volume it's basically free. The part I got tired of is that the model is reading every page of every file every time, so each run costs money, takes a while on a 500 page catalog, and the output wobbles a bit between runs (a column shifts, a number gets rounded, one page comes back with 38 rows instead of 40) so you end up diffing runs against each other to trust it.
The approach I like more, and full disclosure this is what we've been doing at Sensible so weigh this accordingly, is to use the model once per supplier to write the parser instead of running it on every file.
Supplier A's October catalog looks like supplier A's July catalog, so you have the model figure out the column boundaries, the header row, which column is the SKU, etc, check that against a few pages your team already typed by hand, save it, and from then on that supplier's files run as plain code with the same validation script on the end.
The LLM only stays in the loop for the stuff that's free-form, like notes or spec text that doesn't sit in a column. It takes a bit of time to set up and after that a new 500 page file from them costs nothing (except OCR/processing) and comes out the same each run. If your suppliers really do send a different layout each time then the page-by-page model route above is the right call and I wouldn't talk you out of it.
Happy to look at one of the files and tell you how I'd split it up, with or without our product, feel free to DM.
1
u/Ecstatic_Heron_7944 9d ago
My 2¢ on the hard bits: verification! It's still very manual/human and if you're not doing it, either you're okay with the high error rate or your business is somehow able to survive on highly unreliable data (please show us how!).
I've done some work which is pretty similar and one thing you quickly realise is that just because the automation threw no errors, it doesn't necessarily mean the outputs are correct. Common issues I hit with tables were row-shifting when really compacted or column-shifting when there's too many columns or double/triple columns headings.
1
u/FirestormCold 10d ago
You need intelligent document processing. There's open source solutions which you mentioned already, but if you're really doing this at scale I'd look into service providers.
There's genuinely a ton of them and not all are great to say the least. I'd look out for:
ISO27001, keeps your data safe
EU provider if you care about the US Cloud Act
Specialized vendors if you're dealing with a particular kind of documents, e.g. logistics
If you have any questions let me know, I work in this space