Files and Spreadsheets
Reading Excel files with the connector — and an honest case for using manual entry instead.
Infohub's Microsoft Excel connector reads values out of spreadsheet files. It works, and it is frequently the wrong tool for the job — so this article covers both when to use it and what to use instead.
When a spreadsheet is the right source#
Use the Excel connector when the spreadsheet is a system output you do not control:
- A report another application writes to a folder on a schedule
- An extract from a system with no API and no accessible database
- A file a supplier or laboratory delivers regularly, in a stable format
The common factor is that the file is generated, its format is stable, and you cannot reasonably get the data another way.
When it is the wrong source#
Reach for something else when the spreadsheet is maintained by a person.
A file that someone opens, types into, and saves will eventually be renamed, moved to a different folder, restructured with a helpful new column, left open and locked at exactly the moment Infohub reads it, or replaced by "Copy of" a version. Each of those breaks retrieval, most of them silently.
For data that originates with a person, manual entry measures with a form are better in every respect that matters:
| Spreadsheet | Manual entry | |
|---|---|---|
| Where the value lives | A file that can move or be locked | The Infohub database |
| Validation | None | Limits flag implausible values on entry |
| Audit | Whatever the file system records | In the model, per period |
| Categories | Free text, spelled four ways | Enumerations |
| Backup | A separate concern | Covered by the database backup |
| Breaks when a column is inserted | Yes | Not applicable |
The conversation worth having. When someone asks for a spreadsheet to be read, ask where the numbers come from. If the answer is "the shift supervisor writes them down", the right answer is a form, not a file. It is usually less work overall and it stops a recurring class of support call.
Configuring the connector#
The Excel connector's configuration is small. You point it at a file and describe where the values are.
Practical points that determine whether it keeps working:
Use a stable, fully-qualified path. A UNC path to a share is normal. Avoid mapped drive letters — they are per-user and the service account will not have them.
The service account needs access. The connection runs as the Flow Bootstrap service account once deployed, not as you. A path you can read from your workstation is not evidence the service can read it. This is the single most common failure with file-based sources.
Anchor to something stable in the sheet. If the layout allows referencing by a named range or a header rather than an absolute cell, prefer it. An absolute cell reference breaks the first time someone inserts a row.
The file-locking problem#
Excel takes an exclusive lock when a file is open. If Infohub reads while someone has it open, retrieval fails.
There is no configuration that fixes this — it is how Excel works. Mitigations, in order of effectiveness:
- Have the producing system write to a folder nobody browses, and read from there. Humans cannot lock a file they never open.
- Copy before reading. A scheduled copy to a staging folder means Infohub reads the copy and never contends with a user.
- Read outside working hours, if the data only needs to be daily.
- Accept intermittent failures and guard the calculations that depend on the data.
Option 2 is the pragmatic favourite: a copy is cheap, and it also gives you a stable snapshot rather than a file changing under you mid-read.
Handling format drift#
Spreadsheet formats change without notice, because nobody thinks of a spreadsheet as an interface.
The realistic defences are limited but worth doing:
- Put a limit on the resulting measure. A value far outside its plausible range is the fastest signal that a column moved and Infohub is now reading the wrong one.
- Cross-check against something. If the spreadsheet reports daily production and a tag also counts it, a calculated variance between them catches drift immediately.
- Tell whoever maintains the file that it is an interface. Often nobody has ever mentioned it. A single conversation prevents most restructuring surprises.
A wrong column is worse than no data. A missing file produces a visible gap. A shifted column produces plausible numbers from the wrong place, which get reported and believed. Where a spreadsheet feeds anything consequential, put a limit or a cross-check on it — a gap is recoverable, a quietly wrong figure may not be.
CSV#
Note that Infohub's CSV capability sits on the consumer side — writing data out — rather than as a data source. For reading delimited files, the Excel connector is the file-based option.
Where you have CSV files to ingest regularly and the volume or reliability matters, loading them into a database and reading that with a SQL connector is usually the more robust route. It gives you a place to validate on load, a record of what was loaded and when, and a source that does not depend on a file being where it was last week.
Diagnosing#
| Symptom | Check |
|---|---|
| Cannot find the file when deployed | Service account access; mapped drive letter instead of a UNC path |
| Intermittent failures during the day | Someone has the file open — copy to a staging folder and read that |
| Values suddenly implausible | A row or column was inserted; the reference now points elsewhere |
| Values stopped changing | The producing system stopped writing, or is writing to a new name |
| Worked from Flow Config, fails deployed | Runs as you in Config, as the Bootstrap account when deployed |
| File found but no values | Sheet name or cell reference; confirm against the actual current file |