Project: a CSV-to-JSON converter in Crystal

stock.csv has five records on six lines, because one title holds a line break inside its quotes. A converter that counts records reports the bad pages cell of B-103 on line 4; a reader looking at the file finds it on line 5.

csv2json, the project of this mini-course, gets that and every other detail right because a specification fixes them first: a schema of typed columns, records with the line they start on, prices as whole paise, every error of a file in one run, and JSON whose bytes another implementation can match. You will build it with shards, test it with crystal spec, convert back with json2csv, and then build the same rules again in Python.

The specification fixes every byte of the output

Two programs written from the same rules, in any language, must print the same bytes. These are csv2json’s rules:

1. It takes two arguments, a schema file and a CSV file. Anything else prints a usage line and ends with exit statusexit status: The whole number a program returns to whatever started it: 0 for success by convention, anything else for a failure. Shells and build pipelines read it to decide what happens next. More in “Project: a marks report in Object Pascal” 2.
2. The schema is JSON: {"columns": [{"name": …, "type": …}, …]}, with the types string, int, rupees and bool. A missing field or another type ends the run with status 2.
3. The CSV is UTF-8 text in the records of RFC 4180: cells separated by commas, records by LF or CRLF, and a cell in double quotes may hold commas, line breaks and quotes written twice. A byte-order markbyte-order mark: The character U+FEFF at the very start of a text file, which some programs write before UTF-8 text. A reader that does not drop it takes it as part of the first value, so the first column of a CSV header no longer matches its name. at the start is ignored, and so are empty lines.
4. The first record must name the schema’s columns, in the schema’s order; otherwise status 2.
5. A string cell is kept as it is. An int is an optional minus and the ASCII digits 0 to 9, within a 64-bit integer. Rupees are digits with at most two after a point, written as whole paise. A bool is true or false. An empty cell is null, except in a string column, where it is the empty string.
6. Every record with the wrong number of cells and every cell that breaks its rule is reported, in file order, as line N: … or line N, column NAME: …, where N is the line on which the record starts, on standard errorstandard error: The second output stream every program has, meant for error messages. Kept apart from standard output, it lets a report be saved to a file while its errors still reach the terminal or a log. More in “Project: a marks report in Object Pascal”; then the run ends with status 1, and nothing goes to standard output.
7. Otherwise the output is a JSON array with one object per record, the keys in the header’s order, indented by two spaces, and a line break after it.

Step through one run, with the two checks that stop it:

The data of this project is a bookshop’s stock. The session converts five files and tries two broken schemas:

from session import run

# csv2json built and run on five files: a clean one, one with mistakes, the same data as an export with a byte-order
# mark and CRLF line breaks, a header without records, and one whose header was renamed; then two schemas it refuses.
run("""
cd csv2json
shards build csv2json
./bin/csv2json ../data/schema.json ../data/books.csv
!./bin/csv2json ../data/schema.json ../data/stock.csv
./bin/csv2json ../data/schema.json ../data/books.csv > books.json
./bin/csv2json ../data/schema.json ../data/export.csv > export.json
diff books.json export.json && echo "export.csv gives the same JSON"
./bin/csv2json ../data/schema.json ../data/empty.csv
!./bin/csv2json ../data/schema.json ../data/renamed.csv
!./bin/csv2json ../data/bad-schema.json ../data/books.csv
!./bin/csv2json ../data/no-type.json ../data/books.csv
""")

