OCR Into Excel When the Page Is a List, Not a Table

You can run OCR into Excel on a page that has no table, but the result is a column of recognized lines, not a finished grid. OCR supplies the text. You decide what the fields are, split each line into columns, and add a column that records which scanned page every row came from.

Much of the paper a law office wants in a spreadsheet was never laid out as one. An exhibit index, a witness list and a stack of cover sheets all hold the makings of rows and columns, and none of them has ruled cells for software to find. The work is different from extracting a table, and so are the mistakes.

Can OCR fill a spreadsheet from a page that has no table?

Yes, with a limit. OCR reads a list or a form as lines of text and can hand them to you in a spreadsheet file. It does not know that one part of a line is a date and the next a description. You get the text, and the columns are yours to make.

What you get depends on the export you choose. Schema Law's format list describes its Excel export as each table on its own sheet, with the rest of the text on another, and its CSV export as the document's tables, or its text line by line with page numbers. For a page with no table, that means the Excel workbook holds the text on its own sheet, and the CSV gives you text with the page number beside it. Excel, CSV and JSON exports are available on every plan, the free one included, and the pricing page sets out each plan's page limits.

Sometimes OCR does more than you expect. A list set in neat aligned columns can be recognized as a table even without ruled lines, and then it arrives already split. The post on getting a scanned PDF into Excel explains how OCR finds a table on a scanned page, and it is the place to go when your page does have one. Look at the parsed result before you plan any manual work. If the columns are already there, you are finished early.

The rest of this post is for the other case: the text came through correctly, in a single column, and you need a structured sheet.

Which legal documents are lists, indexes or forms rather than tables?

Three shapes of legal document hold spreadsheet-ready information without being tables: lists with one entry per line, indexes whose entries wrap over several lines, and forms where each page describes one thing. Each needs a different approach, so name the shape before you start.

Line lists

A witness list is the plain case. Each line has a name, perhaps a role and an address, and the parts come in the same sequence every time. If something consistent sits between the parts, such as a comma, a tab or a dash, splitting is quick.

Wrapped entries

A docket sheet printed from a court's system has a date, an entry number and a description, and the description often runs to three or four lines. The recognized text gives you every printed line separately. Before you can split fields, you have to join the continuation lines to the line that starts the entry. The signal is usually at the start of the line: an entry begins with a date or a number, and a continuation does not.

Forms

A cover sheet is a page of labels and values: "Case No.", "Filed", "Plaintiff", "Nature of Suit". Nothing about it is a list, yet forty cover sheets make a forty-row spreadsheet with one column per label. Checkbox forms are harder, because the answer is a mark in a box and not a word. The post on OCR for scanned forms covers whether OCR can tell a checked box from an empty one; plan to read those answers yourself.

Some documents mix the shapes. A privilege log is usually a true table. The transmittal letter stapled to it is a form. Split the file and treat each part as what it is.

When the stack is a document production, give each row the Bates number, the exhibit number and the custodian. A privilege log row also needs the claim of privilege and the document date. Keep those identifiers as text so a later sort does not drop a leading zero. The sheet then works as a discovery index you can filter by exhibit.

Turning recognized lines of text into spreadsheet columns

To turn lines into columns, find what separates the fields on every line and tell Excel to split there. Where no single character separates them, show Excel the pattern with a few examples. Work in new columns to the right, and leave the recognized line untouched in its own column.

Take five lines from a fictional exhibit index, as they arrive in column B:

Ex. 1 - Master Services Agreement - 03/14/2022 - PELL000112
Ex. 2 - Statement of Work No. 3 - 06/01/2022 - PELL000148
Ex. 3 - Email, R. Okonkwo to D. Pell - 11/09/2022 - PELL000391
Ex. 4 - Notice of Default - 01/23/2023 - PELL000402
Ex. 5 - Wire confirmation - 02/02/2023 - PELL000417

The target is four columns: exhibit number, description, date, starting Bates number.

Splitting on a separator

Excel's Text to Columns wizard splits a column wherever a chosen character appears. Microsoft's instructions for splitting text into columns with the Convert Text to Columns Wizard give the steps: select the cells, choose Data, then Text to Columns, select Delimited, pick the delimiters, and set the Destination where the split data should appear.

1. Copy column B to a spare column, so the original lines survive.
2. Find a separator that appears between fields and nowhere else. A bare hyphen is a poor choice, because it would also cut a hyphenated surname or a Bates prefix such as "PELL-A" in two. Replace " - " (space, hyphen, space) with a character that never occurs in the text, such as |.
3. Run Text to Columns on the copy, choose Delimited, and enter | as the delimiter.
4. Before you finish the wizard, set the Bates and exhibit columns to Text, so nothing is reformatted.
5. Set the Destination to the first empty column.

Showing Excel a pattern

Some lists have no dependable separator. Fields are divided by runs of spaces, or by position alone. Microsoft describes Flash Fill in Excel as a feature that "automatically fills your data when it senses a pattern", started from Data, then Flash Fill, or with Ctrl+E. Type the exhibit number for the first two rows by hand in a new column, press Ctrl+E, and Excel proposes the rest. Repeat for each field.

