Import, export and search

The usual story of a failed migration is not that the new system was wrong. It is that someone imported a spreadsheet, the import half worked, and the organisation was left with a database containing four hundred correct records, ninety wrong ones and no reliable way to tell which was which.

At that point there are two options, both bad. Spend a day picking through it by hand, or delete everything and start again, having lost confidence in the process. What actually happens in most organisations is a third thing: the new system is quietly abandoned, the spreadsheet survives another year, and everyone agrees to look at it again in the spring.

The root cause is that the import wrote before anyone could see what it was going to do. Every problem downstream follows from that single decision.

A preview that writes nothing

Import in Consonas reads your file, matches the columns it recognises, works out which records it would create, which it would update and which it would skip, and shows you all of that. Nothing is written. You read it, and then you decide.

The skips are usually the most informative part, because they are where your file disagrees with itself: rows with no name, addresses in the wrong column for some entries, a date format that changes halfway down because someone edited it on a different computer. Those are things you want to know before there are records depending on them.

And because the first pass writes nothing, a wrong import costs you nothing. Fix the file, run it again. There is no half finished import to unpick, no partial state to reason about, and no reason to abandon the migration.

People and organisations from one file

Almost every real file contains both: a person, their job title, and the company they work for. Import creates the person, creates the organisation, and connects them, which is the shape you want and the shape a system with separate contact and company imports makes you produce by hand in two passes.

Duplicates are matched on email address and on close name matches, and appear in the preview as records that will be updated rather than created. You are shown them rather than having them merged silently, because two records that look identical are occasionally two people, and a product that guesses will eventually guess about somebody who matters.

Bring the messy spreadsheet instead of cleaning it first

The advice everybody gives is to tidy the file before importing. It is well meant and it is the wrong order, because until you have seen the preview you do not know which columns matter, and you will spend an hour standardising a field nothing reads.

The preview shows exactly what will be created, row by row, before a single record is written. Clean against that. It is faster, and it tells you which of your columns are actually two columns wearing one heading.

Decide what a row is before you map anything

This is the decision that matters and it takes two minutes. Most spreadsheets have rows that describe a person and the organisation they work for at the same time. That is two relationships and a connection between them, not one record, and importing it as one produces a system that cannot answer who else works there.

The symptom of getting this wrong is discovering in month four that you have four hundred people and no companies, or four hundred companies each with a person's name in a field. Both are recoverable and both cost an afternoon you did not need to spend.

Map fewer columns than you have

Bring what someone will actually read. Name, contact details, type, the two or three pieces of information your trade genuinely uses. Leave the rest.

There is a strong instinct to bring everything on the grounds that it might be useful. It very rarely is, and the cost is real: fields nobody fills stay empty, empty fields make a record look neglected, and a record that looks neglected stops being trusted. You can always import the rest later against the same records.

Import a hundred rows first

Not the whole file. Take a hundred, look at what was created, and open five records at random to see whether they read the way you expected. Almost every problem shows up in the first hundred, and undoing a hundred is a different proposition from undoing four thousand.

Then run an export

Immediately, before there is anything worth losing. It takes a minute and it converts a promise on a website into a file you have opened. Do it again periodically so a current copy exists somewhere we do not control.

The first afternoon is the only time you can find out what an export produces without needing it to work, which is exactly why it is the right time to find out.

Search is for the thing you half remember

A name you are not sure how to spell, a phrase from a note, a company you met once. It searches across everything you are allowed to see, which means two people running the same search can correctly get different results, and that is the permission system working rather than an inconsistency.

Names rank above notes, which is right for the common case and occasionally wrong for yours. If you know the thing you remember was written in a note, saying so in the search rather than hoping is faster.

A saved view is for the question you will ask again

This is the habit that most changes whether a system of this kind is useful, and almost no one adopts it in the first fortnight. The useful question is never everyone. It is anything that has not moved in a month, clients in this region with no activity this quarter, everything owned by somebody who has left.

Three named views is usually enough to change how a team works. The one that finds money in most businesses is some version of nothing has happened here in a while, because it surfaces exactly the records that no process would ever bring to anyone's attention.

Views respect permission, which is what makes them shareable

A view is a question rather than a list of results. When someone else opens it, it runs for them, against what they are allowed to see. That is why a saved view can be shared without anyone having to think about whether it will leak something.

