Data in files

Problem

Text, but you need numbers

The warehouse exports its products to a file. Each row is one product: name, price and stock, separated with commas. The first row says what is in each column:

products.csv

Name,Price,Stock
Bread,1.20,30
Milk,2.10,18
Coffee beans,18.90,12

You can already read the file. But to the program, the row is just text:

const line = "Bread,1.20,30";
console.log(line.length);
console.log(line[6]);

Output

13
1

It does not know that 1.20 is a price and 30 is a count. To work out what the goods are worth, you have to turn each row into something with fields: a Product object.

The idea

CSV: row to object

A file like this is called CSV (comma-separated values). Excel reads it, databases read it, any program reads it. A row becomes an object in three steps: split, turning each part into the type you need, and the constructor.

It is most convenient to make this a static method (static) in the class itself:

function toNumber(text) {
  const n = Number(text);
  if (text.trim() === "" || Number.isNaN(n)) {
    throw new Error(`bad number (${text.trim()})`);
  }
  return n;
}

class Product {
  constructor(name, price, stock) {
    this.name = name;
    this.price = price;
    this.stock = stock;
  }

  static fromCsv(line) {
    const f = line.split(",");
    if (f.length !== 3) {
      throw new Error(`expected 3 columns, got ${f.length}`);
    }
    return new Product(f[0].trim(), toNumber(f[1]), toNumber(f[2]));
  }

  toCsv() {
    return `${this.name},${this.price},${this.stock}`;
  }
}

const p = Product.fromCsv("Coffee beans,18.90,12");
console.log(`${p.name}: €${p.price.toFixed(2)} x ${p.stock} = €${(p.price * p.stock).toFixed(2)}`);

Output

Coffee beans: €18.90 x 12 = €226.80

fromCsv is the only place that knows what the row looks like. If the format changes, you change only that. The check for the number of columns throws an error with a clear message, instead of the program stopping later with an unclear one.

Number("seven") does not throw an error. It gives NaN ("not a number"), and Number("") gives 0. A program that does not check quietly does math with wrong numbers. That is why toNumber checks the text and throws by itself.

What fromCsv does with the row, step by step, with its body spelled out line by line:

const line = "Coffee beans,18.90,12";
const f = line.split(",");
const name = f[0];
const price = Number(f[1]);
const stock = Number(f[2]);
const p = new Product(name, price, stock);
console.log(`${p.name}: €${p.price} x ${p.stock}`);
console.log(`€${(p.price * p.stock).toFixed(2)}`);

Step by step, in words

1. The row from the file is one string. To the program, the 18.90 in it is just four characters.
2. split cuts the row at every comma: an array of three strings.
3. The name stays a string.
4. Number turns the string "18.90" into the number 18.9. Now you can do math with it.
5. Number makes the number 12 from "12". In JavaScript both the price and the count are a number.
6. new Product puts the three values in the fields of a new object, and p points to it.
7. p.price prints as 18.9: the zero at the end was only in the text.
8. The price times the stock: now it is math with numbers.

See it

The whole file

You load all the products into an array and work out the value of the goods. Here and from now on, import { readFileSync, writeFileSync } from "node:fs"; is at the top and is not shown:

try {
  writeFileSync("products.csv", "Name,Price,Stock\nBread,1.20,30\nMilk,2.10,18\nCoffee beans,18.90,12\n");
  const lines = readFileSync("products.csv", "utf8").trimEnd().split("\n");
  const products = [];
  let header = true;
  for (const line of lines) {
    if (header) {
      header = false;
      continue;
    }
    products.push(Product.fromCsv(line));
  }
  let value = 0;
  for (const p of products) {
    value += p.price * p.stock;
  }
  console.log(`${products.length} products, stock worth €${value.toFixed(2)}`);
} catch (e) {
  console.log(`Cannot read products.csv: ${e.message}`);
}

Output

3 products, stock worth €300.60

• products starts as an empty array, and push adds each new product at its end. products.length is how many there are.
• header is true only on the first pass. Then the row is skipped with continue.

There is more about arrays that grow in the module "Collections".

The idea

Bad rows

People and other programs write the files. Sooner or later a row comes with a missing column, a word instead of a number, or an empty row:

products.csv

Name,Price,Stock
Bread,1.20,30
Milk,2.10
Coffee,seven,4

Tea,3.50,25

One error should not stop the loading of everything else. Each row is in its own try, and the message gives the row number, so that someone can fix it:

try {
  writeFileSync("products.csv", "Name,Price,Stock\nBread,1.20,30\nMilk,2.10\nCoffee,seven,4\n\nTea,3.50,25\n");
  const lines = readFileSync("products.csv", "utf8").trimEnd().split("\n");
  const products = [];
  let row = 0;
  for (const line of lines) {
    row++;
    if (row === 1 || line.trim() === "") {
      continue;
    }
    try {
      products.push(Product.fromCsv(line));
    } catch (e) {
      console.log(`Row ${row}: ${e.message}`);
    }
  }
  console.log(`Loaded: ${products.length}`);
} catch (e) {
  console.log(`Cannot read products.csv: ${e.message}`);
}

Output

Row 3: expected 3 columns, got 2
Row 4: bad number (seven)
Loaded: 2

• Empty rows are skipped with line.trim() === "".
• One catch catches both errors that fromCsv throws: for the number of columns and from toNumber. e.message tells you which one happened.
• The program says "Loaded: 2" and carries on with them.

Try it

Empty columns

In the first row the price is empty, in the second the stock. The third row turns an empty column into a number:

What will the program print?

console.log("Coffee,,4".split(",").length);
console.log("Coffee,7.50,".split(",").length);
console.log(Number(""));

Answer

3
2
NaN

3
3
0

3
3
NaN

Check

The idea

Objects to CSV

