Identifiers with zeroes
Fictional example: 0012 and 12 are distinct strings. Convert to a number only if the field definition allows it. If a zero was lost, return to the original before matching.
In-depth practical guides
Check structure, missing values, duplicates and keys without changing the original file.
Updated :

AI-generated illustration
Opening a file in a spreadsheet can remove leading zeros, convert dates or conceal irregular rows. Review a copy before correcting it. Check delimiter, encoding, column count, missing values, keys and duplicates; structure does not establish data truth.
Record origin, download date, licence and file hash. Keep received files separate from transformations. Opening and saving in a spreadsheet can already change identifiers.
Identify delimiter, encoding and header presence from documentation and a checked sample. Quoted fields may contain a delimiter or line break, so physical line count is not necessarily record count.
Count blank cells, distinct values and surrounding whitespace. Blank does not necessarily mean zero. NA, null and - remain values unless a dictionary defines them as missing.
Define a key from the grain, such as station and date. Compare exact value tuples and record empty components before joining. Concatenation without an escaping rule can collapse distinct keys; document normalisation.
An identical record and a repeated key are different findings. Establish the grain: company, establishment or dated observation. Document each correction and compare counts before and after.
Reading workshop
A small fictional extract separates dialect, multiline fields, text preservation and an irregular record.
Fictional data and records for learning: none of these results comes from a live service query.
This example’s contract specifies UTF-8, a semicolon delimiter, double quotes and an id;note header. Its declared semicolon dialect differs from the comma separator in RFC 4180.
If the table exceeds the screen, scroll horizontally; arrow keys work when the region is focused.
| Observed element | Count or reading | Diagnosis | Recorded decision |
|---|---|---|---|
| 0012;"alpha; beta" | Two fields despite the semicolon inside the note | The internal separator belongs to the quoted field. | Use the declared dialect; do not split on every semicolon. |
| 0013 with a note containing a line break | Five physical lines in the complete extract | There are four records: one header and three data records. | Keep the line break in the note and count with a CSV reader. |
| 0012 and =2+2 | Strings to inspect without calculation | The leading zero and formula text must remain visible. | Convert neither identifier nor note; no formula executes here. |
| 0014;=2+2;extra | Three fields instead of two | The third data record violates the declared structure. | Set the anomaly aside for resolution; do not silently discard extra. |
UTF-8 text, at most 8192 bytes, 40 records and 12 fields. Select the delimiter; no header is inferred. Double quotes must enclose the whole field; double an internal quote. CRLF, LF and CR are accepted.
No initial UTF-8 mark detected.
Record numbers whose width differs from the first: 4
Preview of at most the first five records. Scroll horizontally or use arrow keys. An empty field differs from an absent field.
| Record | Field 1 | Field 2 | Field 3 |
|---|---|---|---|
| 1 | id | note | Absent field |
| 2 | 0012 | alpha; beta | Absent field |
| 3 | 0013 | line 1
line 2 | Absent field |
| 4 | 0014 | =2+2 | extra |
A bounded dialect based on CSV quoting rules; tab and semicolon are explicit options. No business types, duplicates or facts are validated. Cells remain text; physical-line counts exclude a phantom empty line after the final terminator. A blank line is treated here as a record with one empty field.
Open each proposition to compare its diagnosis. More checks may still be needed.
Incorrect. The multiline field and header invalidate that count. There are three data records, including one irregular record.
That would be a transformation rather than a receipt count. Record three received data records, qualify the anomaly and separately document any authorised exclusion.
That is this example’s structural count. It establishes neither business validity nor key uniqueness in a complete file.
id;note 0012;"alpha; beta" 0013;"line 1 line 2" 0014;=2+2;extra
The five displayed lines form four logical records under the declared dialect. The last is intentionally irregular. The text =2+2 is demonstration data, never an executed instruction.
Fictional example: 0012 and 12 are distinct strings. Convert to a number only if the field definition allows it. If a zero was lost, return to the original before matching.
In an illustrative table, a station appears every day. Station code alone is not unique; station + date may be appropriate. A repeated key does not justify deleting an observation.
Fictional example: a file has a header and two addresses, one containing a line break inside a quoted field. Counting physical lines suggests three observations. Check the record count with a CSV reader configured for this file, then compare the resulting columns with the expected schema. Keep the original file; removing line breaks before parsing would alter the data rather than correct its interpretation.
Worked exercise
Fictional example: the values and documents teach the method and do not describe a real organisation.
The delimiter is a comma. A colleague proposes splitting at every comma; the code must remain text.
If the table exceeds the screen, scroll horizontally; arrow keys work when the region is focused.
| Item | Raw content |
|---|---|
| Header | id,name,count |
| Record | 0012,"Nord, Est",7 |
There are three fields: 0012, «North, East» as one name, then 7. A naive split produces four pieces and shifts the final column. Quotes delimit the field and are not part of its decoded name. The code must remain 0012.
If the quoted name also contains a newline, a physical line no longer necessarily equals one record.
Keep the raw excerpt, separator, quoting convention, field count and key type.
A record of structure, counts and anomalies, with an explicit decision on the transformations to perform.
No. Check grain, versions and columns before deduplicating. Identical records may represent separate events if an event identifier is missing.
Only after checking the schema and intended use. It may be required by an import or indicate an uncollected variable. Record the decision in the dictionary.
Check the delimiter, encoding, quoting and header settings during import. Then inspect known fields, especially accents and identifiers. A column layout does not demonstrate that values were preserved.
DOCUMENTED REFERENCE
Column length needs a unit. Counts below follow UTF-8 and UTF-16 for four exact sequences, not a user file. One perceived sign may use several code points. Table notation: cp = code points; B = bytes.
| Reference | Value or form | What it identifies |
|---|---|---|
| U+0041 · A | cp=1 · UTF-16=1 · UTF-8=1 B | ASCII A has equal counts; not a multilingual rule. |
| U+00E9 · é | cp=1 · UTF-16=1 · UTF-8=2 B | Precomposed é uses one code point but two UTF-8 bytes. |
| U+0065 U+0301 | cp=2 · UTF-16=2 · UTF-8=3 B | e plus combining acute uses two points and three UTF-8 bytes, often one grapheme. |
| U+1F600 | cp=1 · UTF-16=2 · UTF-8=4 B | U+1F600 uses a UTF-16 surrogate pair: JavaScript length is two, not two emoji. |
| UTF-8 BOM | EF BB BF | Optional UTF-8 BOM EF BB BF is a signature, not byte-order switching. Treating it as content can alter the first header. |
| U+FFFD | U+FFFD | U+FFFD may come from invalid decoding or intentional content. It cannot reconstruct lost bytes or alone prove damage. |
Decode with an identified encoding before normalizing. Preserve source bytes for audit. Grapheme segmentation is separate from byte, UTF-16 or code-point counts.
No. It acts on already decoded Unicode, not lost encoding or bytes replaced by a replacement character. Return to source data.
Documentation consulted on .
DOCUMENTED REFERENCE
Informational RFC 4180 describes a common comma format, not every dialect. Splitting on every comma and newline can destroy cells. Fragments here illustrate syntax, not actual observations.
| Reference | Value or form | What it identifies |
|---|---|---|
| Field delimiter | , | Comma delimits this format; semicolon dialects need an explicit setting. |
| Quoted field | "a,b" → a,b | Quoted a,b is one cell, not two columns. |
| Escaped quote | "a""b" → a"b | Two quotes inside a quoted field represent one literal quote. |
| Record / physical line | Quoted CRLF can belong to a field | Quoted CRLF belongs to a field; physical line count can exceed record count. |
| Whitespace | Spaces belong to field content | Spaces are field content under the RFC. Trimming may change an identifier. |
| Header | present / absent · optional | Header is optional; MIME header may state presence. Assuming it can discard a data row. |
Record delimiter, quoting, escape, line endings, encoding and header policy before inferring types. Correct parsing does not validate business meaning. Decimal commas need a convention, not global punctuation replacement.
Count parsed records with the same dialect first, accounting for headers, blanks and multiline fields. Equal counts do not establish matching objects.
Documentation consulted on .
DOCUMENTED REFERENCE
An invisible character can affect a digest and a first field. Before editing a file, separate binary preservation, decoding and the receiving format’s rules.
| Reference | Value or form | What it identifies |
|---|---|---|
| UTF-8 BOM | EF BB BF | U+FEFF encodes as EF BB BF in UTF-8. At a stream’s start it may identify encoding. These three bytes remain in the file even when an editor does not display them. |
| BYTE ORDER | UTF-8: one-byte units | UTF-8 uses one-byte units: there is no little-endian versus big-endian choice to infer from this marker. Do not automatically transfer UTF-16 detection rules. |
| POSITION | initial / internal | Inside text, U+FEFF must not be treated as an initial signature. Globally deleting every occurrence may change content rather than remove a prefix. |
| RAW BYTES | preserve before conversion | Adding or removing the marker changes bytes. Keep the original and label a transformed copy; a post-conversion digest is not the digest of the received file. |
| INVALID UTF-8 | ≠ replacement text | Replacement decoding can hide invalid UTF-8 behind a replacement character. Readable display does not prove byte conformity; document error handling. |
| BagIt 1.0 | bagit.txt: UTF-8 · no BOM | In BagIt 1.0, bagit.txt must use UTF-8 without a BOM. This applies to the declaration file, not universally to every payload file. |
Acceptance, ignoring or removal depends on format and protocol. Encoding diagnosis does not authorize automatic correction of every file.
Not necessarily. An editor can change encoding, marker or line endings. Check bytes and keep transformations separate when binary comparison matters.
Documentation consulted on .