$ cd csv2json
$ shards build csv2json
I: Dependencies are satisfied
I: Building: csv2json
$ ./bin/csv2json ../data/schema.json ../data/books.csv
[
  {
    "code": "B-101",
    "title": "Monsoon Notes",
    "pages": 212,
    "price": 34900,
    "in_stock": true
  },
  {
    "code": "B-102",
    "title": "Lakes, Hills and Rivers",
    "pages": 348,
    "price": 49950,
    "in_stock": false
  },
  {
    "code": "B-103",
    "title": "The \"Quiet\" Train",
    "pages": 96,
    "price": 14900,
    "in_stock": true
  },
  {
    "code": "B-104",
    "title": "चाय और किताबें",
    "pages": null,
    "price": 50,
    "in_stock": null
  }
]
$ ./bin/csv2json ../data/schema.json ../data/stock.csv
line 3, column price: "₹ 299" is not rupees
line 3, column in_stock: "yes" is not a bool
line 5, column pages: "ninety-six" is not an int
line 6: expected 5 fields, found 3
[exit status 1]
$ ./bin/csv2json ../data/schema.json ../data/books.csv > books.json
$ ./bin/csv2json ../data/schema.json ../data/export.csv > export.json
$ diff books.json export.json && echo "export.csv gives the same JSON"
export.csv gives the same JSON
$ ./bin/csv2json ../data/schema.json ../data/empty.csv
[]
$ ./bin/csv2json ../data/schema.json ../data/renamed.csv
header does not match the schema: expected code,title,pages,price,in_stock, found code,name,pages,price,in_stock
[exit status 2]
$ ./bin/csv2json ../data/bad-schema.json ../data/books.csv
schema: column price: unknown type "money"
[exit status 2]
$ ./bin/csv2json ../data/no-type.json ../data/books.csv
schema: Missing JSON attribute: type
  parsing Csv2json::Column#type at line 1, column 14
  parsing Csv2json::Schema#columns at line 1, column 2
[exit status 2]

books.csv became four objects. 349.00 became 34900 paise and 0.5 became 50; the title with a comma kept its comma, the doubled quotes of "The ""Quiet"" Train" became \" in JSON, and the Hindi title of B-104 arrived as it was, with its empty pages and in_stock cells as null. stock.csv produced no JSON at all but four errors, and the lines they name are the lines where the records start. export.csv holds the same data with a byte-order mark and CRLF line breaks, as some spreadsheets save it, and diff found its JSON the same as that of books.csv. A header without records gave an empty array, [].

A typed schema, read with JSON::Serializable

The schema becomes two Crystal classes. include JSON::Serializable gives each a constructor that reads JSON into the class’s typed instance variables, so Schema.from_json(text) either returns a schema whose columns all have a name and a type, or raises:

require "csv"
require "json"

# Converts CSV to JSON and back by a schema of typed columns, as the project's specification says.
module Csv2json
  VERSION = "0.1.0"
  TYPES   = ["string", "int", "rupees", "bool"]
  BOM     = '\u{FEFF}'

  alias Value = String | Int64 | Bool | Nil

  # A cell that is not a value of its column's type, and why.
  record Bad, reason : String

  # One column of the schema file: {"name": "pages", "type": "int"}.
  class Column
    include JSON::Serializable

    getter name : String
    getter type : String
  end

  # The schema file: {"columns": [...]}.
  class Schema
    include JSON::Serializable

    getter columns : Array(Column)

    def names : Array(String)
      columns.map(&.name)
    end
  end

  class SchemaError < Exception
  end

  # Reads a schema and checks that every type is one of TYPES.
  def self.schema(json : String) : Schema
    schema = Schema.from_json(json)
    schema.columns.each do |column|
      unless TYPES.includes?(column.type)
        raise SchemaError.new("column #{column.name}: unknown type #{column.type.inspect}")
      end
    end
    schema
  end

  # A cell as a value of its type: an empty cell is null, except in a string column.
  def self.convert(cell : String, type : String) : Value | Bad
    return (type == "string" ? cell : nil) if cell.empty?
    case type
    when "string"
      cell
    when "int"
      number = cell.to_i64? if cell.matches?(/\A-?[0-9]+\z/)
      number || Bad.new("#{cell.inspect} is not an int")
    when "rupees"
      paise(cell) || Bad.new("#{cell.inspect} is not rupees")
    else
      case cell
      when "true"  then true
      when "false" then false
      else              Bad.new("#{cell.inspect} is not a bool")
      end
    end
  end

  # "349.5" is 34950 paise: whole rupees and at most two digits after the point, counted exactly, without a float.
  def self.paise(cell : String) : Int64?
    match = cell.match(/\A([0-9]+)(?:\.([0-9]{1,2}))?\z/)
    return nil unless match
    rupees = match[1].to_i64?
    return nil unless rupees
    rupees * 100 + (match[2]? || "").ljust(2, '0').to_i64
  rescue OverflowError
    nil
  end

  # Each record of the text with the line it starts on: a line break inside a quoted cell moves the next record down.
  def self.records(text : String) : Array({Int32, Array(String)})
    rows = [] of {Int32, Array(String)}
    line = 1
    CSV.each_row(text.lchop(BOM)) do |row|
      rows << {line, row} unless row.empty?
      line += 1 + row.sum(&.count('\n'))
    end
    rows
  end

  # The JSON of the whole file, or every error in file order.
  def self.to_json(schema : Schema, text : String) : {String?, Array(String)}
    rows = records(text)
    header = rows.shift?
    names = schema.names
    if header.nil? || header[1] != names
      found = header ? header[1].join(",") : "nothing"
      return {nil, ["header does not match the schema: expected #{names.join(",")}, found #{found}"]}
    end
    errors = [] of String
    objects = rows.compact_map do |line, cells|
      if cells.size != names.size
        errors << "line #{line}: expected #{names.size} fields, found #{cells.size}"
        next
      end
      schema.columns.map_with_index do |column, i|
        value = convert(cells[i], column.type)
        if value.is_a?(Bad)
          errors << "line #{line}, column #{column.name}: #{value.reason}"
          next {column.name, nil}
        end
        {column.name, value}
      end
    end
    return {nil, errors} unless errors.empty?
    json = JSON.build(indent: "  ") do |builder|
      builder.array do
        objects.each do |fields|
          builder.object { fields.each { |name, value| builder.field(name, value) } }
        end
      end
    end
    {json, errors}
  end
