An import type contract states how each source field must be interpreted and what must survive a spreadsheet round trip.
Direct answer: assign meaning before the spreadsheet assigns a type
A CSV can arrive intact and still become damaged during import. The file stores characters and separators; the spreadsheet interprets them. If an account identifier becomes a number, a leading zero can disappear or a long sequence can lose significant digits. A successful import dialog therefore establishes very little about whether the original identifiers survived.
Before importing, classify each field by its purpose. Treat an identifier as text when its exact character sequence matters, even if every character is a digit. Set the type before conversion, preserve the original file and test a deliberately difficult set of records through the full export-and-reimport path. Formatting an already changed value cannot recover information that was discarded.
This essay proposes the Meaning Before Type contract: a small agreement connecting each field's meaning, permitted interpretation and acceptance evidence. It is an author-designed review method, not a new CSV standard. Its practical aim is to make silent conversion visible before a spreadsheet becomes the source of another system's records.
Key takeaways
An identifier answers which object; a quantity answers how much. Similar-looking strings can serve different purposes and need different import rules. A customer number is often an identifier even when the provider calls it a number. Adding two customer identifiers rarely produces a meaningful third customer.
Keep storage, display and interpretation separate. A cell can display scientific notation without losing all underlying precision, but Excel also has a documented numerical precision limit. A custom display format may show leading zeros without making them part of the stored identifier. Inspect the actual value and the exported result rather than judging the screen alone.
CSV quoting handles file syntax. It does not provide a universal field-type declaration for spreadsheet importers. A quoted sequence of digits can still be interpreted according to application behavior. Treat exact preservation as something to test, not something the presence of quotation marks automatically establishes.
The problem: useful defaults become invisible decisions
Spreadsheets are designed to make data usable quickly. Automatic interpretation is often helpful for quantities, dates and calculations. That convenience becomes a problem when the application has less context than the person who created the file. The importer sees characters; it does not know which changes would break the identity of a record.
Imagine a volunteer receiving a membership list. The first rows contain short numerical-looking identifiers, so the imported column appears ordinary. Later rows include leading zeros and longer identifiers issued by a newer system. The worksheet still looks plausible. A sorting or formatting pass can make it look even cleaner while the relationship to the original records has already changed.
The root cause is a missing field contract at the point of interpretation. The receiving person knows how to open a file but has not been told which properties must survive. The sender assumes that characters visible in a text file will remain those characters in a worksheet. Neither party explicitly accepts responsibility for conversion.
This is why a spot check of the first few rows is weak protection. The easiest rows may be exactly the rows that automatic detection handles well. A useful acceptance set must contain examples chosen for different failure modes, including those that occur late in the file.
Definitions and entity relationships
A source field is the sequence recorded in the original file. A parsed field is the value produced after the importer applies CSV syntax rules. A typed value is the application's interpretation of that parsed field. A displayed cell is the presentation of the typed value. An exported field is what the next file actually contains.
The path is source characters, parsing, typing, display, editing and serialization. Damage can enter at more than one transition. A comma inside a quoted field concerns parsing. A leading zero removed by numeric conversion concerns typing. A rounded display concerns presentation. An altered exported identifier concerns the boundary to the next consumer.
Power Query and an Excel worksheet are related environments with distinct type behavior. A type supported in the query engine does not automatically establish how every value will behave after loading into a worksheet. Keep the destination and intended use in the acceptance record rather than writing only that the column was typed correctly.
Locale is also an interpretation input. Decimal separators and date conventions can differ between the sender and receiver. An identifier contract should usually prohibit date or numerical interpretation altogether. A quantity contract should state the expected interpretation instead of borrowing the identifier rule for every column.
Framework: Meaning Before Type
The Meaning Before Type contract has four components: semantic role, allowed representation, conversion point and round-trip evidence. The framework is proposed here for operational checking. Its value comes from making each decision explicit enough to inspect, rather than from promising that one application setting solves every format problem.
Semantic role says what the field is for. Write customer identifier, invoice quantity or measurement date instead of relying on a column label such as code or value. State whether exact character equality matters. For an identifier, the acceptance rule might require that the exported sequence match the source sequence without removing zeros or changing digits.
Allowed representation specifies the characters and interpretation permitted by the receiving workflow. A text identifier may allow digits and hyphens while prohibiting whitespace changes. A quantity may need a numerical type with a defined unit. These are local requirements, not assumptions that every identifier uses the same syntax or every measurement has the same precision.
Conversion point records where typing occurs. A CSV file does not contain the workbook's cell types. If a query step changes a column type automatically, inspect that step before loading. If an application opens CSV files directly using automatic interpretation, choose a controlled import route when exact identifiers matter. State the actual application and version used for the test.
In Power Query, inspect the Applied Steps list for an automatic Changed Type step. If it has already converted the identifier numerically, remove or revise that conversion and return to the preserved source before assigning Text. Changing a later step to Text only converts the value that survived; it cannot recreate discarded digits. Preview the difficult records after the corrected step and again after loading to the actual destination.
Round-trip evidence records what happened after loading, saving or exporting, and opening the output through the next intended route. Compare identifiers as strings using the agreed rule. Do not compare only their visual width or their mathematical equality. The contract should make a failure actionable: identify the field, the transition and the source record needed to reproduce it.
Three documented examples of type assumptions
Microsoft's guidance on keeping leading zeros and large numbers explains Excel's treatment of leading zeros and its numerical precision limit of 15 significant digits. It also makes clear that formatting cannot restore digits already lost. This is a reason to choose text before conversion for exact long identifiers, rather than applying a decorative format afterward. [S1]
Power Query documentation describes automatic type detection for unstructured sources and its inspection of the first 200 rows by default. It also distinguishes available numerical types and locale-sensitive conversion. A column whose later records differ from the opening sample can therefore need an explicit type decision rather than confidence borrowed from the preview. [S2]
RFC 4180 documents common CSV syntax, including quoted fields containing commas, line breaks and doubled quotation marks. It is an informational description of a common format, not a universal spreadsheet type system. Correctly quoting a field addresses how the characters are separated and escaped; it does not instruct every receiving application to preserve a digit sequence as text. [S3]
These examples concern different boundaries. Excel precision concerns the typed numerical value. Query detection concerns how a type may be selected. CSV quoting concerns parsing. A sound import process checks all three without treating them as interchangeable causes or prescribing one superficial fix for every failure.
Case study: a membership file passes the preview and fails the handoff
Consider a hypothetical association moving a membership list between two systems through a spreadsheet. The example identifiers and row counts below are illustrative test fixtures, not observations about an actual customer or a claim about the prevalence of errors.
The file contains 240 records. Most early identifiers are short digit sequences. Record 217 has identifier 001204, while record 231 contains the sequence 123456789012345678. The coordinator opens the file, sees orderly columns and begins editing addresses. The preview gave no reason to notice the later exceptional identifiers.
After export, the first identifier appears as 1204. The longer sequence no longer matches the original when the downstream system reads the output. The coordinator initially tries to repair the display by adding zeros and widening the column. That response addresses appearance before determining whether the original characters remain available.
The team returns to the untouched source file and writes a contract for the membership identifier field: exact text, no numerical conversion and equality after export. They select the two difficult records plus an ordinary record as named acceptance fixtures. They import through a route where the field can be assigned text before a numerical conversion step is applied.
They then export to a new file and inspect the exact identifier strings through the intended receiving route. If the next application still converts the fields automatically, the handoff remains unresolved even though the workbook itself is correct. The receiving route is part of the test; the spreadsheet is an intermediate stage.
The failure example is trusting an orderly preview and repairing formatting after conversion. The decision method is to return to the original evidence, define identity preservation and test the entire path. Three fixtures do not prove every record correct, but they make these particular risks reproducible and expose a false claim that opening successfully means importing faithfully.
A practical comparison that avoids misleading equality
Numerical equality can hide the exact problem under investigation. The strings 001204 and 1204 can describe different identifiers even though a numerical comparison treats them as equivalent. Decide the equality rule before writing a reconciliation formula or interpreting a matched count.
Preserve an original, read-only source and produce a separate working output. Compare identifiers at the field level after CSV parsing rather than comparing raw lines, since legitimate quoting or line-ending differences can change file bytes without changing fields. If exact file preservation is itself required, retain a checksum too, but do not confuse a checksum with semantic acceptance.
Choose fixtures around the documented risks: leading zeros, long digit sequences, embedded separators, quotation marks and later rows that differ from the opening sample. Include any identifier that would be costly to mismatch. This is a targeted risk check, not a statistically validated sampling plan or a universal assurance about the whole dataset.
When counts disagree, investigate the transition instead of immediately editing records to force agreement. A parser problem, duplicate identifier or type conversion requires a different repair. Keep an exception list with the source reference and the observed output so a reviewer can distinguish an intentional transformation from an accidental one.
Tradeoff and counterargument: text is not the answer to every column
The strongest objection is that importing everything as text can make a spreadsheet harder to use. Numerical analysis needs numerical values, and dates may need consistent date handling. That objection is correct. The framework asks for meaning before typing, not a universal prohibition on useful types.
Apply text preservation to fields whose exact characters define identity. Apply explicit numerical conversion to quantities after confirming units and conventions. A workbook can contain both. The important decision is whether a transformation is justified by the field's purpose and tested at the relevant destination.
There is a maintenance cost to a detailed import contract. For a one-off low-consequence list, a small field note and targeted comparison may suffice. For a recurring handoff with costly mismatches, a reusable import procedure and automated field comparison may be worthwhile. The consequence of an error should determine the effort, rather than the mere availability of more settings.
An explicit contract also has limits. It cannot repair a source that was already altered before receipt, and it does not establish that the underlying membership data is correct. It establishes a narrower property: specified field meanings and representations survived the observed path. That narrower claim is useful precisely because it can be checked.
Common mistakes
Do not decide that a field is a quantity because it contains digits. Do not assume CSV quotation marks act as a portable text-type declaration. Do not interpret a neat preview as evidence about records the preview did not meaningfully exercise.
Do not apply a custom zero display and call an identifier restored without consulting the source. Do not assume a wide column prevents numerical precision loss. Do not treat a query engine's available type as a guarantee about all later worksheet and export behavior.
Avoid saving the only source copy through the application being tested. That removes the easiest recovery path and makes it harder to establish where the change occurred. Keep evidence from before the risky transition and give the working output a distinct name.
Action plan: turn the next import into a small acceptance test
Identify the fields whose exact characters matter and write their semantic role. Select known difficult records from the untouched source. State the required destination behavior, including whether the next consumer expects a CSV, workbook or another format.
Inspect the import steps and set the intended types before conversion. Watch for automatic change-type steps and locale assumptions. Load into a separate working artifact. Test the chosen records and then export through the real handoff route.
Compare the resulting parsed fields against the source using string equality where identity matters. Record exceptions, the application version and the tested route. Repair the conversion rule from the source instead of manually guessing lost digits. Accept the handoff only for the properties actually demonstrated.
FAQ: identifiers, precision and CSV handoffs
These questions distinguish syntax, stored values and display behavior. Check the linked application documentation for the versions and import routes you actually use.
Is every digit-only field a number?
No. Its purpose determines the type. If exact characters identify a customer or item and arithmetic would be meaningless, treat it as an identifier and define what must survive the handoff.
Does putting quotation marks around an identifier make it safe?
Quotation marks serve CSV parsing rules. They do not define one universal application type. Test the receiving importer and choose an explicit text type when exact character preservation is required.
Can a number format recover missing leading zeros?
A display format can show zeros according to a pattern, but that does not establish the original identifier. Return to the untouched source and compare the exact field instead of guessing how many characters were lost.
Does scientific notation always mean the data is damaged?
No. It can be a display choice. Inspect the stored value and exported field. Separately consider documented precision limits; changing the display cannot restore significant digits already discarded during conversion.
Why test records beyond the preview?
Later records may contain forms absent from the opening sample used for detection. Choose fixtures by failure risk and semantic importance, rather than assuming the earliest rows represent every field value.
Should every column be imported as text?
No. Quantities and dates often need useful typed behavior. Define each field's role, then use a type that serves that role and preserves the properties required by the receiving workflow.
Does Power Query's whole-number type guarantee worksheet precision?
Do not infer every destination's behavior from a query type alone. Test the load and export path you actually use. Query processing and worksheet storage are related stages with distinct constraints.
Can a checksum prove the import was correct?
A checksum can demonstrate file-byte identity. It does not show that a spreadsheet interpreted fields correctly. Preserve the source checksum where useful, then inspect parsed and typed values separately.
What is a round-trip test?
It follows the data through the intended import, working artifact, export and next receiving route. Its acceptance rule states which fields and meanings must match, rather than requiring identical presentation at every stage.
Should raw CSV lines be compared directly?
Use field-level comparison for semantic preservation because valid quoting and line endings may differ. Use byte comparison when exact file identity is the requirement. Be explicit about which property the comparison demonstrates.
What if the original file is already damaged?
Do not invent missing characters. Seek an earlier authoritative export or reconcile against the source system. Record that the available file cannot establish the original identifier rather than calling a guessed repair verified.
How do locale differences affect the contract?
They can change how dates and decimal-looking values are interpreted. State the expected conventions for quantities and dates. For identifiers, prevent interpretation that would alter the exact character sequence.
Are three test records enough?
Three illustrative fixtures can expose specific risks, but they do not prove universal correctness. Use consequential exact-object checks or broader automated comparison when the handoff requires stronger evidence.
Can the receiving system still change a correct export?
Yes. A correct intermediate file does not establish the behavior of its next importer. Include the receiving route in the acceptance test and keep its limitations visible to the person approving the handoff.
What should be retained with the accepted output?
Keep the original source reference, field contract, selected fixtures, comparison results and unresolved exceptions. That evidence explains what passed and helps the next operator repeat the procedure without relying on memory.
Recommended books
The Craft of Research by Wayne C. Booth and collaborators helps distinguish a claim from the evidence needed to support it. The Checklist Manifesto by Atul Gawande offers context for concise safeguards within skilled work. Thinking in Systems by Donella Meadows helps examine dependencies between the source, importer and receiving application. These books support the reasoning method; they do not replace current Microsoft documentation or the CSV format reference.
Further exploration
Once identifiers survive, examine the transformations you actually intend: deduplication, address normalization or unit conversion. Give each its own acceptance rule. Faithful import and correct transformation are different achievements. Keeping them separate makes it possible to improve the data without losing the evidence needed to explain what changed.