File Formats

The previous course made thirty-two decisions in eleven lessons, and only seven of them could be read back from the page. But the page itself came from somewhere: 2,037 rows, 1,400 subscribers, 42,645.6 m³. This lesson looks at what came before that page and asks a single question: how many rows did the file lose on its way into the analysis environment?

The answer depends on decisions, and decisions do not raise errors. When a parser meets a corrupt row, it does not stop and warn; it silently skips that row and keeps reading. That is this lesson’s unit of measure, and it runs through the whole course: at every step, rows in, rows out, and where they went. The most important item of the third is silent loss, that is, a row that drops out without producing any warning.

• DC1. The file is fiction. Instead of a real export, a generator that produces the same structure was written; the seed is visible and the output is the same from run to run. It is a model.
• DC2. The generated set yields the previous course’s page: 2,037 records, 1,400 distinct subscribers, 1,877 distinct subscriber-period pairs, 42,645.6 m³ in total.
• DC3. The delimiter is a semicolon and the quote is a double quote; the address field is written inside quotes in every record, and the file is written in UTF-8.
• DC4. The threshold for counting two totals as the same is 0.05 m³, because readings are reported with one decimal and a difference below that can only come from rounding.
• DC5. The largest reading the generator can produce is 37.0 m³; a larger value in the reading field shows that the field does not carry a reading.

What Is Inside the File

// source.mjs — a fictional export from the regional measurement network. THIS IS A
// MODEL: a generator that produces the same structure was written instead of a
// real file; the seed is visible and the output is the same from run to run. The
// records produce the page from M26/K02: 1400 subscribers, 1877 subscriber-period
// pairs, 160 repeated rows, 2037 records in total.
export const SEED = 1246, DELIM = ";", QUOTE = '"';
export const REGION = ["kuzey", "doğu", "merkez", "güney", "batı"];
export const HEADER = ["subscriber_no", "meter_no", "period", "address", "reading_m3", "region"];
const STREET = ["Pine Road", "New Dist Garden Rd", "Station Blvd", "Creek St", "Olive Road"];

export const rng = (t) => { let s = t >>> 0;
  return () => { s = (Math.imul(s, 1103515245) + 12345) >>> 0; return s / 4294967296; }; };

// Records: first the first period of 1400 subscribers, then the second period of 477
// subscribers, then the repetition of the first 160 rows (32 exact copies, 128
// corrected readings).
export function records() {
  const r = rng(SEED), read = () => 5 + Math.floor(r() * 321) / 10, k = [];
  const make = (i, period, reading) => ({ subscriber: 10000 + i, meter: `S-${4000 + i}`,
    period, address: `${STREET[i % 5]} No ${12 + (i % 88)}`, reading, region: REGION[i % 5] });
  for (let i = 0; i < 1400; i++) k.push(make(i, "2024-01", read()));
  for (let i = 0; i < 477; i++) k.push(make(i, "2024-02", read()));
  for (let i = 0; i < 160; i++) k.push(make(i, "2024-01", i < 32 ? k[i].reading : read()));
  return k;
}

// Dirtiness rule: the address of every 21st record contains the delimiter (one in
// three ends with a bare house number after the delimiter), and one in 50 of the
// rest contains a line break. The address field is written inside quotes in every
// record.
export const HAS_DELIM = (i) => i % 21 === 0;
export const HAS_NEWLINE = (i) => !HAS_DELIM(i) && i % 50 === 13;
export const ENDS_NUMBER = (i) => HAS_DELIM(i) && (i / 21) % 3 === 0;

export function exportFile() {
  const k = records(), s = [HEADER.join(DELIM)];
  k.forEach((x, i) => {
    let address = x.address;
    if (HAS_DELIM(i)) address += ENDS_NUMBER(i) ? `${DELIM} ${45 + (i % 30)}` : `${DELIM} Suite ${1 + i % 8}`;
    else if (HAS_NEWLINE(i)) address += `\nFlr ${1 + (i % 6)}`;
    s.push([x.subscriber, x.meter, x.period, QUOTE + address + QUOTE, x.reading.toFixed(1), x.region]
      .join(DELIM));
  });
  return Buffer.from(s.join("\n") + "\n", "utf8");   // the file is written in UTF-8
}