end

The last two runs of the session were the schema’s checks. In bad-schema.json one column has the type money, which JSON reads well enough; csv2json’s own check, in Csv2json.schema, refused it as unknown type "money". In no-type.json a column has no type at all, and that needed no code of csv2json’s: from_json raised Missing JSON attribute: type and named where, Csv2json::Column#type at line 1, column 14, because getter type : String has no default.

The same idea carries the cells. convert returns Value | Bad: a value of the column’s type, or a Bad, a one-field record that says why the cell is not one. The caller has to handle both, because the compiler checks every use of the union, the same check that stopped the nil of the Ruby lesson. to_json collects a message for every Bad and goes on, so a file with four mistakes reports four errors in one run.

A record’s line is the line it starts on

A CSV file has lines and records, and they are not the same thing. The title of B-102 in stock.csv is "Notes from and the Ghats", one quoted cell with a line break in it, so that record takes lines 3 and 4. Step through the records and the line each one starts on:

records counts that way: it starts at 1 and, after each record, adds 1 and the number of line breaks inside the record’s cells. Crystal’s CSV reader returns records without their lines, so the count is csv2json’s own, and it is written into the specification because readers count differently. Python’s csv.reader keeps line_num, which its documentation calls the number of lines read from the source, so after a record of two lines it names the line where the record ends. Run it on the same records, then edit STOCK:

# Python's csv module on the records of stock.csv: line_num counts the lines read so far, so after a record that spans
# two lines it is the line where the record ends, not the one where it starts.
import csv
import io

STOCK = (
    "code,title,pages,price,in_stock\n"
    "B-101,Monsoon Notes,212,349.00,true\n"
    'B-102,"Notes from\nthe Ghats",180,₹ 299,yes\n'
    "B-103,Tea and Trains,ninety-six,149,true\n"
    "B-104,City of Lakes,348\n"
)

reader = csv.reader(io.StringIO(STOCK))
for row in reader:
    print(reader.line_num, row[0])

1 code
2 B-101
4 B-102
5 B-103
6 B-104

Python said 4 for B-102, the line where it ends, and csv2json says 3, where it starts. Neither is wrong, but the two disagree on every record that spans lines, so a specification has to say which line it reports.

The byte-order mark is the other trap of real exports. Some programs start UTF-8 files with the character U+FEFF; without text.lchop(BOM) the first column’s name would be \u{FEFF}code, and export.csv would fail the header check although it looks right in every editor.

Each cell converts by a rule, or says why not

csv2json does not let Crystal’s general conversions decide what a number is. The session shows what they accept on their own, and what a price becomes when it goes through a float:

# Why csv2json writes its own rules for numbers: what Crystal's general conversions accept, and a price as a float.
p " 212".to_i64?, "+212".to_i64?, "1_000".to_i64?, "0x1F".to_i64?
p "212".matches?(/\A-?[0-9]+\z/), " 212".matches?(/\A-?[0-9]+\z/)
p "4.35".to_f * 100, ("4.35".to_f * 100).to_i64
p "1e3".to_f?, "  7.5 ".to_f?

from session import run

