# csv

> Read CSV into objects or rows of cells, write it back with correct quoting, and handle messy files.

This is the Markdown version of https://osl-lang.org/packages/csv. Every page of the OSL docs has one: add .md to its path. https://osl-lang.org/llms.txt lists them all.

```osl
import "std:csv"
```

`std:csv` reads and writes comma-separated values: spreadsheet exports, bank statements, database dumps. It handles the quoting (commas, quotes and newlines inside cells), so you never split lines by hand.

```osl
import "std:csv"
```

## Parse into objects

`csv.parse` treats the first row as column names and returns one object per remaining row. It returns a [result](/packages/result.md), since the text may be malformed.

```osl
import "std:csv"

string text = "name,city,age\nAda,London,36\nGrace,New York,85\n"
auto people = csv.parse(text).unwrap()
log people.len
for p in people (
  log p.name "lives in" p.city
)
```

Output:

```text
2
Ada lives in London
Grace lives in New York
```

Every cell is a string, because CSV has no types. Convert what you need with `tryAs`, which is a result too, so a bad cell doesn't crash the program:

```osl
import "std:csv"

auto rows = csv.parse("item,qty\nbolt,12\nnut,many\nwasher,7\n").unwrap()
int total = 0
for r in rows (
  switch string(r.qty).tryAs<int> (
    case ok(n)
      total += n
    case err(e)
      log "bad quantity for" r.item ":" r.qty
  )
)
log "total" total
```

Output:

```text
bad quantity for nut : many
total 19
```

Try it: add a `Cy,Rome,40` row and run again.

## Quoted cells

Cells with commas, quotes or newlines are wrapped in double quotes, and a quote inside is doubled. `parse` unwraps them.

```osl
import "std:csv"

string text = "name,quote\n\"Smith, J\",\"He said \"\"hi\"\"\"\nLee,\"two\nlines\"\n"
for r in csv.parse(text).unwrap() (
  log r.name "|" r.quote
)
```

Output:

```text
Smith, J | He said "hi"
Lee | two
lines
```

Try it: put a comma in a name without quotes and see what happens to the row.

## Rows of cells

Objects don't keep column order, and a header can be missing or wrong. `csv.parseRows` returns plain rows of strings, header included, in file order, and never complains about uneven row lengths.

```osl
import "std:csv"

auto rows = csv.parseRows("a,b,c\n1,2\n3,4,5,6\n").unwrap()
for r in rows ( log r.len r )
```

Output:

```text
3 ["a","b","c"]
2 ["1","2"]
4 ["3","4","5","6"]
```

## Other delimiters

Tab-separated and semicolon-separated files are common. Pass `delimiter` (one character).

```osl
import "std:csv"

auto rows = csv.parse("id;price\n1;2,50\n2;3,10\n", {delimiter: ";"}).unwrap()
for r in rows ( log r.id r.price )
log csv.parseRows("a\tb\n1\t2", {delimiter: "\t"}).unwrap()
```

Output:

```text
1 2,50
2 3,10
[["a","b"],["1","2"]]
```

## When the input is bad

A `ParseError` names the line, and for quoting problems also the column. A row with more cells than the header, a repeated column name and an unclosed quote all fail. A row with fewer cells just leaves the rest empty.

```osl
import "std:csv"

string[] inputs = ["a,b\n1,2,3", "a,a\n1,2", "a,b\n\"1,2", "a,b\n1"]
for text in inputs (
  switch csv.parse(text) (
    case ok(rows)
      log "ok" rows
    case err(e)
      log e.kind e.message
  )
)
log csv.isValid("a,b\n1,2") csv.isValid("a,b\n\"1")
```

Output:

```text
ParseError csv.parse: line 2: 3 fields, but the header has 2 columns
ParseError csv.parse: line 1: the column "a" appears more than once
ParseError csv.parse: line 2, column 5: extraneous or missing " in quoted-field
ok [{"a":"1","b":""}]
true false
```

## Write CSV

`csv.stringify` takes an array of objects (a header row, then one row each) or an array of arrays (rows as they are). It quotes cells that need it. Columns default to every key used, sorted. Pass `columns` to choose and order them, and `header: false` to leave out the header row.

