100 Excel Files: What Did the Automation Actually Handle?

“How can I extract specific data from more than 100 Excel files and combine it into one table when the worksheets and required fields differ from file to file?”

Here is the result first. Of 100 synthetic Excel files created for this test, the program selected one value and returned OK for 83, while sending 17 to a review queue. A later comparison with a table of expected answers (the answer key) showed that all 83 OK results matched. One distinction matters: an AI model did not read and interpret every file each time the program ran. AI helped create a fixed-rule Python program, and that program ran locally across the files.

Here is how the three numbers relate.

The 17 review cases break down into 5 ambiguous files + 4 files with no target value + 8 missed valid values. In real work there is usually no answer key, so the last two groups cannot be distinguished before someone checks the source file. That is the central limitation of this experiment.

Why 17 files required review

Of the 100 synthetic reports, 91 contained one valid target value. Four intentionally contained no target value. Five contained two different values for the same target label—the item name printed next to a value—so the correct choice was deliberately ambiguous.

Comparing the final program's output with the answer key produced this breakdown. The revision process appears in the next section.

Looking at the output alone, all 12 files in those two rows were simply MISSING. Only the answer key revealed that four truly had no value while eight were detection failures. A person would have to open the original reports to make that distinction in real work.

AI created the rules; it did not interpret every file while the program ran

The initial request to AI was simple: the workbooks and worksheets vary, so use sheet names or keywords to locate the value required for each report type. For the experiment, I added requirements to preserve the originals, record the source filename, worksheet, label cell and value cell, and produce a consolidated table and dashboard.

I sent five synthetic example files and this request to Claude's fable model. Claude produced a program that classified sales, budget and project reports, then searched for predefined sheet-name and label patterns. It was not a program that called an external AI model for each workbook.

The program handled all five example files correctly. Across all 100 files, however, the first version found only 60 of the 91 valid target values. All 60 accepted values matched the answer key, but it missed 31. Its initial detection coverage was therefore 60/91, about 65.9%.

I sent three missed examples back to Claude for a correction, but the response did not finish and no revised program came back. Another AI coding tool, Codex, then broadened the rules so that Korean label variants such as those meaning “total results,” “approved amount,” and “cumulative spending” were treated as the same target item, and added the requested charts. The change did not hard-code the three filenames, cell positions or amounts.

Codex's final revision found 83 of the 91 valid values. The resulting 83/91, about 91.2%, is the share of files with a valid value that the program found. It is not a percentage of all 100 files. The first five examples and the three files inspected during correction were themselves part of that 100-file set, so this was not a score measured on a separate test set.

Looking only at the 83 files the program accepted, all 83 matched the synthetic answer key. In other words, the program did not present a wrong value as correct in this test. That result does not guarantee error-free performance on different real reports.

Source tracking and review mattered more than the 0.73-second run

The final program processed all 100 files in 0.73 seconds using openpyxl, a Python component for reading and writing Excel files, in a Python environment that was already set up. It made zero external AI calls while running and did not modify the source workbooks.

That 0.73 seconds covers only a repeat run of an already-created program. I did not consistently measure the time spent designing the automation, preparing examples, waiting on Claude's incomplete correction attempt, revising the code and validating the output. No one timed a person processing the same 100 files by hand, so this experiment cannot support a speed multiplier or break-even estimate.

The 83 accepted files should not be treated as automatically trustworthy. They all matched this experiment's answer key, but a business total may still require sampling or checking every source, depending on the consequence of an error. The consolidated table therefore retained the source filename, worksheet, label cell and value cell for each accepted value. If the total matters, do not stop at the 17 review cases. Sample or fully check the sources of the 83 accepted values as well.

A safer way to try this workflow

You do not have to explain every exception to AI before starting. A smaller set of representative files and expected outputs can establish the first rules, provided that the workflow includes explicit safeguards.

1. Begin with synthetic or de-identified examples rather than company files.
2. Choose a small set that represents the most common report types and show the desired output.
3. Require every extracted value to retain its source file, worksheet, label and value cell.
4. Route conflicting candidates and undetected values to review instead of guessing.
5. Test on files that were not used as examples, using a fixed answer key to measure both missed values and wrong extractions.
6. Check organizational data-handling rules before sending company files or review lists built from them to an external AI service.
7. Before running generated code, check that it will not overwrite the originals or access files outside the intended work folder, then run it on copies first.

Fixed rules need to be retested when report formats change. I did not test adding new reports and refreshing the consolidated table and dashboard with the same program.

What this experiment did not establish

Another design considered at the start was to have AI interpret each workbook on every run. I did not test that approach because of its potential cost, so its accuracy, cost and security burden remain unknown.

The experiment covered exactly 100 files, not more than 100. Microsoft Excel was unavailable in the test environment; I opened the output in Apple's Numbers app and inspected the workbook structure, so full Excel compatibility was not verified. I also did not test whether a non-developer could independently install, run and troubleshoot the program.

Excel has other automation options. According to Microsoft's documentation, Power Query can reapply recorded data-transformation steps when a query is refreshed. Office Scripts can record repeated actions or write them as code and run them again, subject to account, license, administrator and platform requirements. I did not run either option or compare it with this Python program.

Conclusion: the goal was not to eliminate review

The revised fixed-rule program automatically accepted 83 of 100 synthetic files and routed 17 to review. Among the 91 files that contained a valid target value, it found 83 and missed 8. Perfect results on five examples did not predict performance across 100, and the rules still missed unseen label variants.

The useful outcome was not having AI handle everything on its own. It was a repeatable consolidation process that kept a record of where each value came from and made uncertain or undetected cases visible. A practical first step is to validate the workflow on synthetic examples, keep the original workbooks unchanged, and make sure every result can be traced back to its source cell.

AI was used to assist with research and drafting. The author independently verified and edited the final article.