if (import.meta.url === `file://${process.argv[1]}`) {
  const k = records(), b = exportFile(), lines = b.toString("utf8").trimEnd().split("\n");
  const count = (f) => k.reduce((s, _, i) => s + (f(i) ? 1 : 0), 0);
  console.log(`seed ${SEED}; logical records ${k.length}, distinct subscribers ` +
    `${new Set(k.map((x) => x.subscriber)).size}, distinct subscriber-periods ` +
    `${new Set(k.map((x) => x.subscriber + x.period)).size}`);
  console.log(`file ${b.length} bytes, ${lines.length} physical lines (1 header + ` +
    `${lines.length - 1} data lines)`);
  console.log(`records with a delimiter in the address ${count(HAS_DELIM)} (${count(ENDS_NUMBER)} of them ` +
    `end in a number after the delimiter), records with a line break in the address ${count(HAS_NEWLINE)}`);
  console.log(`correctly read total ${k.reduce((s, x) => s + x.reading, 0).toFixed(1)} m3`);
  const hex = (name) => Buffer.from(name, "utf8").toString("hex").match(/../g).join(" ");
  for (const name of ["kuzey", "güney"])
    console.log(`region "${name}" UTF-8 bytes: ${hex(name)} (${Buffer.byteLength(name)} bytes, ` +
      `${name.length} characters)`);
  console.log(lines[0]); console.log(lines[1]); console.log(lines[14]);
}

seed 1246; logical records 2037, distinct subscribers 1400, distinct subscriber-periods 1877
file 108230 bytes, 2077 physical lines (1 header + 2076 data lines)
records with a delimiter in the address 97 (33 of them end in a number after the delimiter), records with a line break in the address 39
correctly read total 42645.6 m3
region "kuzey" UTF-8 bytes: 6b 75 7a 65 79 (5 bytes, 5 characters)
region "güney" UTF-8 bytes: 67 c3 bc 6e 65 79 (6 bytes, 5 characters)
subscriber_no;meter_no;period;address;reading_m3;region
10000;S-4000;2024-01;"Pine Road No 12; 45";9.4;kuzey
10013;S-4013;2024-01;"Creek St No 25

The first contradiction is in the last three lines. The file has 2,076 data lines but 2,037 logical records; the difference of 39 comes from the 39 records that carry a line break in their address. How many lines a record occupies is not fixed; measuring the file by counting lines gives a wrong number.

The second is on the first data line. The address field reads "Pine Road No 12; 45": the delimiter itself appears inside the field. The delimiter is a character chosen to separate fields, and it can occur in the data too; that is exactly why quotes exist. A parser that does not read quotes sees seven fields here instead of six and reads the tail of the address as the reading field.

The third is in the byte lines. kuzey is five characters and five bytes, güney is five characters but six bytes. The encoding a file was written in is not written inside the file; the reading side makes a decision, and if the decision is wrong no error occurs, only the letters change.

Rows In, Rows Out, Silently Dropped

// read.mjs — the same file is read with two parsing decisions. K1 splits the text
// at line breaks and the lines at the delimiter, strips the quotes, and decodes the
// file with a single-byte encoding. K2 preserves what is inside quotes and decodes
// the file with the encoding it was written in. Neither raises an error; the
// difference shows up only in the counts.
import { exportFile, HEADER, REGION, DELIM, QUOTE, records } from "./source.mjs";
const b = exportFile(), RD = HEADER.indexOf("reading_m3"), RG = HEADER.indexOf("region");

const extract = (rows) => {
  const out = [], dropped = [];
  for (const fields of rows) {
    const reading = Number(fields[RD]);
    if (fields[RD] !== "" && fields[RD] !== undefined && Number.isFinite(reading))
      out.push({ reading, region: fields[RG] });
    else dropped.push(fields.length);
  }
  return { rowsIn: rows.length, out, dropped };
};

// K1: naive reader. It knows nothing about quotes; it strips the quotes field by field.
const k1 = (bytes) => extract(bytes.toString("latin1").trimEnd().split("\n").slice(1)
  .map((s) => s.split(DELIM).map((a) => a.replaceAll(QUOTE, ""))));

// K2: a state machine that preserves what is inside quotes. Inside quotes both the
// delimiter and the line break are part of the field; two quotes side by side mean
// a single quote.
function k2(bytes) {
  const m = bytes.toString("utf8"), record = []; let field = [""], inside = false;
  for (let i = 0; i < m.length; i++) {
    const c = m[i];
    if (inside) { if (c === QUOTE && m[i + 1] === QUOTE) { field[field.length - 1] += c; i++; }
      else if (c === QUOTE) inside = false; else field[field.length - 1] += c; }
    else if (c === QUOTE) inside = true;
    else if (c === DELIM) field.push("");
    else if (c === "\n") { record.push(field); field = [""]; }
    else field[field.length - 1] += c;
  }
  if (field.length > 1 || field[0] !== "") record.push(field);
  return extract(record.slice(1));
}

