skip to content

A table read from a delimited file holds rows whose values sit one column right, and fewer rows than the file has lines. What produced each symptom?

level: seniorimportance: should knowfreq 50%

answer

  1. three counts, not one
  2. one symptom shifts, the other merges
  3. a separator inside a field's own text
  4. a line break inside a value
  5. count fields per line, not rows

basics

~20 s

Two different faults. A separator inside a field's own text pushes every later value one position right within that line. A line break inside a value makes one record out of two lines, so rows fall below the line count.

solid answer

~50 s

These are two independent faults that arrive together in messy text. The shifted values come from a separator appearing inside a field's own content — a comma in an address, a tab in a pasted note — so the line carries one field too many and everything after the offender occupies the wrong position. The shortfall in rows comes from something else entirely: a value that contains a line break, which spreads one record across two physical lines. A reader that handles that correctly gives you one row from two lines, and it is your line-based expectation, not the read, that was wrong. A reader working strictly on physical line boundaries instead sees two broken halves, and then its malformed-line disposition — stop, drop, or restore the shape — decides whether you lose a record or gain two damaged ones. Diagnose by counting fields per line rather than rows.

go deeper

for a junior

Learn the distinction first: a line is what the file holds, a record is what the reader made of it, and a row is what reached the table. These three numbers are allowed to differ, and knowing that is most of the answer.

for a middle

Explain both mechanisms — a separator inside a field's own text, and a line break inside a value — and say which direction each moves the row count relative to the line count.

for a senior

Demonstrate the diagnosis: a tabulation of field counts per line finds both faults in one pass, and you run it before touching anything downstream of the read.

for a principal

Decide standing policy on whether misaligned lines are absorbed or refused, and weigh the cost of asking for a re-send against the cost of carrying a reader configured to tolerate messy text indefinitely.

## Line, record, row: three numbers that are allowed to differ A **line** is a physical run of text in the file. A **record** is what the reader — the call that turns a file into a table in one step — managed to make of the lines. A **row** is what ended up in the table. Most of the time all three are equal, which is why people treat them as one number and are then unable to explain a discrepancy. Both symptoms in this question are disagreements between two of the three, and they pull in different directions. ## The shift: one separator too many Within a line, nothing ties a value to a column except **its position between separators**. The names come from the first line and apply to positions, not to content. So a single extra separator inside one field's own text displaces every field after it by one place. Where the extra separators come from is mundane: - free-text notes typed by people, containing the separator character - addresses, product names and descriptions pasted from elsewhere - a locale where the separator character is also a decimal mark - a producer that concatenated two values without escaping either What happens next depends on the reader: - **It rejects the line.** The record is gone and the row count falls, so a count comparison catches it. - **It restores the shape.** It truncates the surplus, or absorbs it into the last column. The field count now matches, **no disposition fires, and nothing anywhere records that the values are misaligned**. This is the case that survives into a report. - **It stops.** The best outcome, and the one you want while you are still learning what the file contains. The symptom you actually see downstream is a handful of rows where a date sits in a quantity column and a name sits in a date column. The temptation is to hunt the transform that mangled them. Nothing mangled them; they arrived that way. ## The split record: one value, two lines The second symptom has the opposite cause. A value legitimately contains a line break, so one record occupies two physical lines. Two outcomes exist, and which you get depends on the reader and sometimes on the mode you asked it to run in: - **The reader handles it.** Two lines become one record and one row. Nothing is lost. Your `lines - 1` expectation is simply too high, and the shortfall it reports is a **false alarm** — but one worth reading, because it tells you the file contains embedded breaks and your expectation needs a better source. - **The reader splits on physical line boundaries.** It sees two halves, each with the wrong number of fields, and the malformed-line disposition takes over. You lose one record, or you gain two damaged ones. Whether a given reader handles an embedded break, and whether a faster or parallel mode of the same reader stops handling it, varies between tools. It is not a property of the data. ## Telling them apart in one pass 1. **Count the fields on every line** in a single plain pass that does no type work, and tabulate the counts. One value will dominate overwhelmingly. 2. **Read the outliers above and below separately.** Lines with more fields than the dominant count are your surplus-separator suspects. Lines with fewer are usually halves of a record containing a line break. 3. **Check an outlier-below against its neighbour.** If the two lines concatenated form a well-shaped line, it is a split record and not damage. Only then choose a disposition. Diagnosing first is what stops you from configuring the reader to swallow a fault you have not understood. ## What each symptom does to a row-count comparison | Symptom | Lines | Records | Rows | What a count comparison shows | |---|---|---|---|---| | Surplus separator, line rejected | 1 | 0 | 0 | a genuine shortfall | | Surplus separator, shape restored | 1 | 1 | 1 | nothing at all | | Value with a line break, handled | 2 | 1 | 1 | a false shortfall | | Value with a line break, split | 2 | 0 or 2 | 0 or 2 | a shortfall or a surplus | The row that matters most is the second. It is the reason a count comparison, however good a habit, is not a complete defence at the file boundary, and the reason the field-count tabulation above earns its minute.

  • How do you locate the offending lines in a file of ten million lines?
    Count the fields on every line in one plain pass and tabulate the results. One value dominates; lines above it carry a surplus separator, and lines below it are usually halves of a record containing a line break. Print a handful of each with their positions in the file and read those rather than the file.
  • A row's values are shifted right but the field count is still correct. How?
    Because the reader restored the shape instead of rejecting the line: a surplus field can be truncated off the end or absorbed into the last column, depending on the design. The field count then matches, no disposition fires, and the misalignment reaches the table with nothing anywhere recording that it happened.
  • Which of the two symptoms does a row-count comparison detect?
    Only the merged-record case, and it reports it as a shortfall you then have to interpret: the record was not lost, your line-based expectation was too high. A surplus separator whose line the reader restored to shape moves no count at all and is invisible to the comparison.

saying these in an interview costs you the question

  • Blames a downstream transform for misaligned values
  • Assumes every record occupies exactly one line
  • Says the reader would surely have raised on a shifted line
  • Treats fewer rows than lines as always a loss
  • Deletes the offending lines without examining them
  • Confuses a line in the file with a row of the table