Excel and Access databases on the shop floor: what to do before AI

Why shop-floor spreadsheets matter before AI#

Shop-floor spreadsheets matter before AI because they often hold the records that the ERP and MES never captured. Downtime tallies, scrap logs by shift, setup sheets with the real process settings, changeover matrices and supervisor notes frequently live in Excel files on a shared drive or a supervisor's laptop.

These files are where people wrote down why: why the line was stopped, why a setting was changed, why a lot was held. That reasoning is what makes operational records useful for analytics and for AI, and it disappears when a file is overwritten or a supervisor leaves.

Data problems are a common barrier. In RSM's 2026 middle-market survey, data quality and availability issues were the top inhibitor to AI deployment, at 34%, ahead of security and privacy concerns and legacy systems integration. Spreadsheet sprawl is one of the most ordinary causes.

How do you find every spreadsheet and Access file?#

Finding every spreadsheet and Access file takes a file scan plus conversations, because the most important files are often on personal drives. Plan the search the way you would plan a physical inventory: by area, with an owner for each.

• 1Scan file shares and cloud drives for Excel and Access file types, including older formats and macro-enabled workbooks.
• 2Ask each supervisor and shift lead which files they open every day and which ones they would miss.
• 3Check quality, maintenance and planning team folders, where tracking databases often start.
• 4Look for Access front-end files that link to back-end databases on a share, and note both locations.
• 5Find spreadsheets with live links into the ERP or MES, since these are reports rather than records.
• 6Record the owner, last modified date and purpose of each file in one inventory sheet.

Triage table: keep, consolidate, archive or exclude#

Each file gets one triage call based on what it records and who it describes. Keep means it stays live and gets cleaned up. Consolidate means its records move into one master table or into the ERP, MES or QMS. Archive means a read-only copy with a description. Exclude means it stays out of any analytics or outside use.

Consolidation is where most of the effort goes. Three presses may log downtime with three different reason lists, so agree on one code list first, map each old reason to it, and keep the original text in its own column. Never overwrite the operator's words with the new code, since the words often explain more than the code does.

What makes a kept or archived file usable later?#

A kept or archived file is usable later only if someone who never met its author can read it. Shop spreadsheets rely on context: color coding that means on hold, merged header cells, abbreviations only one shift uses, and dates typed several different ways.

Research on dataset documentation makes the same point. Datasheets for Datasets proposes that every dataset be accompanied by a datasheet documenting its motivation, composition, collection process and recommended uses. A plant version can fit on one page.

Write that page while the file's owner is still around. It should name the purpose, the date range, what each column and code means, what the colors mean, who entered data and how, and any known gaps such as a month the log was not kept.

Access databases need extra care#

Access databases need extra care because their value sits partly in the logic, not only the tables. Queries calculate yields, forms enforce which fields are required, and macros or VBA code move records between tables. Exporting only the tables can lose the rules that gave the numbers meaning.

Export each table to an open format, save a copy of the relationships diagram, and list the main queries and what they calculate. Keep a read-only copy of the original file as well. Older Access files can be hard to open on current software, so do this while the person who built the database can still answer questions.

Illustrative: a stamping plant sorts its spreadsheets#

Illustrative: a fictional metal stamping plant runs its ERP for orders and inventory, but its quality history lives in an Access NCR database built by a quality engineer who has since retired. Downtime is tallied on Excel sheets per press, die change times sit in a separate workbook, and the training matrix is a shared spreadsheet.

The COO and IT director run the scan and interviews and find far more files than expected. They consolidate the downtime sheets into one table with a single reason code list, migrate open NCRs into the new QMS and archive the Access database with exported tables and a written description of its queries. The training matrix is excluded.

The result is a short, documented set of records the plant trusts. When the company later considers whether its operating history could support a data license, the inventory already shows what exists, what it covers and what is off limits.

How SourceX looks at spreadsheet records#

SourceX looks at spreadsheets as supporting records that add context to ERP, MES, QMS and maintenance history. The fit check asks only for metadata, such as which systems and file types hold which records and for how many years, and nothing is shared at that stage.

In the SourceX Enterprise Data Value Framework, documented spreadsheets can add human-generated signal and domain expertise, while poor data cleanliness and high preparation cost reduce net value. Triage and documentation are how a plant moves its files toward the right side of that balance.