# What to_i64? and to_f accept on their own, and what a price becomes when it goes through a float.
run("""
cd rules
crystal run rules.cr
""")

$ cd rules
$ crystal run rules.cr
212
212
nil
nil
true
false
434.99999999999994
434
1000.0
7.5

to_i64? alone accepted " 212" with its space and "+212", which the specification’s int does not allow, and to_f? read "1e3" as 1000.0 and " 7.5 " with its spaces as 7.5. So convert tests an int cell against /\A-?[0-9]+\z/ first and only then converts it, and to_i64? still refuses a number too large for 64 bits.

The float row is the reason for paise: 4.35 as a float times 100 printed 434.99999999999994, and cutting it to an integer gave 434, one paisa less. paise never makes a float. It takes the rupees and up to two digits after the point as two integers, rupees * 100 + fraction, so 349.5 is exactly 34,950 and 149.505 is refused.

Back to CSV, the specs and the release build

json2csv is the way back. It writes the schema’s header, then one record per object; CSV::Builder quotes a cell only when it needs quotes, and paise are written as rupees with two digits after the point. The project’s files fit together like this:

from session import run

# JSON back to CSV with json2csv, and that CSV back to JSON: the JSON must not change, while the CSV is normalised.
run("""
cd csv2json
shards build
./bin/csv2json ../data/schema.json ../data/books.csv > books.json
./bin/json2csv ../data/schema.json books.json > back.csv
cat back.csv
./bin/csv2json ../data/schema.json back.csv > back.json
diff books.json back.json && echo "back.csv gives the same JSON"
""")

$ cd csv2json
$ shards build
I: Dependencies are satisfied
I: Building: csv2json

I: Building: json2csv
$ ./bin/csv2json ../data/schema.json ../data/books.csv > books.json
$ ./bin/json2csv ../data/schema.json books.json > back.csv
$ cat back.csv
code,title,pages,price,in_stock
B-101,Monsoon Notes,212,349.00,true
B-102,"Lakes, Hills and Rivers",348,499.50,false
B-103,"The ""Quiet"" Train",96,149.00,true
B-104,चाय और किताबें,,0.50,
$ ./bin/csv2json ../data/schema.json back.csv > back.json
$ diff books.json back.json && echo "back.csv gives the same JSON"
back.csv gives the same JSON

back.csv is not books.csv: 499.5 came back as 499.50 and 149 as 149.00, because the JSON holds paise, not the text of the cell. The JSON is what survives a round trip, and diff found back.json identical to books.json. A specification therefore names the canonical formcanonical form: The one form of some data that a specification declares the reference, such as csv2json's JSON with prices in paise. Other forms, such as the CSV that json2csv writes back, may differ in their text and still hold the same data.: here the JSON, with CSV as one way to write it.

The specs test each rule on its own, with the cells that break it:

require "./spec_helper"

SCHEMA = Csv2json.schema(%({"columns": [{"name": "code", "type": "string"}, {"name": "pages", "type": "int"}]}))

describe Csv2json do
  describe ".convert" do
    it "reads ints exactly, and nothing else as an int" do
      Csv2json.convert("212", "int").should eq(212_i64)
      Csv2json.convert("-7", "int").should eq(-7_i64)
      Csv2json.convert("1_000", "int").should eq(Csv2json::Bad.new(%("1_000" is not an int)))
      Csv2json.convert("99999999999999999999", "int").should be_a(Csv2json::Bad)
    end

    it "counts rupees in paise without a float" do
      Csv2json.convert("349.5", "rupees").should eq(34950_i64)
      Csv2json.convert("0.07", "rupees").should eq(7_i64)
      Csv2json.convert("149.505", "rupees").should eq(Csv2json::Bad.new(%("149.505" is not rupees)))
      Csv2json.convert("₹ 299", "rupees").should be_a(Csv2json::Bad)
    end

    it "takes only true and false as bools, and empty cells as null" do
      Csv2json.convert("true", "bool").should be_true
      Csv2json.convert("yes", "bool").should eq(Csv2json::Bad.new(%("yes" is not a bool)))
      Csv2json.convert("", "bool").should be_nil
      Csv2json.convert("", "string").should eq("")
    end
  end

  describe ".records" do
    it "gives each record the line it starts on" do
      rows = Csv2json.records(%(code,pages\nA,"1\n2"\nB,3\n))
      rows.map(&.[0]).should eq([1, 2, 4])
    end

    it "drops a byte-order mark and skips empty lines" do
      Csv2json.records("\u{FEFF}code,pages\n\nA,1\n").should eq([{1, ["code", "pages"]}, {3, ["A", "1"]}])
    end
  end

  describe ".to_json" do
    it "reports every error with its line" do
      json, errors = Csv2json.to_json(SCHEMA, "code,pages\nA,x\nB\n")
      json.should be_nil
      errors.should eq([%(line 2, column pages: "x" is not an int), "line 3: expected 2 fields, found 1"])
    end

    it "refuses a header that is not the schema's" do
      _, errors = Csv2json.to_json(SCHEMA, "code,title\n")
      errors.should eq(["header does not match the schema: expected code,pages, found code,title"])
    end
  end

  describe ".to_csv" do
    it "writes paise back as rupees and quotes where it must" do
      schema = Csv2json.schema(%({"columns": [{"name": "code", "type": "string"}, {"name": "price", "type": "rupees"}]}))
      csv = Csv2json.to_csv(schema, %([{"code": "A, B", "price": 49950}, {"code": "C", "price": null}]))
      csv.should eq(%(code,price\n"A, B",499.50\nC,\n))
    end
  end