// The threshold for counting two totals as the same is 0.05 m3: readings are
// reported with one decimal, and a difference below that can only come from rounding.
const THRESHOLD = 0.05, MAX_READING = 37.0;   // the largest reading the generator can produce
const target = records().reduce((s, x) => s + x.reading, 0);
const fmt = (w) => (h) => h.map((v, j) => (j ? String(v).padStart(w[j])
  : String(v).padEnd(w[0]))).join("");
const fmt1 = fmt([22, 8, 9, 8, 12, 11, 10]);
console.log(fmt1(["parsing decision", "rows in", "rows out", "dropped", "total m3",
  "mismatched", "house no"]));
const result = {};
for (const [name, f] of [["K1 naive split", k1], ["K2 quote-preserving", k2]]) {
  const r = result[name] = f(b), t = r.out.reduce((s, x) => s + x.reading, 0);
  console.log(fmt1([name, r.rowsIn, r.out.length, r.dropped.length, t.toFixed(1),
    r.out.filter((x) => !REGION.includes(x.region)).length,
    r.out.filter((x) => x.reading > MAX_READING).length]));
}
const r1 = result["K1 naive split"], t1 = r1.out.reduce((s, x) => s + x.reading, 0);
const half = r1.dropped.filter((n) => n < 6).length, shifted = r1.dropped.filter((n) => n > 6).length;
const houseNo = r1.out.filter((x) => x.reading > MAX_READING);
console.log(`\nthe file has ${r1.rowsIn} data lines but ${records().length} logical records: ` +
  `the 39 records that carry a line break in the address produced ${r1.rowsIn - 2037} extra lines`);
console.log(`K1 dropped ${r1.dropped.length} lines without any warning: ${half} split ` +
  `half-lines, ${shifted} lines with a shifted field`);
console.log(`of the ${r1.out.length} records K1 extracted, ${houseNo.length} survived ` +
  `but carry a house number shifted out of the address in the reading field (like ${houseNo[0].reading} m3)`);
console.log(`the two totals diverge by ${Math.abs(target - t1).toFixed(1)} m3, ` +
  `${Math.round(Math.abs(target - t1) / THRESHOLD)} times the ${THRESHOLD} m3 threshold; ratio ` +
  `${(100 * Math.abs(target - t1) / target).toFixed(1)}%`);
console.log(`the region name does not match the list in ` +
  `${r1.out.filter((x) => !REGION.includes(x.region)).length} of the records from K1; ` +
  `the region breakdown silently leaves these records out`);

parsing decision       rows in rows out dropped    total m3 mismatched  house no
K1 naive split            2076     1934     142     41661.4       1157        33
K2 quote-preserving       2037     2037       0     42645.6          0         0

the file has 2076 data lines but 2037 logical records: the 39 records that carry a line break in the address produced 39 extra lines
K1 dropped 142 lines without any warning: 78 split half-lines, 64 lines with a shifted field
of the 1934 records K1 extracted, 33 survived but carry a house number shifted out of the address in the reading field (like 45 m3)
the two totals diverge by 984.2 m3, 19684 times the 0.05 m3 threshold; ratio 2.3%
the region name does not match the list in 1157 of the records from K1; the region breakdown silently leaves these records out

The two rows of the table read the same file and give two different sets. The quote-preserving read extracts all 2,037 rows, drops none, and finds a total of 42,645.6 m³. The naive split extracts 1,934 records from 2,076 lines: 142 lines disappeared and none of them raised an error. Both finished with an exit code of zero.

The breakdown of the loss has two items. 78 lines are split halves: the 39 records that carry a line break in their address were spread over two lines in the file, the naive reader took both for separate lines, the reading field was missing in both, and it threw both away. One record spent two lines to disappear. 64 lines are shifted-field lines: in 64 of the 97 records that carry a delimiter in their address, the tail of the address landed in the reading field, did not convert to a number, and the line dropped. In terms of logical records the account closes: 103 of the 2,037 records left the set entirely.

The remaining 33 records are the lesson’s real finding. In one third of the records that carry a delimiter in their address, a bare house number followed the delimiter; the shifted field converted to a number and the record survived. The reading field now holds a value like 45 m³, and according to DC5 this value cannot be a reading. These 33 records appear missing from no count; they only inflate the total. The 103 dropped records reduce the total, the 33 inflated records raise it; the net difference comes to 984.2 m³, 19,684 times the threshold and 2.3% of the total.

