In-depth practical guides

Diagnose a CSV file before using it

Check structure, missing values, duplicates and keys without changing the original file.

Updated :

Reports and charts on a data review desk. Illustrative scene with no real data.

AI-generated illustration

Can this table support the intended question?

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.

  1. Keep the original

    Record origin, download date, licence and file hash. Keep received files separate from transformations. Opening and saving in a spreadsheet can already change identifiers.

  2. Set the dialect

    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.

  3. Profile the columns

    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.

  4. Test a composite key and proposed cleaning

    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.

  5. Decide before correcting

    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

Count records before repairing lines

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.

The two-column DEMO-C03 batch

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.

The two-column DEMO-C03 batch
Observed elementCount or readingDiagnosisRecorded decision
0012;"alpha; beta"Two fields despite the semicolon inside the noteThe internal separator belongs to the quoted field.Use the declared dialect; do not split on every semicolon.
0013 with a note containing a line breakFive physical lines in the complete extractThere 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+2Strings to inspect without calculationThe leading zero and formula text must remain visible.Convert neither identifier nor note; no formula executes here.
0014;=2+2;extraThree fields instead of twoThe third data record violates the declared structure.Set the anomaly aside for resolution; do not silently discard extra.

Read a small CSV excerpt without converting cells

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.

Inspection result

Records, including the first
4
Physical lines
5
Fields in the first record
2
Records of a different width
1

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.

Parsed fields, without formula evaluation
RecordField 1Field 2Field 3
1idnoteAbsent field
20012alpha; betaAbsent field
30013line 1 line 2Absent field
40014=2+2extra

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.

What can the receipt record say before this batch is transformed?

Open each proposition to compare its diagnosis. More checks may still be needed.

Five observations because there are five lines.

Incorrect. The multiline field and header invalidate that count. There are three data records, including one irregular record.

Two observations after deleting the irregular line.

That would be a transformation rather than a receipt count. Record three received data records, qualify the anomaly and separately document any authorised exclusion.

Three received data records: two with two fields, one with three.

That is this example’s structural count. It establishes neither business validity nor key uniqueness in a complete file.

Fictional CSV extract displayed without execution

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.

Documentation of the rules, separate from fictional examples

Compare other situations and exercises

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.

Repeated key, different observations

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.

An address spans several lines

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

A comma inside a name is not a column

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.

Starting information
ItemRaw content
Headerid,name,count
Record0012,"Nord, Est",7

Your checks

  1. Count fields using the quoting rules.
  2. Keep the first field as text.
  3. Compare a naive split's result.
Read the answer

Answer and decision

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 case changes

If the quoted name also contains a newline, a physical line no longer necessarily equals one record.

Record to retain

Keep the raw excerpt, separator, quoting convention, field count and key type.

Record to retain

  • Origin, licence and original hash
  • Dialect and missing-value dictionary
  • Grain, selected key and observed issues
  • Transformation rules and compared counts

Official references

Related source profiles

Related protocols

Checks before reaching a conclusion

  • Do identifiers retain their zeroes?
  • Are blanks distinguished from zeroes?
  • Does the grain justify the key?
  • Are corrections traceable and reversible?

A record of structure, counts and anomalies, with an explicit decision on the transformations to perform.

Frequently asked questions

Should all duplicates be deleted?

No. Check grain, versions and columns before deduplicating. Identical records may represent separate events if an event identifier is missing.

Can an empty column be removed?

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.

Why do columns look shifted in a spreadsheet?

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

Text length: bytes, code points and visible signs differ

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.

Text length: bytes, code points and visible signs differ
ReferenceValue or formWhat it identifies
U+0041 · Acp=1 · UTF-16=1 · UTF-8=1 BASCII A has equal counts; not a multilingual rule.
U+00E9 · écp=1 · UTF-16=1 · UTF-8=2 BPrecomposed é uses one code point but two UTF-8 bytes.
U+0065 U+0301cp=2 · UTF-16=2 · UTF-8=3 Be plus combining acute uses two points and three UTF-8 bytes, often one grapheme.
U+1F600cp=1 · UTF-16=2 · UTF-8=4 BU+1F600 uses a UTF-16 surrogate pair: JavaScript length is two, not two emoji.
UTF-8 BOMEF BB BFOptional UTF-8 BOM EF BB BF is a signature, not byte-order switching. Treating it as content can alter the first header.
U+FFFDU+FFFDU+FFFD may come from invalid decoding or intentional content. It cannot reconstruct lost bytes or alone prove damage.

Implication for the record

Decode with an identified encoding before normalizing. Preserve source bytes for audit. Grapheme segmentation is separate from byte, UTF-16 or code-point counts.

Can NFC repair damaged encoding?

No. It acts on already decoded Unicode, not lost encoding or bytes replaced by a replacement character. Return to source data.

Primary documentation

Documentation consulted on .

DOCUMENTED REFERENCE

CSV: a displayed line is not always a record

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.

"a,b""a""b"c[ a,b ] · [ a"b ] · [ c ]
CSV: a displayed line is not always a record
ReferenceValue or formWhat it identifies
Field delimiter,Comma delimits this format; semicolon dialects need an explicit setting.
Quoted field"a,b" → a,bQuoted a,b is one cell, not two columns.
Escaped quote"a""b" → a"bTwo quotes inside a quoted field represent one literal quote.
Record / physical lineQuoted CRLF can belong to a fieldQuoted CRLF belongs to a field; physical line count can exceed record count.
WhitespaceSpaces belong to field contentSpaces are field content under the RFC. Trimming may change an identifier.
Headerpresent / absent · optionalHeader is optional; MIME header may state presence. Assuming it can discard a data row.

Implication for the record

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.

Can exports be compared by line counts?

Count parsed records with the same dialect first, accounting for headers, blanks and multiline fields. Equal counts do not establish matching objects.

Primary documentation

Documentation consulted on .

DOCUMENTED REFERENCE

UTF-8 BOM: an invisible marker is still bytes

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.

UTF-8 BOM: an invisible marker is still bytes
ReferenceValue or formWhat it identifies
UTF-8 BOMEF BB BFU+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 ORDERUTF-8: one-byte unitsUTF-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.
POSITIONinitial / internalInside text, U+FEFF must not be treated as an initial signature. Globally deleting every occurrence may change content rather than remove a prefix.
RAW BYTESpreserve before conversionAdding 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 textReplacement decoding can hide invalid UTF-8 behind a replacement character. Readable display does not prove byte conformity; document error handling.
BagIt 1.0bagit.txt: UTF-8 · no BOMIn BagIt 1.0, bagit.txt must use UTF-8 without a BOM. This applies to the declaration file, not universally to every payload file.

Implication for the record

Acceptance, ignoring or removal depends on format and protocol. Encoding diagnosis does not authorize automatic correction of every file.

Is a CSV unchanged if it opens correctly and is saved again?

Not necessarily. An editor can change encoding, marker or line endings. Check bytes and keep transformations separate when binary comparison matters.

Primary documentation

Documentation consulted on .