The other direction is a method that builds the row:

toCsv() {
  return `${this.name},${this.price},${this.stock}`;
}

You write the header row, and then one row for each object:

const stock = [new Product("Bread", 1.2, 30), new Product("Milk", 2.1, 18)];
try {
  let text = "Name,Price,Stock\n";
  for (const p of stock) {
    text += `${p.toCsv()}\n`;
  }
  writeFileSync("stock.csv", text);
  process.stdout.write(readFileSync("stock.csv", "utf8"));
} catch (e) {
  console.log(`Cannot write stock.csv: ${e.message}`);
}

Output

Name,Price,Stock
Bread,1.2,30
Milk,2.1,18

fromCsv can read this file back. Saving and reading have to use the same format.

Watch out if you format the prices with toLocaleString. It follows the language settings, and in German, Bulgarian and many other languages the decimal mark is a comma:

const de = `Bread,${(1.2).toLocaleString("de-DE", { minimumFractionDigits: 2 })},30`;
console.log(`${de}  -> ${de.split(",").length} columns`);
const fixed = `Bread,${(1.2).toFixed(2)},30`;
console.log(`${fixed}  -> ${fixed.split(",").length} columns`);

Output

Bread,1,20,30  -> 4 columns
Bread,1.20,30  -> 3 columns

1,20 becomes two columns, and the rows shift. For files that another program will read, use toFixed(2). It always gives a point, on every computer.

Try it

Find the bug

The program should load the three products from products.csv, but it stops on the very first row:

Error

Error: bad number (Price)

Pick the line with the bug.

1let row = 0;2let loaded = 0;3for (const line of lines) {4 row++;5 if (row === 0) {6 continue;7 }8 const p = Product.fromCsv(line);9 loaded++;10}11console.log(`Loaded: ${loaded}`);

Check

The idea

Columns by name

Another program exports the same data, but with the columns in a different order and with one more. If you write f[1] for the price, with a new format you will read the wrong column and not notice. It is safer to find the column by its name in the header row:

function column(header, name) {
  for (let i = 0; i < header.length; i++) {
    if (header[i].trim().toLowerCase() === name.toLowerCase()) {
      return i;
    }
  }
  throw new Error(`No column ${name}`);
}

try {
  writeFileSync("export.csv", "Stock,Name,Code,Price\n30,Bread,1001,1.20\n18,Milk,1002,2.10\n");
  let nameCol = -1;
  let priceCol = -1;
  for (const line of readFileSync("export.csv", "utf8").trimEnd().split("\n")) {
    const f = line.split(",");
    if (nameCol === -1) {
      nameCol = column(f, "name");
      priceCol = column(f, "price");
      continue;
    }
    console.log(`${f[nameCol]}: €${f[priceCol]}`);
  }
} catch (e) {
  console.log(e.message);
}

Output

Bread: €1.20
Milk: €2.10

The first pass only finds where the columns are. After that, each row is read by those indexes. If the column is not there, column throws an exception right away, instead of reading wrong data.

More precisely, if you are curious

• A comma in the value. "Cheddar, aged" has a comma in it. In CSV such a value goes in quotes: "Cheddar, aged",9.80,5, and a quote inside is written double: "". Then split(",") is no longer enough. Node has no built-in module for CSV. Real projects use a library such as csv-parse or Papa Parse, which knows all the rules.
• Another separator. Excel on a computer with German or Bulgarian settings often saves CSV with ;, exactly because the comma is the decimal mark. Then you cut the row with split(";").
• JSON. For data that only your program will read, JavaScript most often uses JSON: JSON.stringify(product) makes text from the object, and JSON.parse(text) turns it back.

Exercise

Data that comes alive

Solve the tasks in your editor. The program first creates the input file itself.

1. CSV analysis. Create people.csv with the contents below. Print the average age (integer division), who is the oldest, and how many people there are from each city, in the order the cities appear. Keep the cities in one array, and the counts in another, at the same indexes.

people.csv

Name,Age,City
Ivan,34,Sofia
Maria,28,Plovdiv
Georgi,41,Sofia
Elena,25,Varna

Output

Average age: 32
Oldest: Georgi, 41
People from Sofia: 2
People from Plovdiv: 1
People from Varna: 1

2. Saving the hero. A Hero class with a name, level, health and gold. The method save(path) saves the hero as key=value lines, and the static method load(path) creates a new Hero from such a file. Create "Arin" with level 6, 100 health and 120 gold, raise his level by 1, take away 15 health, give him 220 gold, save him, print the file and load it back.

Output

name=Arin
level=7
health=85
gold=340
Loaded: Arin, level 7, health 85, gold 340

3. Duplicate text. Create text.txt with the text below. Split it into sentences at the period (and the spaces after it), and print each sentence that appears more than once, one time.

text.txt

This is text. This is also text. This is text.

Output

Duplicate text: "This is text."

More tasks

• The most expensive product. Load products.csv from the lesson, and print the product with the highest price and how many products have stock under 20.
• Fixed rows. Load the file with the bad rows, and write the good ones to products-clean.csv, and the bad ones, with the row number and the reason, to rejected.txt.
• Sales report. A file sales.csv with the columns Date,Product,Count,Price. The rows are sorted by date. Print the revenue for each day with padEnd and toFixed in columns.

Test

Check yourself

1. What is the first row of most CSV files?

The first record with dataA header row with the names of the columnsThe number of rows

2. What does Number("seven") return?

0It throws an errorNaN

3. What is "Coffee,7.50,".split(",").length?

312

4. You save prices with price.toLocaleString("de-DE", { minimumFractionDigits: 2 }). What happens to the CSV file?

Nothing, the prices have a pointThe prices come out with a comma, and each one becomes two columnsThe program stops with an error

Check

Start the lesson over