Excel, Word, Photos, Scans: Reading File Formats Correctly

Invoices, purchase orders, and delivery slips rarely come in the form of a clean PDF. Sometimes a supplier sends an Excel spreadsheet, sometimes a Word document, and sometimes someone takes a photo of the delivery slip with their cell phone. For the automatic data extraction Does that make a big difference? After all, each file format has its own pitfalls.

In this post, we’ll use an example to show where these pitfalls lie and how Konfuzio handles them. To do this, we’ve recreated a fictional procurement process involving four documents: a quote as a Word file, a purchase order as an Excel spreadsheet, a delivery note as a cell phone photo, and an invoice as a scan.

Here's how to process different file formats

Konfuzio reads every document the way a human would see it—as a page. With a PDF or a photo, this is easy, since both are already a single page.

It’s different with an Excel or Word file. It contains only the content and formatting information. What you see on the screen is a preview generated by Excel or Word. You won’t know what the page will look like when printed or saved as a PDF until you convert the file, and the result may differ significantly from the on-screen preview.

A tried-and-true method is therefore to first convert each file into a page and then read it. In our example, it looks like this:

• Conversion: Word and Excel files are automatically converted to PDF. Neither Excel nor Word needs to be installed for this.
• Text recognition: Text recognition, or OCR (Optical Character Recognition) for short, reads each page and keeps track of where each word appears on the page.
• Extraction: An AI fills in the fields of the extractor—that is, the template—with the desired information. Each value has a source that is highlighted on the page.

Of course, you could also read the cells of an Excel spreadsheet directly. However, using the page has two advantages: Each value can be verified on the page, and the same fields and validation rules apply to all file formats. Our article explains how the reference is generated Document Extraction Using LLMs. The catch: Whatever is lost during the conversion can no longer be read by anyone later on.

Here's an example: four documents, four file formats

Our example involves the procurement of heating equipment for a construction project. The companies, names, and numbers are fictitious. The invoice has six line items and a total amount of €56,756.10.

The extractor looks for the same information in every document: document type, number, date, sender, recipient, amounts, and the individual line items. Two validation rules check the totals. The total amount must equal the sum of the net amount and sales tax, and the line items must add up to the net amount.

In all four documents, Konfuzio read the data completely and accurately, and both validation rules were met. The fact that this works with such diverse files is due to what happens before the data is read. This is most evident in Excel.

Excel: When Diamonds Appear Instead of Numbers

