The thing everybody depends on

Most small businesses do not have a database. They have a spreadsheet - one file, usually on one person's laptop, containing the thing the whole operation runs on. Orders. Students. Members. Inventory. The file is not a record of the business; the file is the business, and the person who understands its formulas is the person who understands the company.

The request is always the same when this comes up: turn it into an application. The naive reading is "make a web app with the same columns". That is the reading that produces software which is slightly worse than the spreadsheet, because the spreadsheet had rules in it and the app does not.

Where spreadsheets break

A spreadsheet stores data in a way that is remarkably forgiving, and each forgiveness is a trap for the person importing it.

Merged cells. A heading spanning three columns is not a heading, it is a layout instruction. It means "the columns under me belong to the same group", and the group is not named anywhere except visually. An import has to decide: is the group a field, a category, or nothing at all?

Columns that mean different things in different rows. The "Amount" column might hold a number in one row, a range in another, and text like "negotiated" in a third. In a spreadsheet this is fine - a human reads it. In a database, one column has one type, and the first row that breaks the type breaks the import.

Formulas that are really business rules. This is the important one. A cell containing =IF(D2>0, D2*0.05, 0) is not a computed value, it is a policy: five percent above some threshold. If the import copies the computed number, the rule is now invisible and nobody can change it without the developer. If the import copies the formula, the rule is now a string in a code field, and it has to be parsed, versioned and kept safe.

The empty row that means something. A blank row often means "end of this section". A blank cell often means "same as above". Both are conventions of a visual medium, and both have to be decided deliberately.

The approach that worked

The rule I settled on: map the workbook, do not migrate it.

The first deliverable is not an application. It is a model - a document that says, in a table, what each sheet means and what each column means, including the columns whose meaning varies. Written that way, the person who owns the workbook can read it and correct it, and the corrections are the point.

Concretely, the mapping asks four questions per column:

1. What is this called, in the owner's words? Not the header string - the business term. "Amount" might be "settled amount", "invoiced value" or "what we agreed", and those are three different fields.
2. What type is it, really? A date, a currency, a quantity, a free-text note, a choice from a list.
3. What does empty mean? Unrecorded, zero, or the same as the row above. These have to be different columns in the model, because they behave differently.
4. What rule is behind it? If the sheet computes it, the rule is captured as a rule, with the threshold, the version and the person who set it - not as a pasted formula.

Then the workbook becomes a source, and the application becomes the place where the rules live. A scheduled import reads the sheet, applies the mapping, and writes rows that are already normalised - so the application never has to cope with a merged cell again.

The part that took the most work

Reading a workbook robustly is a small problem with a surprising number of edges.

• The header is rarely in row one. It is often in row one of a table that starts on row six, after a title, a date and a logo.
• A column can appear twice - two blocks of the same sheet, side by side, with different headers. The importer has to treat them as two tables, not one.
• A number can be stored as text, especially if someone typed a currency symbol in front of it. A date can be stored as a serial number, or as a string in three different formats.
• Merged cells mean the value is only in the top-left cell of the merge, and the cells under it are empty - so the importer has to carry the last value forward only where the sheet's convention says to, which the mapping records explicitly.

The rule I used for all of these is the same: the importer never guesses. It reads a configuration, it applies it strictly, and when it meets something it does not recognise it records a warning against that cell and moves on. A silent guess is how a spreadsheet's rules get quietly rewritten by an import nobody checked.

What the application should actually do

The mistake to avoid is treating the import as the product. The product is the rules the spreadsheet was pretending to hold.

Once the model exists, the application can do the things a spreadsheet genuinely cannot: enforce a uniqueness rule, show several views of the same data, notify the people who need to know, keep an audit trail, and give one person the ability to change a rule without asking anyone else to open a file and edit a formula.

The spreadsheet does not stop being useful - it usually stays as the export. But it stops being the place where the rules live, and that is the entire point of the exercise.

The case study for this is Excelite, which takes a workbook that a business runs on and turns it into an application, with the mapping between the two kept explicit rather than implied.