csv

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

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, since the text may be malformed.

main.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
)

Click the code to edit it

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:

main.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

Click the code to edit it

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.

main.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
)

Click the code to edit it

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.

main.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 )

Click the code to edit it

Other delimiters#

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

main.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()

Click the code to edit it

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.

main.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")

Click the code to edit it

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.

main.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]])

Click the code to edit it

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.

main.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()

Click the code to edit it

Group and total#

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

main.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"]})

Click the code to edit it

Your turn

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.

main.osl

import "std:csv"

string text = "name,email\nAda,[email protected]\nBob,not-an-email\nCy,[email protected]\n"
auto rows = csv.parse(text).unwrap()
log rows.len

import "std:csv"

string text = "name,email\nAda,[email protected]\nBob,not-an-email\nCy,[email protected]\n"
auto rows = csv.parse(text).unwrap()
log rows.len

Ctrl Enter runs

Expected output

Ada <[email protected]>
Cy <[email protected]>
dropped 1

Quick check

What type is every cell csv.parse gives you?

Your turn

Double the scores

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

main.osl

import "std:csv"

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

import "std:csv"

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

Ctrl Enter runs

Expected output

name,double
Ada,10
Bo,14

API reference#

39 members, generated from the std:csv source of OSL 9.3.0.

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.

Edit this page View as Markdown