The last row measures a fourth channel. The file was written in UTF-8, the naive reader decoded it with a single-byte encoding, and region names that carry multi-byte letters turned into other strings. In 1,157 of the 1,934 records the region name does not match the list; a summary that breaks down by region silently leaves these records out, and the sum of the breakdown does not match the grand total. The encoding decision dropped not a single row; it only made more than half of the records unclassifiable.

Delimiter, Document, Column

// format.mjs — the same 2037 records are stored in three structures. THIS IS A
// MODEL: the names of the formats do not matter, their structures do. Columnar
// model: reading as a 2-byte integer (deci-m3), subscriber as a 4-byte integer,
// period and region as one-byte codes, text length-prefixed.
import { exportFile, records, REGION } from "./source.mjs";
const k = records(), doc = Buffer.from(JSON.stringify(k), "utf8");
const text = (a) => Buffer.concat(a.map((s) => Buffer.from(s, "utf8"))
  .flatMap((x) => [Buffer.from([x.length]), x]));
const COLUMN = {
  subscriber: Buffer.from(new Int32Array(k.map((x) => x.subscriber)).buffer),
  reading: Buffer.from(new Uint16Array(k.map((x) => Math.round(x.reading * 10))).buffer),
  period: Buffer.from(k.map((x) => (x.period === "2024-01" ? 0 : 1))),
  region: Buffer.from(k.map((x) => REGION.indexOf(x.region))),
  meter: text(k.map((x) => x.meter)), address: text(k.map((x) => x.address)),
};
const total = () => { let t = 0;
  for (let i = 0; i < k.length; i++) t += COLUMN.reading.readUInt16LE(i * 2) / 10;
  return t.toFixed(1); };
const fmt1 = (h) => h.map((v, j) => (j ? String(v).padStart([0, 13, 15, 12][j])
  : String(v).padEnd(22))).join("");
console.log(fmt1(["structure", "total bytes", "bytes needed", "total m3"]));
for (const [name, whole, question] of [["delimited text", exportFile().length, exportFile().length],
  ["nested document", doc.length, doc.length],
  ["columnar", Object.values(COLUMN).reduce((s, x) => s + x.length, 0), COLUMN.reading.length]])
  console.log(fmt1([name, whole, question, total()]));
console.log(`learning the total reading takes ${COLUMN.reading.length} bytes in the columnar structure ` +
  `and reading ${exportFile().length} bytes in delimited text: ${(exportFile().length / COLUMN.reading.length)
    .toFixed(0)} times`);

structure               total bytes   bytes needed    total m3
delimited text               108230         108230     42645.6
nested document              243328         243328     42645.6
columnar                      68040           4074     42645.6
learning the total reading takes 4074 bytes in the columnar structure and reading 108230 bytes in delimited text: 27 times

The three structures carry the same 2,037 records and give the same total. The difference is where the boundary of a record is written. In delimited text the boundary is a character, and that character can occur in the data too; the loss of 142 lines is the price of that. In the nested document structure the file grows to more than twice the size because the field name is repeated in every record, but where a record ends can be read from the structure of the text. In the columnar structure every column is a separate byte array and its type is declared; a text shift into the reading field is structurally impossible.

The last line measures the second face of the choice. The total-reading question asks for 4,074 bytes in the columnar structure and 108,230 bytes in delimited text — 27 times. The columnar structure does this cheaply because it never opens the address column; on the other hand, seeing all the fields of a single record means going to six separate arrays. Choosing a format is not a matter of convenience but a decision about which job gets done cheaply.

Summary

• The file has 2,076 data lines and the set has 2,037 logical records; counting lines is not counting records.
• The quote-preserving read extracts all 2,037 records and finds a total of 42,645.6 m³; the naive split extracts 1,934 records and drops 142 lines without any warning.
• The breakdown of what dropped is 78 split half-lines and 64 shifted-field lines; of the 2,037 records, 103 left the set, and 33 of the shifted fields converted to numbers and survived in the reading field as house numbers. The net difference is 984.2 m³, 19,684 times the threshold.
• The wrong encoding drops not a single line, but it makes the region name of 1,157 records fail to match the list.
• The same set takes 68,040 bytes in the columnar structure and the total reading question is answered by reading 4,074 bytes; in delimited text the same question asks for 108,230 bytes.

Next Step

This lesson took the data from a file and counted its rows. But most data sits in a database, and on its way out it brings a question the file did not carry: a query returns rows, but what a record of what that row is depends on how the query is written. The next lesson measures whether the row a query returns matches the unit the analysis asks about, and counts how joining two tables multiplies the row count.