How to check a PDF extraction is right: count, compare, and list what could not be read

Why "it looks right" is not a check

A PDF stores text at positions, so any extraction rebuilds rows from layout. Rebuilding goes wrong in ordinary ways. A description that wraps onto a second line becomes an extra row with an empty code. Two tight rows are joined into one. A heading repeated at the top of each page is read as data. Columns shift when one cell is empty. The result still looks like a clean table, so a quick scroll finds nothing.

Microsoft's documentation for Excel's PDF connector says rows that spread over several lines may not be identified properly and may need cleaning, and its options combine similar tables on consecutive pages by default. Both facts are reminders that the tool's guess about table structure is something to check, not something to trust.

Check one: count the rows on every page

Count the data rows on each source page, by eye or from item numbers, and write the number down before looking at the extraction. Then count the extracted rows that came from each page. A difference on a page points straight to where to look.

Count per page, not only in total. One missing row on page 2 and one extra row on page 9 give a correct total and two errors. The accompanying worked example shows exactly this case with invented data.

• Keep the source page number on every extracted row so counts can be done by filter.
• Do not count header lines repeated at the top of each page as records.
• A page with no difference has passed this check only; it has not been shown correct.

Check two: compare a sample you chose first

Decide the sample before looking at the results: its size, how rows are picked and which rows must be included, such as the first and last row of each page and rows with long or unusual descriptions. Then compare every field of every sampled row with the document, not just the one that looks risky.

Report the result as counts: for example, rows compared, fields compared, mismatches found and how each was corrected. Do not turn that into a percentage accuracy for the whole file. A sample shows the method works on those rows; it does not certify the rows nobody compared.

• Write the sample size and selection method into the agreement before extraction starts.
• Have the comparison done by someone who did not run the extraction.
• If the sample finds a pattern, such as every wrapped description is wrong, fix the pattern and sample again.

Check three: use any totals the document carries, and test the types

If the document itself has item counts, page totals or a grand total, compare them with the spreadsheet. Check that numeric columns really are numbers, that units and currency symbols stayed in their own columns and that codes with leading zeros kept them. Excel removes leading zeros from numbers it reads as numeric, so a code column should be handled as text from the start.

A column that mixes numbers and text usually means a row was misread. Filter the column for the entries that fail and look at them against the page.

Keep an exceptions list instead of guessing

When a row cannot be read with confidence, do not put a best guess in the data. Move it to a separate exceptions sheet with its page, its position and the raw text, and say why. The person who owns the document then decides each one. Agree beforehand how many exceptions would make the whole job worth stopping and re-scoping.

The one-off extraction jobs for price lists and filled forms use these checks as their acceptance tests, from £295 and £245 respectively, and the monthly service repeats them for each batch. Those prices are untested proposals, payment follows your sign-off, and no job promises accuracy beyond the counts reported. The first enquiry needs only the page count and column headings, never the document.

• The checks cannot tell you whether the source document itself is correct.
• They are not a substitute for your own review of the exceptions.