end

from session import run

# csv2json's specs: the conversion of cells, the line of each record, the errors and the CSV written back.
run("""
cd csv2json
crystal spec
""")

$ cd csv2json
$ crystal spec
........

Finished in …
8 examples, 0 failures, 0 errors, 0 pending

from session import run

# The release build of csv2json: its size and the libraries it loads when it starts.
run("""
cd csv2json
shards build csv2json --release
wc -c bin/csv2json
otool -L bin/csv2json
""")

$ cd csv2json
$ shards build csv2json --release
I: Dependencies are satisfied
I: Building: csv2json
$ wc -c bin/csv2json
 1296728 bin/csv2json
$ otool -L bin/csv2json
bin/csv2json:
	/usr/lib/libiconv.2.dylib (compatibility version 7.0.0, current version 7.0.0)
	/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1359.0.0)

All eight examples passed. shards build csv2json --release built the converter alone, 1,296,728 bytes, and like the first program of this mini-course it loads only libiconv and libSystem from macOS: PCRE2, which its regular expressions need, and the garbage collector were linked in from the release’s embedded/lib.

Practise: csv2json again, in Python

Writing the converter a second time, in another language, gives the first one something to be checked against, a test oracletest oracle: A second, trusted way to work out the expected results of tests, such as a short program written straight from the specification, used to check an implementation on many inputs.. When the two print different bytes for the same file, either one of them breaks a rule or a rule can be read two ways, and both are worth finding. The lab takes three steps, and each sample test is a line that the Crystal tool printed in the sessions above:

def convert(cell, kind):
    """The value of cell in a column of this kind, or ValueError with the reason it is not one."""
    if kind == "int":
        return int(cell)
    if kind == "rupees":
        return int(float(cell) * 100)
    if kind == "bool":
        return cell == "true"
    return cell

from cells import convert


def reason(cell, kind):
    """The message of the ValueError that convert raises, or None when it raises none."""
    try:
        convert(cell, kind)
    except ValueError as error:
        return str(error)
    return None


def test_the_prices_of_books_csv():
    """349.00, 499.5, 149 and 0.5 became 34900, 49950, 14900 and 50 paise"""
    assert [convert(c, "rupees") for c in ["349.00", "499.5", "149", "0.5"]] == [34900, 49950, 14900, 50]


def test_prices_that_break_the_rule():
    """three decimals, a currency sign and a thousands separator"""
    assert reason("₹ 299", "rupees") == '"₹ 299" is not rupees'
    assert reason("149.505", "rupees") == '"149.505" is not rupees'
    assert reason("1,499", "rupees") == '"1,499" is not rupees'


def test_a_price_that_a_float_gets_wrong():
    """4.35 rupees through a float is 434 paise; the rule gives 435"""
    assert convert("4.35", "rupees") == 435


def test_ints():
    """only an optional minus and digits, within 64 bits"""
    assert convert("212", "int") == 212
    assert convert("-7", "int") == -7
    assert reason("ninety-six", "int") == '"ninety-six" is not an int'
    assert reason(" 212", "int") == '" 212" is not an int'
    assert reason("+212", "int") == '"+212" is not an int'
    assert reason("99999999999999999999", "int") == '"99999999999999999999" is not an int'
    assert convert("9223372036854775807", "int") == 9223372036854775807