The same address twice inside one file

Duplicate review compares each row against the records you already hold, and it compares the rows against each other as well. A spreadsheet holding the same person on line eighty and line six hundred becomes one record rather than two. The earlier row is the one written, the later row fills in any column the earlier one left empty, and the preview says against that line that it was combined into one record, so you see it happen before anything is written.

Capitalisation defeats neither comparison. Both are made on the address folded to lower case, so an address typed two ways on two rows is still one person here.

What neither comparison can catch is the same person under two different addresses, or two rows that carry no address at all. The file is where that is cheapest to fix, so it is still worth sorting by the name column and looking. Two minutes there is worth an hour of merging afterwards, once each record has begun accumulating a history of its own and the merge has become a decision about which history to lose.

Someone who is in the recycle bin

A person who was deleted is not matched as a duplicate, deliberately. An import must not quietly bring someone back who was removed on purpose, possibly at their own request, by the side effect of a spreadsheet still holding their name. The row is read as new, and if you approve it you have created a new record rather than resurrected an old one, which is the outcome you can see and reason about.

A file that is not quite a file

Files that have been through several hands are frequently malformed in small ways. A quote in the middle of a field that was never quoted, a file that ends inside a quotation that was never closed, an invisible marker some spreadsheet applications write at the very start that would otherwise become part of your first column heading. Each of these is reported against its line number rather than absorbed silently, because a parser that makes its best guess at a broken line is a parser that produces a record nobody can trace back to anything.

A note containing a line break is read as one field rather than as the end of a row, so long free text survives. A file of much more than ten thousand rows is read up to about that point and stops. Split it, which you should be doing anyway.

Searches that correctly find nothing

A single letter returns nothing, on purpose. Matching is on the beginnings of words, so a search for the middle of a surname finds nothing while the same search against its first few letters finds it. And a search whose every match sits outside what you may see comes back empty rather than partially populated, which looks like a fault and is the permission model behaving exactly as intended.

We read the file ourselves instead of handing it to a library

The rules for this file format are small and completely written down, and the failures that matter for an import are precisely the ones a general purpose reader smooths over: the stray quotation mark, the line break inside a field, the invisible marker at the top of the file. We would rather report those with a line number than absorb them neatly. The cost is that any fault in reading your file is entirely ours, which is the correct place for it to sit and not the comfortable one.

The export is one file of lines rather than a folder of spreadsheets

A folder of spreadsheets is nicer to double click and loses the thing that matters, which is what pointed at what. The export is one file with a manifest at the top carrying a count for every table and what your organisation was entitled to, and then one line per record. It opens in any text editor, it can be read by anybody's developer without our involvement, and it can be loaded back. The honest cost is that it is not a spreadsheet, and if what you want is a list of clients with chosen columns you want a list rather than the export.

We read your export back every night

Every line, not a sample: the manifest is checked, each line is parsed, and the counts the manifest claims are compared with the rows the file actually contains. An export that writes cleanly and cannot be read back is the exact failure that stays hidden until the day someone needs it, and a backup nobody has ever read is a hope rather than a backup. It costs a little machine time nightly and it is the cheapest insurance in the product.

Import sits with an administrator, and export goes one wider

Export sits with owners, administrators and managers, because taking a copy destroys nothing. Import sits with owners and administrators, and not with managers. An administrator holds every permission the organisation has, this one included; what separates them from the owner is the grant over sensitive records and the billing, not anything on this page.

Import is still the one ordinary action that writes thousands of records on a single decision, so it is worth deciding on purpose who holds it rather than inheriting the default. Roles here are yours to define, and taking import out of the role you give somebody so they can reset passwords is a reasonable narrowing rather than a sign that something is wrong. If you change it, change it knowing which of the two is the destructive one.

The file that arrives is different in every trade, and so is the decision about what a row describes. Charities and membership organisations usually arrive with the hardest one, because a spreadsheet of donors or members carries consent as a tick and the tick is the part that will not survive being asked about.

Local service businesses and letting and estate agents more often arrive with one row per job or per tenancy instead of one per client, which is the decision worth making before anything is mapped. Recruitment agencies arrive with two populations in one file and need them kept apart from the first row.

Testing the export on the first afternoon is the same exercise as judging a supplier who has no customer stories yet, which is the position we are in and say so.