Scanned PDF to CSV: Rows a Review Tool Can Load

To convert a scanned PDF to CSV, run OCR that recognizes tables, then export the rows as comma-separated text. The result is a small plain file that case management systems, review tools, databases and spreadsheets can all import. Most of the work lies in checking the rows and importing them without the receiving program altering the values.

A CSV looks trivial, and that is why it causes trouble. It has no way to say "this column is text" or "this file is in UTF-8", so every program that opens it guesses. This guide covers the choices and the guesses.

When should you choose CSV over an Excel workbook?

Choose CSV when another program will import the rows, and choose an Excel workbook when a person will work in the data. CSV is the format importers ask for because it carries nothing but values. A workbook carries sheets, column widths, formatting and formulas, which people want and importers ignore or reject.

The last two rows matter more than they seem. Because a CSV is plain text, you can open it in a text editor and see exactly what is in it, with nothing reformatted. You can also compare two exports line by line to see what changed after a rescan.

Pick the workbook when you plan to sort, total or annotate. The Schema Law Excel export puts each table on its own sheet and labels it with the page it came from, which is easier to work in than a single stacked file. The guide to getting tables out of a scanned PDF and into Excel covers that route.

One caution on the phrase "load file". A review platform's load file is often comma or delimiter separated, but it has required fields and a set layout that a table export does not have. The post on creating a load file from scanned PDFs explains what has to be added.

Converting a scanned PDF to CSV

Converting a scanned PDF to CSV takes an upload, a look at the parsed tables, and one export. In Schema Law the CSV contains the document's tables when it has any. When it has none, the CSV contains the document's text, one line per row, with the page number beside it.

1. Check the scan is worth converting. Open the PDF and zoom in on the table. If you cannot read a digit, recognition cannot either. A cleaner copy saves more time than any later fix.
2. Upload the file. Schema Law takes PDF, PNG and JPEG files up to 20 MB through the browser, after you sign in with Google.
3. Read the parsed pages. Find each table on screen and compare its column count and its first and last rows with the scan. This takes a minute and catches the worst failures.
4. Export CSV. CSV is one of the eight export formats on every plan, the free one included. The pricing page sets out how many pages each plan covers.
5. Open the file in a text editor first. Notepad or TextEdit will do. Confirm the header row, count the commas on a couple of lines, and look for the blank lines that separate tables.
6. Import it, do not double-click it. The last section explains why.

How the software decides where a table starts and where each column ends is its own subject. The Excel post covers how OCR finds a table on a scanned page, and the same detection feeds the CSV.

What goes wrong when a CSV is imported?

CSV imports fail for a short list of reasons: the wrong character encoding, the wrong delimiter, rows with uneven numbers of cells, a header the importer does not expect, and values the importer converts on its own. Most can be spotted in a text editor before the import is run.

Encoding

The Schema Law CSV is UTF-8 and begins with a byte order mark, an invisible marker that tells Excel to read the file as UTF-8. Most importers handle the mark. A few older ones show three stray characters in front of the first header name. If your first column heading looks corrupted and the rest are fine, that mark is the cause, and re-saving the file as "UTF-8 without BOM" in a text editor removes it.

Delimiters by region

On computers set to a region that writes decimals with a comma, spreadsheet programs often expect semicolons between values. A comma-separated file then opens as a single column. Nothing is wrong with the file. Use the import dialog and choose comma.

Recognition errors

No import setting repairs a misread digit. A CSV full of amounts deserves a total check: add the column and compare the sum with the total printed on the scanned page. If they differ, find the row. The post on OCR errors describes proofreading OCR output against the original in more depth.

Commas, quotes and line breaks inside cells

A cell that contains a comma, a double quote or a line break is wrapped in double quotes, and any double quote inside it is doubled. That convention is what lets "Dunmore Freight, Inc." stay in one cell instead of splitting at its comma.

The convention is written down in RFC 4180, which describes the common CSV format. It says fields containing line breaks, double quotes and commas should be enclosed in double quotes, and that a double quote inside a quoted field must be escaped by preceding it with another double quote. The memo is informational, not a formal standard, so programs do vary. The export described here follows these rules and ends each record with the CRLF line break the memo describes.

Here is a short example with invented entries:

Date,Payee,Memo,Amount
03/04/2021,"Harlow Properties LLC, Landlord",April rent,"18,450.00"
03/09/2021,First County Bank,"Wire fee, ""expedited""",25.00
03/15/2021,Dunmore Freight,Refund,-310.00