def test_bools_and_empty_cells():
    """true and false only; an empty cell is null except in a string column"""
    assert convert("true", "bool") is True
    assert convert("false", "bool") is False
    assert reason("yes", "bool") == '"yes" is not a bool'
    assert convert("", "int") is None
    assert convert("", "bool") is None
    assert convert("", "rupees") is None
    assert convert("", "string") == ""
    assert convert("चाय और किताबें", "string") == "चाय और किताबें"

import csv
import io


def records(text):
    """(line, cells) for each record of the CSV text, line being the line the record starts on."""
    reader = csv.reader(io.StringIO(text))
    return [(reader.line_num, row) for row in reader]

from records import records

STOCK = (
    "code,title,pages,price,in_stock\n"
    "B-101,Monsoon Notes,212,349.00,true\n"
    'B-102,"Notes from\nthe Ghats",180,₹ 299,yes\n'
    "B-103,Tea and Trains,ninety-six,149,true\n"
    "B-104,City of Lakes,348\n"
)

EXPORT = "\ufeffcode,title\r\nB-101,Monsoon Notes\r\nB-102,\"Lakes, Hills and Rivers\"\r\n"


def test_start_lines_of_stock_csv():
    """the record after the quoted line break starts on line 5"""
    assert [line for line, _ in records(STOCK)] == [1, 2, 3, 5, 6]


def test_cells_of_stock_csv():
    """the quoted title keeps its line break, and B-104 has three cells"""
    rows = records(STOCK)
    assert rows[2][1][1] == "Notes from\nthe Ghats"
    assert rows[4] == (6, ["B-104", "City of Lakes", "348"])


def test_byte_order_mark_and_crlf():
    """the export's header starts with code, not with the mark"""
    assert records(EXPORT) == [(1, ["code", "title"]), (2, ["B-101", "Monsoon Notes"]), (3, ["B-102", "Lakes, Hills and Rivers"])]


def test_empty_lines_count_but_give_no_record():
    """the record after an empty line starts on line 3"""
    assert records("code,pages\n\nA,1\n") == [(1, ["code", "pages"]), (3, ["A", "1"])]

import csv
import io
import json
import re

INT = re.compile(r"-?[0-9]+")
RUPEES = re.compile(r"([0-9]+)(?:\.([0-9]{1,2}))?")
WORDS = {"int": "an int", "rupees": "rupees", "bool": "a bool"}
TYPES = ("string", "int", "rupees", "bool")


def convert(cell, kind):
    """The value of cell in a column of this kind, or ValueError with the reason it is not one (step 1)."""
    if cell == "":
        return "" if kind == "string" else None
    if kind == "string":
        return cell
    value = None
    if kind == "int" and INT.fullmatch(cell) and -(2**63) <= int(cell) < 2**63:
        value = int(cell)
    elif kind == "rupees" and RUPEES.fullmatch(cell):
        found = RUPEES.fullmatch(cell)
        value = int(found.group(1)) * 100 + int((found.group(2) or "").ljust(2, "0"))
    elif kind == "bool" and cell in ("true", "false"):
        value = cell == "true"
    if value is None:
        raise ValueError(f"{json.dumps(cell, ensure_ascii=False)} is not {WORDS[kind]}")
    return value


def records(text):
    """(line, cells) for each record, line being the line it starts on (step 2)."""
    if text.startswith("\ufeff"):
        text = text[1:]
    rows = []
    line = 1
    for row in csv.reader(io.StringIO(text)):
        if row:
            rows.append((line, row))
        line += 1 + sum(cell.count("\n") for cell in row)
    return rows


def run(schema_text, csv_text):
    """(stdout, stderr, status) of csv2json for a schema and a CSV text."""
    schema = json.loads(schema_text)
    rows = records(csv_text)
    objects = [dict(zip(rows[0][1], cells)) for _, cells in rows[1:]]
    return json.dumps(objects) + "\n", "", 0

from csv2json import run

SCHEMA = (
    '{"columns": [{"name": "code", "type": "string"}, {"name": "title", "type": "string"},'
    ' {"name": "pages", "type": "int"}, {"name": "price", "type": "rupees"}, {"name": "in_stock", "type": "bool"}]}'
)

