Moving a spreadsheet-run process into software means deciding, column by column, what the new system takes as data and what it computes itself. The column map is written from the new record outward: what that record holds gets settled first, and the old columns fill in behind. A formula column imported as data becomes a rival account: the new system produces that quantity itself, and within the first month the two have drifted apart.
Most of these conversations open the same way. Here is the sheet, import it as it stands. The request is reasonable: the sheet has been running the business for years and every number in it means something to someone. The difficulty is not that the sheet is wrong. The difficulty is that a sheet is not one kind of object. Three unrelated kinds of column sit side by side in it, and each of the three behaves differently when the work moves into software.
Every column in the export is a question the old tool knew how to ask
Press export and what lands on your desk is not the whole of the work. It contains the questions the old tool knew how to ask, and nothing else. A question the tool never asked does not appear there as an empty column. It does not appear at all.
The dental clinic CRM carries the clearest version of this. Before it, the practice was run through a general-purpose booking tool that recorded only when an appointment was happening. Which step of the patient’s treatment plan the appointment belonged to was not a field it held. So in any export from it, that column is missing rather than blank, and no import setting will change that.
The same clinic also had more than one source. Appointments lived in the calendar, invoices in a separate book, and the month-end figures somewhere else again. Why parts of a business end up running outside the software, in the hand-kept ledger, is a subject of its own. For migration purposes only one consequence matters: the column map draws a set of columns from each source in turn.
That column map is the first document of the move. On the left, every column the old tool holds. On the right, the field of the new record it feeds. Two kinds of gap appear, and both of them are information. A row with nothing on the right is something the old tool never held. A row with nothing on the left is a field of the new record that the old tool has no answer for.
Which end you write the map from changes the result. Start from the old sheet and the old tool’s idea of a record is copied across in silence: one row used to be one appointment, so one record becomes one appointment. The clinic was built the other way round. What a treatment plan holds was decided first, and appointments were attached to steps of that plan. So the map starts from the new record: you write down what a record in the new system holds, then distribute the old columns into its fields.
So the work of a column map is to decide what each column is.
Which columns are typed data and which are calculated results
The first split is between columns someone typed and columns a formula produced. One question sorts them. Does this cell get its value from other cells? If it does, the column is a result.
A result is not imported. On the column map, what goes beside it is not the name of a field but a sentence: this value will be computed from source records.
The construction workforce attendance system shows the paper version of the problem. On the paper timesheet, hours worked was a column like any other. In the new system nobody types that value into a field; it appears when two scans line up. The payment figure behaves the same way: the system does not keep it anywhere of its own, it produces it from the source records. Why a value is derived instead of being entered a second time is the subject of records derived from source versus records entered a second time. For the migration, one consequence is enough: that column has no destination.
The cost of importing a result column is not storage. It is a rival account of the same quantity. The old figure you carried across sits in a record; the new system computes the same quantity from its own sources. The two begin to drift within the first month, and nobody has written down which one a report should print. At that point, which figure the report prints has stopped being a software question.
There is a second thing that travels with a result column, and it is easy to miss. On the paper timesheet the payment calculation rested on lines nobody could verify, so when a worker disputed a figure there was no independent record to point at. Import that column as data and you import the weakness along with the number. The figure now sits inside software, which makes it look firmer, while the paper behind it has not changed at all.
The clinic shows the same rule on the reporting side. The month-end table used to be assembled by hand somewhere outside the booking tool. The migration decision follows directly: the table does not travel, the records that feed it do.
One counter-case matters here. Sometimes a formula column carries a rule rather than a calculation: a rate, a coefficient, a tariff. Then what travels is not the cell but the rule, and a rule enters the system as a setting rather than as a row of data. If the rule varies from file to file or from person to person, it has to be settled before the move. A sheet filled in with two different coefficients cannot arrive in the new system as a single tariff.
Repeating rows collapse into one parent record
The third kind of column looks like plain data on its own. The trouble is not in the column. It is in the repetition of the row.
The catalogue behind the Kokudankokuya store shows both ends of the problem. A standard product model treats each fragrance as a single line and pushes the difference in bottle size down into the description; then the row count is smaller than the number of records the business actually needs. Open a separate product for every size and the list stretches out under repetitions of the same fragrance name; now the row count is larger. What works in the store is neither. Each fragrance is one product record, and each size sits beneath it, priced and stocked on its own.
From that comes a rule for reading sheets. In a block of repeating rows, the columns whose values stay the same from row to row belong to a single parent record; the columns whose values change belong to child records attached to it. The number of rows in your hand is not the number of records you need.
This is a rule for reading a sheet, not a rule for designing a model. It does not tell you where the child record stops, or which difference deserves a record of its own and which is only an attribute. That boundary is drawn by the price-and-stock test for product variants. A column map consumes the answer to that test; it does not produce it.
The same rule runs backwards on an entirely different sheet. On a construction timesheet, what repeats is not a product but a person: the same worker appears on one site’s roster as one line and on another site’s roster as a second. Here repetition does not inflate the list, it splits the person. The steady column of the block is the person; the columns that change are the site and the date. In the new system the block collapses to one person record with site records attached to it, so time on two different sites stays separately visible even after it has been added together.
Where there is no repeating block, this section has nothing to say. The row is already the record, and that part of the map comes out one to one.
What happens to a field the old tool never kept
The hardest line on a column map is the one with nothing on the left: the new record holds a field, and the old tool has no answer for it.
At the clinic that field was the plan step. In the new system, what an appointment points at is not an hour but a numbered stage of a course of treatment, so revising the plan reorders the calendar beneath it. Past appointments have no plan record to attach to, because none was ever kept. The only way to fill that column backwards is to guess, and a guessed value stops being a guess the moment a report treats it as data.
On site the same gap sits on verification. On the paper roster the person writing a line and the person vouching for it were the same person, and at month end there was no independent record of who had been where. In the new system an entry carries, on the record itself, who scanned it and which site it came from. Import the paper lines and you import a set of statements. Verification does not come with them, because it was never produced at the source.
So the decision is taken column by column, and there are three options.
- Carry it over as it stands. Typed data with a field waiting for it in the new record; nothing to do beyond a format conversion.
- Carry the row, leave the field empty. The source has no answer; the record arrives, the field stays blank, and it starts filling on cutover day.
- Do not carry it at all. Neither the row nor the field crosses over.
Cutover day is not really a date. It is the day a particular set of columns starts filling. That difference is not hidden, it is marked on the record: a line inherited from paper reads as a statement, a record created afterwards reads as verified. Without the marker the two sets get added together with equal weight in the same report, and afterwards nobody can separate out how much of that report rests on statements.
The third option is a genuine decision. If past entries feed no future calculation, leaving them behind is a scoping decision rather than data loss. But it is not the software team’s to make; you are the party who knows which history feeds which calculation. Our job is to put the option on the table by name.
Three source systems side by side
Read three different source arrangements against the same three questions and the picture looks like this.
| Old tool and its row | What the record became | Columns that travelled as data | What did not travel: a result, or never held |
|---|---|---|---|
| General-purpose booking tool; a row is an appointment: time and patient. Alongside it an invoice book and a month-end table | A treatment plan broken into sequenced sessions; every appointment attached to a step of the plan | Patient; the date and time of the appointment | Plan step: never held, starts at cutover. Month-end table: a result, read again from the records that did travel |
| Flat catalogue list; a row is a fragrance–size pair, with the fragrance name rewritten for every size | Fragrance as one product record; each size attached to it, carrying its own price and stock | Fragrance name and description once per repeating block; price and stock per size | Repeated fragrance columns: copies, not records. Row count does not travel as product count |
| Paper attendance roster and the payment calculation drawn from it; a row: person, day, site, hours worked | An entry and exit pair: person, site from location, the span between the two scans | Person; date; site, flagged as a statement | Hours and payment amount: results, computed again from the pair. Verification: never held, starts at cutover |
The first column and the last two are migration reasoning; the second column is what each system actually holds today. The fragrance row differs from the other two in one respect, and the difference is worth stating rather than smoothing over: what fails to travel there is not a result but a repeated copy. In the clinic and construction rows, both kinds of non-travelling column appear together: a computed result and a field the source never held. Expect both in your own sheet. If only one of them turns up, the other has probably not been looked for yet.
Why the old total and the new total disagree
The first reconciliation after cutover is usually the tensest day of the project. The total on the old sheet and the total the new system reports do not agree, and the first reading is always the same: something was lost in the import.
The gap has to be split into structural items first, because two of them appear even when the import is correct.
The first is a change in the unit being counted. Move off a catalogue that kept a row per bottle size and the product count falls. No size has been deleted; the repeated header rows have folded into one record, and the sizes now sit beneath it. What the old list counted as a product is, in the new system, one unit of a product. The two numbers share a name without counting the same thing.
The second is the pairing condition. On site, the new system produces hours only where an entry and its exit have found each other; an entry whose counterpart has not arrived does not turn into a number. The paper roster converted every line straight into hours. The first month’s total therefore comes out lower, and what dropped is not lost time but time with no matched record behind it. How an entry with no exit is handled on site is a separate subject; for reconciliation, the only thing that matters is that such an entry stays out of the total.
The reconciliation itself has to change method as well. Counting a new system in the old system’s unit will always report a mismatch. The comparison is made on record counts and source pairs rather than row counts and sheet totals: how many parent records, how many units attached to each, how many pairs closed, how many entries still open.
Once those two are taken out, what remains is not structural. It is a real import error, and the place to look is the source records rather than the result column: a column mapped into the wrong field, a date broken in format conversion, a repeating block cut in the wrong place. The correction is applied to the source record, not to the total, and the total settles by itself. No import can be called correct until the explainable part of the gap has been separated from the rest.
How to bring the sheet to the first meeting
What usually arrives at the first meeting is a summary of the sheet, and a summary deletes exactly the things the decisions depend on. Formulas disappear into their results, repeating blocks flatten out, and fields that were never held were never going to be written down in the first place.
Bring the sheet itself, with three marks on it.
- Leave the formula cells visible. The sheet will tell you which columns are calculations; the column headings will not.
- Mark the repeating row blocks. Where a block starts and ends draws the boundary of the parent record directly.
- Keep a separate list of the fields the old tool never held. That list is not in the sheet. It is in your head, and unless it is written down it will not come up in the meeting at all.
Those three marks hand over three migration decisions. Beside a visible formula column goes “computed from source”. A marked repeating block splits into a parent record and its children. The list of missing fields sets the cutover date. The column map becomes writable in the first meeting, and what carries into the second meeting is a list of decisions.
One boundary is worth drawing. How a record comes into existence in the field after cutover is not this article’s subject; who opens it, where, and at what moment belongs to business process tracking. The column map ends there.
And one counter-case. Not every sheet needs migration decisions. If the row already matches the record the business runs on, and no column comes out of a formula, the map comes out one to one, what travels is a copy, and this article has nothing to say about that sheet.
Bring the sheet itself, not a summary of it: formulas visible, repeating row groups marked, and a separate list of what your old tool never recorded. The management software page shows what changes when the rule lives in the record rather than in a column of totals.