```osl
import "std:csv"

auto data = [
  {name: "Ann, Jr", age: 3, note: "say \"hi\""},
  {name: "Bo", age: 4}
]
log csv.stringify(data)
log csv.stringify(data, {columns: ["name", "age"]})
log csv.stringify(data, {columns: ["age"], header: false})
log csv.stringify([["a", 1], ["b", null]])
```

Output:

```text
age,name,note
3,"Ann, Jr","say ""hi"""
4,Bo,

name,age
"Ann, Jr",3
Bo,4

3
4

a,1
b,
```

Missing keys and `null` become empty cells. The output ends with a newline, which `log` shows as a blank line.

Try it: change `columns` to `["age", "name"]`.

## Files

`csv.parseFile` and `csv.parseRowsFile` read a file directly. Write the output with [fs](/packages/fs.md).

```osl
import "std:csv"
import "std:fs"

auto people = [{name: "Ada", year: 1815}, {name: "Alan", year: 1912}]
string text = csv.stringify(people, {columns: ["name", "year"]})
fs.writeFile("people.csv", text).unwrap()
for p in csv.parseFile("people.csv").unwrap() (
  log p.name p.year
)
log csv.parseFile("missing.csv").unwrapErr().kind
fs.remove("people.csv").unwrap()
```

Output:

```text
Ada 1815
Alan 1912
NotFoundError
```

## Group and total

Read a sales export, total by category, and write a report:

```osl
import "std:csv"

string text = `category,item,amount
fruit,apple,3
veg,leek,2
fruit,pear,4
veg,kale,5
fruit,plum,1
`

string[number] totals = {}
for r in csv.parse(text).unwrap() (
  string cat = r.category
  number amount = string(r.amount).tryAs<number>.unwrapOr(0)
  if totals.contains(cat) ( totals[cat] += amount ) else ( totals[cat] = amount )
)

object[] report = []
for cat in totals.getKeys().sort() (
  report.append({category: cat, total: totals[cat]})
)
log csv.stringify(report, {columns: ["category", "total"]})
```

Output:

```text
category,total
fruit,8
veg,7
```

### Exercise: Clean a contact list

Parse the CSV text. Keep only people whose `email` contains an `@`, and print them as `name <email>`, one per line, followed by how many rows were dropped.

Starting code:

```osl
import "std:csv"

string text = "name,email\nAda,ada@example.com\nBob,not-an-email\nCy,cy@example.org\n"
auto rows = csv.parse(text).unwrap()
log rows.len
```

It should print:

```text
Ada <ada@example.com>
Cy <cy@example.org>
dropped 1
```

Hint 1: Keep a counter of dropped rows and `continue` when an email has no `@`.

Hint 2: Strings have `.contains("@")`.

A solution:

```osl
import "std:csv"

string text = "name,email\nAda,ada@example.com\nBob,not-an-email\nCy,cy@example.org\n"
auto rows = csv.parse(text).unwrap()
int dropped = 0
for r in rows (
  string email = r.email
  if !email.contains("@") (
    dropped++
    continue
  )
  log r.name ++ " <" ++ email ++ ">"
)
log "dropped" dropped
```

**Quiz.** What type is every cell `csv.parse` gives you?

- [ ] `int` when it looks like a number.
- [x] `string`, always.
- [ ] `any`, decided by the column.

Answer: CSV has no types, so convert with `tryAs<int>` or `tryAs<number>` and handle the failure.

### Exercise: Double the scores

Parse the CSV and write a new one with the columns `name` and `double`, where `double` is twice the score.

Starting code:

```osl
import "std:csv"

string text = "name,score\nAda,5\nBo,7\n"
log csv.parse(text).unwrap().len
```

It should print:

```text
name,double
Ada,10
Bo,14
```

Hint 1: Build an array of objects with `{name: ..., double: ...}`.

Hint 2: `string(r.score).tryAs<int>.unwrapOr(0)` converts a cell.

Hint 3: `csv.stringify(rows, {columns: ["name", "double"]})`

A solution:

```osl
import "std:csv"

string text = "name,score\nAda,5\nBo,7\n"
object[] out = []
for r in csv.parse(text).unwrap() (
  int score = string(r.score).tryAs<int>.unwrapOr(0)
  out.append({name: r.name, double: score * 2})
)
log csv.stringify(out, {columns: ["name", "double"]})
```

