Choosing Column Names Users Recognize in CSV Imports

The header row of a CSV is the part of the file that people read. It is the name of the thing, and it decides whether a user can look at the export and know what each column is, and whether the file they build themselves will be accepted. Developers tend to name columns after the database: display_name, plan_tier, seat_count. Users name them after what they see on the screen, or after what their spreadsheet calls them, or after the way another system spells them. When those two vocabularies meet at the import, either the import is strict and users fail, or it is flexible and developers have to write the flexibility. This article shows the naming rules that work, the mapping function that makes the importer forgiving and the three outcomes it should report. It belongs to CSV import and export features in applications, and the mapping screen it supports is described in designing a CSV import screen users can succeed with.

Name for the reader

The export is a document that someone will open, so its header should read like one. Use the labels from your interface: if the screen says "Full name" and "Seats", so does the header. Use normal words with spaces and capitals, which spreadsheets display well, and avoid the conventions of code, snake_case, camelCase and abbreviations such as cust_id, unless your users are developers. Keep units and formats in the name when they matter: Amount (EUR), Created (UTC), Birth date (YYYY-MM-DD). A header that tells the reader the unit prevents the question that would otherwise reach support.

A few things to avoid. Do not put punctuation that some tools mishandle: a leading =, +, - or @ makes a header a formula in a spreadsheet, and a quote or a comma needs quoting, which is legal but easy to get wrong in someone else's script. Do not use two columns with the same name. Do not start the file with the word ID in capitals if Excel users will open it directly, since Excel can mistake such a file for another format (Excel CSV versus RFC CSV differences). And do not reorder or rename columns casually between releases: a file exported last month and edited should still import this month.

Keep a key, treat the header as a label

The way to have friendly names and stability is to separate them. Each column has an internal key that never changes (email, name, plan, seats) and a header label that people see, which can change, be translated or gain synonyms. The exporter writes the label, and the importer maps from whatever label arrives back to the key. Then renaming "Seats" to "Licensed seats" in a release is a change to the label list and an added alias, and every file in the wild keeps working.

The same split gives you localization for free. The header for the same key is Full name in English, Nom complet in French, Vollständiger Name in German, and the importer accepts all of them, because a file exported in one language is routinely edited and re-imported by someone working in another.

A mapping function that forgives

The importer should match headers by what they mean and not by exact string. The steps are mechanical: strip a byte order mark, trim, collapse runs of spaces and underscores, lowercase, then look the result up in a table of aliases. Here is the function in PHP, with the table for four fields:

const COLUMNS = [
    'email' => ['email', 'e-mail', 'email address', 'mail'],
    'name'  => ['name', 'full name', 'customer name', 'contact'],
    'plan'  => ['plan', 'subscription', 'tier'],
    'seats' => ['seats', 'licenses', 'users', 'number of seats'],
];

function norm(string $h): string {
    $h = preg_replace('/^\xEF\xBB\xBF/', '', $h);          // byte order mark
    return strtolower(trim(preg_replace('/[\s_]+/', ' ', $h)));
}

Each key has a list of normalized aliases. mapHeaders builds a lookup from alias to key, walks the file's header, records the position of each mapped column, collects the ones it does not recognize and raises an error if two columns map to the same key. Run on three headers that differ in the ways real files do:

["E-mail","Full  Name","Tier","Number of Seats"]  -> mapped {"email":0,"name":1,"plan":2,"seats":3}; missing []; unknown []
["email_address","contact","plan","seats","notes"]   -> mapped {"email":0,"name":1,"plan":2,"seats":3}; missing []; unknown [notes]
["Email","Name","Plan"]                              -> mapped {"email":0,"name":1,"plan":2}; missing [seats]; unknown []

The first header carries a byte order mark in front of E-mail and a double space in Full Name; both normalized away, and Tier and Number of Seats hit their aliases. The second uses underscores, email_address becoming email address, and has an extra notes column. The third is short a required column. All three produce a precise result, which is the point: the function does not decide what to do about the differences, it reports them.

Three outcomes, three responses

The mapping result has three parts, and each one wants a different reaction.

Mapped. The columns the importer understood. Show them to the user in the mapping screen with the matched field, so they can see that Tier was understood as Plan and correct it if it was wrong. Silent matching is fine for exact and well-known aliases and risky for fuzzy ones, so show everything that was not an exact match.

Unknown. Columns in the file that match nothing. Do not fail the import for these. Ignore them and say so: "The column Notes was not recognized and will be ignored." Failing is hostile to users whose spreadsheet has a few working columns of their own, and it also means that an export from another tool with extra columns cannot be imported. If you want the user to decide, offer a dropdown to map the unknown column to a field or to ignore it.

Missing required. Fields the importer needs and the file lacks. This one blocks the import, and the message names the field and the headers it accepts: "Required column Seats was not found. Accepted names: Seats, Licenses, Users, Number of seats." Do not make the user guess what to call it.

A fourth case should be an error and not a message: two file columns mapping to the same key. A file with both Email and E-mail is ambiguous, and a silent choice means one column of data is dropped without notice. Stop and ask the user which to use.

Fuzzy matching, with care

Some teams add edit-distance matching, so that Emial maps to Email. It is friendly and dangerous. A misspelled header is usually a sign of a hand-edited file, and a wrong guess moves a column of data into the wrong field, silently, which is worse than a refusal. If you do it, apply it only to unmapped columns, only to close matches (one or two characters, and unambiguous), always show the guess and require the user to confirm it. For automated feeds, never. The exact-and-aliases approach covers most real variation: spaces, case, underscores, the usual synonyms.

Grow the alias list from evidence. When a user's import fails on an unknown header that is clearly a known field (E mail, Mobile for phone), add the alias. Keep a log of unknown headers across imports, and read it quarterly: the most common unknown header is a missing alias or a missing feature.

Order and position

Header names make the order of columns irrelevant, and the importer should use that: read by name, not by position, so a file with the columns shuffled works. Files without a header are the exception. If you allow them, state the expected order in the template and check the column count, and treat the first row as data. Detecting whether a first row is a header is a heuristic, and a wrong guess drops a record or imports a header as a customer, so ask the user ("My file has a header row") and default to yes.

When internal names are the right choice

If the audience is developers and the file is an interface between systems, use stable machine names: lowercase, underscores, no spaces, documented in a schema. customer_id and created_at are better for a feed read by a script than Customer ID and Created (UTC), which need quoting in some tools and are awkward as column names in a dataframe. Machine feeds are also the case where aliases should not exist: strictness is a feature when a program is on the other end, and an unknown column is an error (handling a CSV provider who changes columns without warning).

For mixed audiences, offer both: a friendly export for people and a machine export for integrations, from the same column definitions. In both cases the contract is the key list, versioned and documented. The behavior of the PHP regular expression functions used above is in the PCRE reference, and the header itself is optional as far as RFC 4180 is concerned. The broader problem of header variation is covered in header variants and normalizing column names.