BOOKS = (
    "code,title,pages,price,in_stock\n"
    "B-101,Monsoon Notes,212,349.00,true\n"
    'B-102,"Lakes, Hills and Rivers",348,499.5,false\n'
    'B-103,"The ""Quiet"" Train",96,149,true\n'
    "B-104,चाय और किताबें,,0.5,\n"
)

BOOKS_JSON = """[
  {
    "code": "B-101",
    "title": "Monsoon Notes",
    "pages": 212,
    "price": 34900,
    "in_stock": true
  },
  {
    "code": "B-102",
    "title": "Lakes, Hills and Rivers",
    "pages": 348,
    "price": 49950,
    "in_stock": false
  },
  {
    "code": "B-103",
    "title": "The \\"Quiet\\" Train",
    "pages": 96,
    "price": 14900,
    "in_stock": true
  },
  {
    "code": "B-104",
    "title": "चाय और किताबें",
    "pages": null,
    "price": 50,
    "in_stock": null
  }
]
"""

STOCK = (
    "code,title,pages,price,in_stock\n"
    "B-101,Monsoon Notes,212,349.00,true\n"
    'B-102,"Notes from\nthe Ghats",180,₹ 299,yes\n'
    "B-103,Tea and Trains,ninety-six,149,true\n"
    "B-104,City of Lakes,348\n"
)

STOCK_ERRORS = """line 3, column price: "₹ 299" is not rupees
line 3, column in_stock: "yes" is not a bool
line 5, column pages: "ninety-six" is not an int
line 6: expected 5 fields, found 3
"""


def test_books_csv():
    """the JSON csv2json printed for books.csv, byte for byte"""
    assert run(SCHEMA, BOOKS) == (BOOKS_JSON, "", 0)


def test_stock_csv():
    """four errors in file order, no JSON, status 1"""
    assert run(SCHEMA, STOCK) == ("", STOCK_ERRORS, 1)


def test_a_header_without_records():
    """an empty array"""
    assert run(SCHEMA, "code,title,pages,price,in_stock\n") == ("[]\n", "", 0)


def test_renamed_header():
    """renamed.csv says name where the schema says title"""
    out, err, status = run(SCHEMA, "code,name,pages,price,in_stock\nB-101,Monsoon Notes,212,349.00,true\n")
    assert (out, status) == ("", 2)
    assert err == "header does not match the schema: expected code,title,pages,price,in_stock, found code,name,pages,price,in_stock\n"


def test_unknown_type():
    """bad-schema.json has the type money"""
    bad = '{"columns": [{"name": "code", "type": "string"}, {"name": "price", "type": "money"}]}'
    assert run(bad, BOOKS) == ("", 'schema: column price: unknown type "money"\n', 2)

Then the self-check on the five ideas of the project, asked one at a time from the idea your answers show least, each answer with its reason:

Interview questions

Warm-up (fresher to mid-level): Why does csv2json report the line where a record starts, and how does it work it out?

Because that is where a person looks: an editor shows lines, and a record with a line break inside a quoted cell spans several of them, so its position among the records is not its line. In stock.csv, B-103 is the fourth record but starts on line 5, and the error says line 5. Crystal’s CSV reader returns records without lines, so csv2json counts them itself: it starts at 1 and, after each record, adds one for the record’s own line break and one for every line break inside its cells. The rule is written into the specification, because readers differ: Python’s csv.reader.line_num counts the lines read so far, which after a record of two lines is the line where it ends, 4 for B-102 where csv2json says 3.

Two longer questions, on two tools that disagree about line numbers and on an export too large for memory:

Key takeaways

• A specification written first fixes the schema, the cell rules, the start line of each record, the errors and the JSON’s layout, so that two implementations print the same bytes.
• JSON::Serializable reads the schema into typed classes and names a missing field itself, and Value | Bad makes every caller handle a cell that breaks its rule.
• A record’s line is where it starts: add one and the line breaks inside its cells after each record, and drop a byte-order mark before the header check.
• Conversions of a library accept more than a specification allows, and a price through a float lost a paisa, so csv2json tests each cell with its own rule and counts rupees in whole paise.
• json2csv shows that the JSON is the canonical form, the specs test each rule with the cells that break it, and the release build loads only two libraries of macOS.