## API reference

## API reference

Every member of `std:csv`, generated from the source of OSL.

### Functions

#### `csv.addColumn(object[] data, any column, any defaultValue) object[]`

**Deprecated.** Deprecated: set the key in a for loop instead.

addColumn returns copies of the objects with column set to defaultValue.

#### `csv.aggregate(object[] data, any key, any value, any ...fn) object`

**Deprecated.** Deprecated: group the records with a for loop instead.

aggregate groups the objects by key and calls fn with each group's
values for value.

#### `csv.appendRow(object[] data, object row) object[]`

**Deprecated.** Deprecated: use records.append(row) instead.

appendRow returns a copy of data with row added at the end.

#### `csv.chunk(object[] data, any size) object[][]`

**Deprecated.** Deprecated: split the records with a for loop and .trim() instead.

chunk splits the objects into arrays of size (10 when size is not
positive).

#### `csv.count(object[] data, any key) string[int]`

**Deprecated.** Deprecated: count with a for loop instead.

count counts the objects for each value of key.

#### `csv.filter(object[] data, any fn) object[]`

**Deprecated.** Deprecated: use records.filter(fn) instead.

filter keeps the objects fn returns true for.

#### `csv.flatten(object[] data, any separator) object[]`

**Deprecated.** Deprecated: flatten the records with a for loop instead.

flatten lifts one level of nested objects into keys joined by separator
("_" when empty).

#### `csv.fromRows(array[] rows) object[]`

**Deprecated.** Deprecated: use csv.parse(csv.stringify(rows)) or a loop instead.

fromRows turns a header row plus rows of values into objects.

#### `csv.getColumn(object[] data, any column) array`

**Deprecated.** Deprecated: use records.map(row -> row[column]) instead.

getColumn lists one key's value from every object.

#### `csv.groupBy(object[] data, any key) string[object[]]`

**Deprecated.** Deprecated: group the records with a for loop instead.

groupBy groups the objects by their value for key.

#### `csv.isValid(string text) boolean`

isValid reports whether csv.parse would accept text with the default
options.

#### `csv.join(object[] data1, object[] data2, any key) object[]`

**Deprecated.** Deprecated: join the records with a for loop instead.

join pairs objects from data1 and data2 with equal values for key, with
data2's keys suffixed "_2".

#### `csv.mapRows(object[] data, any fn) object[]`

**Deprecated.** Deprecated: use records.map(fn) instead.

mapRows replaces each object with what fn returns for it.

#### `csv.merge(object[] data1, object[] data2) object[]`

**Deprecated.** Deprecated: use data1.concat(data2) instead.

merge concatenates two arrays of objects.

#### `csv.parse(string text, object? options) result<object[], error>`

parse reads CSV text whose first row names the columns and returns one
object per remaining row, mapping each column name to that row's cell as
a string. A row with fewer cells than columns leaves the rest as "".
Objects don't keep column order; pass columns to csv.stringify to choose
it. Options: delimiter (one character, default ","). Fails with
ParseError, naming the line and column, for malformed quoting, a
repeated column name or a row with more cells than columns.

#### `csv.parseFile(string path, object? options) result<object[], error>`

parseFile reads the CSV file at path like csv.parse, with the same
options. Fails with NotFoundError, PermissionError or IOError when the
file can't be read and ParseError when it isn't valid CSV.

#### `csv.parseRaw(any data) array[]`

**Deprecated.** Deprecated: use csv.parseRows(text) instead.

parseRaw reads CSV text as rows of cells, or [] when it is malformed.

#### `csv.parseRows(string text, object? options) result<string[][], error>`

parseRows reads CSV text as rows of string cells, header row included,
keeping every row's length and the column order. Options: delimiter (one
character, default ","). Fails with ParseError, naming the line and
column, for malformed quoting.

#### `csv.parseRowsFile(string path, object? options) result<string[][], error>`

parseRowsFile reads the CSV file at path like csv.parseRows, with the
same options. Fails with NotFoundError, PermissionError or IOError when
the file can't be read and ParseError when it isn't valid CSV.

#### `csv.pivot(object[] data, any keyColumn, any valueColumn, any ...pivotColumn) string[object]`