Read it the way an importer does:

• The second line has four cells. The payee contains a comma, so it is quoted. So is the amount, because 18,450.00 has a thousands comma.
• In the third line, the memo contains both a comma and quotation marks. The whole cell is quoted and each inner quotation mark appears twice.
• The last line needs no quotes at all.

Legal documents trigger this constantly. Company names with "Inc." after a comma, dollar amounts over 999, addresses, and "Last, First" names all contain commas. A file that handled them wrongly would shift every later cell in the row one place to the right.

A production export hits the same quoting rules. Custodian names written last name first, Bates ranges, and exhibit titles that contain commas all need the wrapping quotes. Keep the production number and the exhibit number in their own columns so a review tool can load the rows and still point back to the discovery set.

Two mistakes to avoid when you edit a CSV by hand:

• Do not use find and replace to remove quotation marks. They are holding cells together.
• Do not paste in curly quotes as wrappers. Only the straight double quote works as the wrapper. A curly quote from the original document is ordinary cell content and is safe where it is.

If a count of commas on one line differs from the header line, look for a quoted cell before assuming the row is broken. Commas inside quotes do not count.

One table per file or one file per document

Schema Law writes one CSV per document. Every table in the document goes into that one file, in page order, with a blank line between tables. Many importers want exactly one table per file, so a document with several tables usually needs splitting before import.

Take a twelve-page statement with a transaction table on each of ten pages and a summary table on the last. The export is one file holding eleven grids. Each grid starts with its own first row, which is normally the column headings as they were printed on that page. Within a grid, short rows are padded with empty cells so every row has the same number of cells. Different grids can have different widths.

To split the file, open it in a text editor and cut at each blank line, or have a short script do it. Then decide what the importer needs:

• Tables with the same columns, such as a ledger continued over several pages: join them into one file and delete the repeated header rows. Check the row at each page break, where a split or duplicated row is most likely.
• Tables with different columns, such as transactions and a summary: keep them as separate files, named for their page.

The CSV does not record which page each table came from. If you need that, export the Excel workbook as well, where each sheet name carries the page number, or use the JSON export, in which each table sits inside its page. The post on scanned PDF to JSON shows that structure.

A document with no tables at all produces a different file: two columns headed page and text, one row for each line of text. That layout is handy for loading a document's text into a database or filtering by page in a spreadsheet. It is also the sign that a table you expected was not detected as a table. If you see page,text at the top of a file you thought would be a ledger, go back to the parsed pages and look at how the table was read.

Opening a CSV without Excel changing the data

Double-clicking a CSV lets Excel guess the type of every column, and its guesses alter legal data. Import the file instead and set identifier columns to text. Microsoft's guide to keeping leading zeros and large numbers confirms that Excel automatically removes leading zeros and converts large numbers to scientific notation.

What that does to a law firm's data:

• Leading zeros vanish. Account number 00417732 becomes 417732. So does a ZIP code such as 02139 and a document number such as 000123.
• Long numbers are rounded. The same Microsoft page gives Excel a maximum precision of 15 significant digits. A 16-digit card or account number loses its final digit to a zero.
• Text turns into dates. A unit such as 3-4 or a code such as 1/2 may be shown as a date.
• Amounts are restyled. A figure in brackets such as (310.00) is typically turned into the number -310, and 18,450.00 loses its comma and trailing zeros until you format the column. The value survives, but the cell no longer matches the page character for character.

The safe route, following the steps on that Microsoft page:

1. In Excel, open a blank workbook.
2. On the Data tab, choose From Text/CSV and select the file.
3. Choose to edit the data before loading, which opens the query editor.
4. Select each column that holds identifiers and set its data type to Text.
5. Load the data into the worksheet.

Saving is the second trap. If you open a CSV by double-clicking, tidy one cell and save, Excel writes its converted values back to the file. The zeros are then gone from the CSV itself. Keep the exported file untouched and work on a copy.

One behavior of the exported file is deliberate. A cell that begins with = or @ is written with an apostrophe in front of it. Spreadsheet programs treat such cells as formulas, and a formula arriving from a scanned document of unknown origin is a risk. If the receiving system is not a spreadsheet and you see a stray apostrophe before an email handle or an equals sign, that is where it came from, and it is safe to remove on import.

If you only want to look at the rows, do not use Excel at all. A text editor shows the file exactly as it is. For anything destined for a review tool or a case management system, send the untouched export. The export formats section of the homepage lists the other files available from the same upload.