Flash Fill guesses, and its guesses go wrong on the odd rows: the entry with two dates, the description that contains a number. Scroll the whole filled column once and compare a few rows against the original line.

Joining wrapped entries first

For docket-style text, add a helper column that marks the start of an entry. A formula that tests whether the line begins with a digit is usually enough. Filter on the lines that are not starts, and append each to the entry above it. For twenty entries, do it by hand. For two thousand, it is worth asking someone comfortable with Power Query or a short script.

What Excel changes without asking

Excel reformats values that look like numbers or dates. A Bates number with leading zeros can lose them, and a case number such as "3-22" can become a date. The post on scanned PDF to CSV explains how to open a CSV without Excel changing the data, and the same care applies when you split columns.

One spreadsheet row per document across a stack of scans

To build a log from a stack of scans, decide the columns first, then take the same few values from each document and enter them as one row. OCR makes the values searchable and copyable. It does not choose them for you, and across forty different layouts no formula will either.

Suppose a client delivers a box of sixty pieces of correspondence about a construction dispute, and you need a chronology index. Set the sheet up before opening a single scan:

A workable routine

1. Convert the stack. Upload each scan, or the combined file, and keep the parsed text open in one window and the spreadsheet in another.
2. Go document by document. Find the date, author, recipient and subject line in the recognized text and paste each into its cell. Pasting avoids retyping errors. It also carries recognition errors with it, so glance at the page image as you go.
3. Fill the source columns at once. Do not leave them for the end. You will not remember which file row 37 came from.
4. Mark doubt in the sheet. Add a "Check" column and put a note where a date is illegible or a signature could be two people.
5. Sort only after every row has its ID. Then a sort by date can always be undone by sorting on the ID.

When pages are identical forms

If every page is the same printed form, the values sit in the same place on each, and the recognized text for each page follows the same sequence. That makes the text sheet or the line-by-line CSV more useful. Filter the lines for a label such as "Claim No." and you have that value for every page in a few seconds, each with its page number. Do that for each label, then line the results up by page.

Keep expectations modest for forms filled in by hand. Printed labels read well. Handwritten answers often do not, and the post on OCR for handwritten notes explains why results vary so much from one hand to another. Test five pages before you commit to the method for five hundred.

A source-page column for rows built from recognized text

Every row built from recognized text should say which file and which page it came from. With that column, anyone can open the scan and confirm a value in seconds. Without it, a spreadsheet of sixty rows is sixty assertions that nobody can check without searching the whole stack again.

A table lifted whole from one page carries its own context. Rows assembled from a list, or from many documents, lose it the moment they are sorted or filtered. The reference has to travel inside the row.

What to record

• The file name, exactly as saved, including the extension.
• The page number within that PDF, counted from the first page of the file. This is the number your PDF viewer shows, and it is the one you need to jump to the page.
• The printed page or Bates number, in a separate column, if the page has one. PDF page 14 may be printed page 12 or bear the stamp PELL000125. Keep both and never mix them in one column.
• A page range for entries that run over a page break, written the same way every time, such as 14-15.

If you exported text line by line with page numbers, half of this is already there. Keep the page column through every split, filter and sort. When you break one line into four fields, the page number stays on the row. When you join three wrapped lines into one entry, keep the page of the first line, and note the second page if the entry crosses over.

Using the column

• Verification. A second person can sample every tenth row, open the stated page, and compare. That is a real check, and it takes minutes.
• Citation. When a row ends up supporting a statement in a brief or a declaration, the page reference is already there.
• Correction. If you later replace a poor scan with a better copy, you know exactly which rows to revisit.
• Finding gaps. Sort by file and page. A jump from page 31 to page 34 tells you two pages produced no rows. Either they were blank, or you missed them.

Protect the column from Excel. Format it as Text before pasting, or a range like 3-4 will turn into a date.

When is retyping a short list faster than OCR into Excel?

Retyping wins when the list is short, the scan is poor, or the layout is so irregular that splitting would take longer than typing. A short list of simple entries on a single page is often quicker to key in than to convert, split and verify.

OCR has a fixed cost that typing does not: uploading, exporting, working out the separator, splitting, and repairing the rows that split wrongly. That overhead is the same for ten lines as for a thousand. Typing has no overhead and a steady cost per line. Somewhere the two cross.

Three things shift the balance:

• Risk in the characters. Long numbers are where typists transpose digits. Even for a short list, pasting recognized numbers and checking them can be safer than keying them.
• Whether you want the text anyway. If the document is going through OCR for search or for a Word copy regardless, the text is already there and the fixed cost is paid.
• Who is doing it. Someone fluent with Text to Columns breaks even on a much shorter list than someone opening the wizard for the first time.

Either way, the checking step does not go away. Typed rows need proofreading against the page as much as recognized rows do, and the source-page column earns its place in a typed sheet too.

One more question sits in front of all of this: whether a spreadsheet is what the next person needs. A list that will only be read, or pasted into a pleading, may belong in Word or plain text. The post on legal document conversion sets out how to match the format to the task and the recipient.