**Deprecated.** Deprecated: build the object with a for loop instead.

pivot builds {key: {pivot: value}} from three columns.

#### `csv.prependRow(object[] data, object row) object[]`

**Deprecated.** Deprecated: use records.prepend(row) instead.

prependRow returns a copy of data with row added at the start.

#### `csv.readFile(any path) object[]`

**Deprecated.** Deprecated: use csv.parseFile(path) instead.

readFile reads and parses a CSV file, or [] when it can't be read or
parsed.

#### `csv.readFileRaw(any path) array[]`

**Deprecated.** Deprecated: use csv.parseRowsFile(path) instead.

readFileRaw reads a CSV file as rows of cells, or [] when it can't be
read or parsed.

#### `csv.reduce(object[] data, any initial, any fn) any`

**Deprecated.** Deprecated: use a for loop instead.

reduce folds the objects into one value with fn(accumulator, row).

#### `csv.removeColumn(object[] data, any column) object[]`

**Deprecated.** Deprecated: delete the key in a for loop, or pass columns to csv.stringify, instead.

removeColumn returns copies of the objects without column.

#### `csv.renameColumn(object[] data, any oldColumn, any newColumn) object[]`

**Deprecated.** Deprecated: rename the key in a for loop instead.

renameColumn returns copies of the objects with oldColumn renamed.

#### `csv.sample(object[] data, any count) object[]`

**Deprecated.** Deprecated: use random.sample(records, count) instead.

sample picks count distinct objects at random.

#### `csv.sortBy(object[] data, any key) object[]`

**Deprecated.** Deprecated: use records.sortBy(key) instead.

sortBy sorts the objects by key as text.

#### `csv.sortByNum(object[] data, any key) object[]`

**Deprecated.** Deprecated: use records.sortBy(row -> number(row[key])) instead.

sortByNum sorts the objects by key as numbers.

#### `csv.stats(object[] data, any key) object`

**Deprecated.** Deprecated: use records.map(row -> number(row[key])) with .sum(), .min(), .max() and .average() instead.

stats summarises key's values as numbers: count, min, max, average and
sum. With no rows, count and sum are 0 and the others are null.

#### `csv.stringify(any data, object? options) string`

stringify writes data as CSV text. data is an array of objects, written
as a header row of column names followed by one row per object, or an
array of arrays, written as rows of cells as they are. Cells holding null
or a missing key are empty; other values are written as OSL prints them.
Options: delimiter (one character, default ","); for objects, columns
(the column names to write, in order; default every key used, sorted)
and header (write the header row; default true). A single object is
accepted as one row for old scripts. Throws TypeError when data is not
one of these shapes.

#### `csv.stringifyArray(array[] data) string`

**Deprecated.** Deprecated: use csv.stringify(rows) instead.

stringifyArray writes rows of cells as CSV.

#### `csv.stringifyRows(object[] data) string`

**Deprecated.** Deprecated: use csv.stringify(records) instead.

stringifyRows writes objects as CSV, taking the columns from the first
object's keys in sorted order.

#### `csv.toRows(object[] data) array[]`

**Deprecated.** Deprecated: write the rows with records.map(...) instead.

toRows turns objects into a header row plus rows of values.

#### `csv.transpose(array[] data) array[]`

**Deprecated.** Deprecated: transpose with for loops instead.

transpose swaps the rows and columns of an array of arrays.

#### `csv.unique(object[] data, any key) array`

**Deprecated.** Deprecated: use records.map(row -> row[key]).toSet() instead.

unique lists the distinct values of key as text, in first-seen order.

#### `csv.writeFile(any path, object data) boolean`

**Deprecated.** Deprecated: use fs.writeFile(path, csv.stringify([data])) instead.

writeFile writes one object as a two-line CSV file and reports success.

#### `csv.writeFileArray(any path, array[] data) boolean`

**Deprecated.** Deprecated: use fs.writeFile(path, csv.stringify(rows)) instead.

writeFileArray writes rows of cells as a CSV file and reports success.

#### `csv.writeFileRows(any path, object[] data) boolean`

**Deprecated.** Deprecated: use fs.writeFile(path, csv.stringify(records)) instead.

writeFileRows writes objects as a CSV file and reports success.