Guide
Checking a PDF to CSV conversion before you trust it
Updated
Extraction rarely fails loudly. It produces a file that opens, looks like a table and is wrong in a few places, which is considerably more dangerous than an error message.
Question 1
Open the PDF and try to select a line of text. What happens?
This one test separates the three fundamentally different cases, and it takes five seconds. Everything downstream depends on the answer.
Email me this result
We will send you these figures and a link back to them. One email, nothing else unless you tick the box.
Count the rows
Against the source, including any that wrapped onto a second line in the PDF. Wrapped rows becoming two records is the most common single defect.
Where a table runs across pages, check that repeated header rows have not arrived as data in the middle of the file.
Count the fields per row
Almost every whitespace-aligned table defect shows up as a row with fewer fields than the rest, caused by a value wide enough to close the gap between two columns.
Sorting by field count, or filtering for rows that do not match, finds them in seconds and is the highest-value single check on this page.
Check that numbers are numbers
A column that sorts alphabetically is a column of text. Currency symbols, thousands separators and trailing spaces all cause it.
Sum the numeric columns and compare against any total in the source. This catches both the type problem and the missing row problem at once.
Check the first row and the last
The header may have arrived as data. The last row may be a total masquerading as a transaction, or may be missing entirely because of an off-by-one in a range.
Both ends fail more often than the middle, and both are checked by looking.
Spot check five records from the middle
Against the original, by eye. Chosen from the middle, because the beginning is what was visible while the settings were chosen.
Five records takes two minutes and is the only check that catches reading-order errors, which are complete, plausible and in the wrong sequence.