If a column is too narrow, Excel displays only hash marks (###) instead of the numbers. Microsoft explains this in „Correcting an error value of type #####“: The diamonds appear „when a column isn't wide enough to display the entire cell contents.“ On screen, this is hardly noticeable, because when you click on the cell, you see the value in the formula bar. In a printout or PDF, however, it is missing.

Like many everyday spreadsheets, our sample order form has columns that are too narrow. If you convert it to a PDF using a standard office program without any preparation, only hash marks will appear in the order date, the five prices, the net amount, the total amount, and all six delivery dates. The 12-digit manufacturer numbers each become „7.315E+11,“ and the remaining amounts appear in the American number format.

The order date, net amount, and total amount can no longer be extracted from this PDF because the values simply aren't on the page. Konfuzio leaves these fields blank instead of guessing the values, and at least one validation rule is not met. So the error is noticeable, but it hasn't been fixed.

Print Numbers in Full

Before Konfuzio converts an Excel file, every cell containing a number or date is formatted to „Fit to cell size.“ If the value fits within the column, nothing changes. If it does not fit, it is scaled down rather than replaced with asterisks. Column widths, page numbers, and layout remain unchanged, and the conversion uses the German number format.

This way, all amounts, dates, and the manufacturer number 731500100245 for that same order are displayed in full on the page, and Konfuzio reads them without any errors.

Wide tables, hidden sheets, and print ranges

A second problem is the width. Our delivery schedule has 16 columns and doesn't even fit on an A4 page in landscape orientation. When converted directly, calendar weeks 43 through 49 end up on a separate third page, without item numbers or descriptions. On their own, these numbers are meaningless.

That’s why Konfuzio measures the width of each worksheet before conversion and selects the appropriate page layout: A4 portrait, A4 landscape, or landscape with the table scaled to fit the page width—as long as the font size remains at least half its original size. Even wider tables are spread across multiple pages in landscape orientation. Each page bears the name of its worksheet; in the document view, for example, „Page 2 · Delivery Schedule.“.

If someone has specified a print area, page breaks, or landscape orientation, Konfuzio adopts these settings. Form templates, in particular, often contain placeholder lines outside the print area. If everything were printed, a few pages would turn into hundreds. However, values outside the print area would then be missing from the final output. If no paper size is specified, Konfuzio defaults to A4; otherwise, the American Letter format would be used.

Hidden worksheets are not included in the printout during conversion. Our order contains a hidden sheet titled „Calculation“ with purchase prices and margins, which does not appear in the exported text. This is an advantage for internal calculations. However, if the data you’re looking for is on a hidden sheet, you’ll need to unhide it before uploading.

Word: The layout is created only when the document is opened

A Word file stores text, tables, and formatting. The program calculates where a line breaks and how many pages are created, and this depends on the font.

Our sample text is set in Calibri, a font that is not available on Linux servers. Konfuzio uses Carlito and the Liberation fonts on those servers. Carlito has the same character widths as Calibri, and the Liberation fonts have the same character widths as Arial and Times New Roman, so the text wraps at the same points as in the original.

Word files can also embed images from the Internet that are not loaded until the file is opened. On a server, this poses a security risk, as a malicious file could trick the server into accessing addresses on the internal network. That’s why Konfuzio converts every file in a dedicated, isolated environment, removing macros and any downloaded content.

Password-protected files cannot be opened. In that case, a message appears asking you to unlock the file or upload it as a PDF.

Photos and scans: images only, no text

For text recognition, photos and scans are both considered images. In the photo, our delivery slip is lying slightly askew on a table, and the lighting is uneven. The invoice is a grayscale scan without a text layer—that is, without the invisible text that digital PDFs contain. Both images are staged. By the way, if you capture documents with your cell phone, you can also save them directly as PDFs, for example when Scanning with the iPhone.

Konfuzio correctly recognizes all information in both documents, including quantities such as „2,400 m“ and the item numbers for all line items.

Our photo, however, is a simple case: a smooth surface, only slightly tilted. Crumpled documents, shadows on the text, or very small images are much more difficult to handle. Our guide describes which scan settings are appropriate—for example, regarding resolution— A Practical Guide to Scanning.

What Konfuzio Does for Each File Format

Whether it's an order as an Excel spreadsheet, a quote as a Word document, or a delivery slip as a photo: You'll see the same fields, the same validation rules, and the source reference for each value. In the document view, you'll see exactly the page that was imported.

There are three things that Konfuzio does not yet handle separately: tracked changes and comments in Word, and linked cells in Excel. They appear as they are displayed during conversion.

Checklist: How to Test an Extraction Solution Using Your Own Files

A demo PDF won't show you whether a solution can handle your file formats. Try it out with files that are just as messy as your real ones:

1. An Excel file in which the date and total amount are in columns that are too narrow.
2. A long number, such as a 12-digit part number, that is formatted as a number rather than text.
3. A spreadsheet that doesn't fit on an A4 page, even in landscape orientation.
4. A hidden worksheet containing confidential values that should not appear in the results.
5. A Word document in Calibri with a table.
6. A cell phone photo and a scan without a text layer.

Don't just look at the result; also take a look at the page that was read. There, you'll immediately see if a value was lost during the conversion.

Conclusion: Good extraction begins before harvesting

When dealing with mixed file formats, the quality of the extraction is determined before the first row is read. An Excel spreadsheet with columns that are too narrow loses its amounts during conversion; with proper preparation, they are preserved. In our example, Konfuzio successfully extracted the entire contents of the quote, delivery note, and invoice because each file was formatted as a clearly readable page.

Would you like to know how Konfuzio handles your most challenging Excel and Word files? Contact us and please include two or three examples.