Simplecsv

The Go package simplecsv is a simple mini-library to handle csv files. I'm building it to help me writing small command line scripts. Maybe it's useful to someone else as well.

Some notes:

• all read methods return the value in the csv and a second true/false value that is true if the value exists
• all write methods that change the csv return the changed csv and a true/false value if the operation was successful
• all cells are strings
• methods that return a csv never modify the original csv and share no data with it: the returned csv is an independent copy. Get methods like GetRow and GetHeaders return copies
• header names (the first row) must be unique, like database columns. Reading a file whose header row has duplicate names, creating a csv with duplicate headers, or renaming a header to a name that already exists is rejected. This guarantees name-based lookups (GetCellByField, FindInField, SortByField, GetRowAsMap, the *FromMap functions, etc.) address exactly one column.
• all rows have the same number of cells as the header row (uniform row width). Reading a ragged file (rows with more or fewer fields than the header row) is rejected with an error, and AddRow / SetRow reject a row whose length is not the header width. This makes "the number of columns" unambiguous and keeps row operations consistent.
• ReadCsv, ReadCsvFile, ReadCsvFileE and ReadCsvFileComma read the whole input into memory. For large or attacker-controlled input, use ReadCsvLimit with positive record and byte limits.
• find and match (FindInColumn, FindInField, MatchInColumn, MatchInField) are case-insensitive by default: Foo = foo = FOO. Use the *CaseSensitive variants (FindInColumnCaseSensitive, FindInFieldCaseSensitive, MatchInColumnCaseSensitive, MatchInFieldCaseSensitive) when case matters.
• CSV / formula injection: values that begin with =, +, -, @, a tab or a carriage return are interpreted as formulas by spreadsheet applications (Excel, LibreOffice Calc, Google Sheets). The Write* functions write values verbatim and do not neutralize such values, because escaping changes data. When the csv may contain attacker-controlled data (exported logs, form input, scraped content) and may be opened in a spreadsheet, call SanitizeFormulas first to prefix those cells with a single quote '. Do not open untrusted CSVs in a spreadsheet without sanitization. Callers that do not want their bytes changed should not call it.
• writes are atomic and not world-readable: WriteCsvFile / WriteCsvFileE / WriteCsvFileComma write the csv to a temporary file in the same directory as the destination and then rename it over the destination, so a failed or interrupted write cannot truncate or corrupt the destination. The file (new or overwritten) ends up with mode 0600 (owner-readable only); callers that need different permissions can os.Chmod the result. Because the destination is replaced with os.Rename rather than opened, a symlink planted at the destination is replaced instead of followed.

Installation

go get github.com/osvik/simplecsv

Then import it in your code:

import "github.com/osvik/simplecsv"

Examples

Csv file

Simplecsv works with comma separated csv files.

Read

Reads file and parses as a SimpleCsv object. fileRead is false if there's an error reading the file or parsing the CSV, if the file's header row contains duplicate names (headers must be unique), or if the file is ragged (rows with a different number of fields than the header row are rejected; uniform row width is an invariant).

var x simplecsv.SimpleCsv
var fileRead bool
x, fileRead = simplecsv.ReadCsvFile("my1file.csv")

All read functions (file-based and reader-based, limited or not) strip a UTF-8 byte order mark (BOM) from the first cell of the first row if present. Excel and many Windows tools write one (EF BB BF) at the start of the file; without stripping, the first header would become "\ufeffid" and every field lookup would silently fail. Only the first cell of the first row is touched.

Create

Create empty file and define csv headers. Header names must be unique; if they are not, the returned error is non-nil and the returned csv is nil:

var u simplecsv.SimpleCsv
var err error
u, err = simplecsv.CreateEmptyCsv([]string{"Age", "Gender", "ID"})
if err != nil {
	log.Fatal(err)
}

MustCreateEmptyCsv is the same but panics when the headers are invalid (empty or duplicated). Handy in tests and small scripts where a valid header list is part of the program, not a runtime condition:

u := simplecsv.MustCreateEmptyCsv([]string{"Age", "Gender", "ID"})

Write

Write the SimpleCsv object to my2file.csv. If there's an error, wasWritten is false.

wasWritten := u.WriteCsvFile("my2file.csv")

The write is atomic: the csv is written to a temp file in the same directory and renamed over the destination, so a failed or interrupted write cannot truncate or corrupt the destination. The file (new or overwritten) ends up with mode 0600 (owner-readable only); callers that need different permissions can os.Chmod the result. A symlink at the destination is replaced rather than followed.

Sanitize (CSV / formula injection)

The Write* functions write values verbatim. A value that begins with =, +, -, @, a tab or a carriage return is interpreted as a formula by spreadsheet applications (Excel, LibreOffice Calc, Google Sheets), and can be used to execute commands or read other cells when the file is opened. This is a real risk when the csv contains data that may be attacker-controlled (exported logs, form input, scraped content) and is later opened in a spreadsheet. Do not open untrusted CSVs in a spreadsheet without sanitization.

SanitizeFormulas returns an independent copy of the csv in which every such cell is prefixed with a single quote ' (the spreadsheet convention), so the value is treated as text. Escaping changes data (for example -5 becomes '-5, though the spreadsheet hides the quote and displays -5 as text), so sanitizing is opt-in. The original csv is not modified.

safe := u.SanitizeFormulas()
wasWritten := safe.WriteCsvFile("export.csv")
err := safe.WriteTo(os.Stdout, ',')

Errors

ReadCsvFileE and WriteCsvFileE work like ReadCsvFile and WriteCsvFile but return an error with the reason of the failure:

x, err := simplecsv.ReadCsvFileE("my1file.csv")
if err != nil {
	log.Fatal(err)
}

The joins, GroupBy and Concat have their own E variants; see Error variants.

err := u.WriteCsvFileE("my2file.csv")

ReadCsv and the file-reading wrappers read the whole input into memory. For large or attacker-controlled input, ReadCsvLimit reads one record at a time and rejects input over the configured limits. maxRecords includes the header row, maxBytes limits input bytes, zero disables the corresponding limit, and negative limits are rejected:

file, err := os.Open("large.csv")
if err != nil {
	log.Fatal(err)
}
defer file.Close()
x, err := simplecsv.ReadCsvLimit(file, ',', 10000, 10*1024*1024)
if err != nil {
	log.Fatal(err)
}

Delimiters, readers and writers

Simplecsv uses , as the default field separator, but it can read and write files with other separators, like ; or tabs, and it can read from any io.Reader (for example os.Stdin) and write to any io.Writer (for example os.Stdout):

x, err := simplecsv.ReadCsvFileComma("semicolonfile.csv", ';')

x, err := simplecsv.ReadCsv(os.Stdin, '\t')

err := x.WriteCsvFileComma("semicolonfile.csv", ';')

err := x.WriteTo(os.Stdout, ',')

Headers

The cells of the first row are considered headers.

Get

Get all headers:

headers := x.GetHeaders()

Get header at position one (second position as it starts from 0):

headerName, headerExists := x.GetHeader(1)

Get header position: (it returns -1 if the header does not exist)

position := x.GetHeaderPosition("Gender")

Rename

Rename header: (old header, new header)

x, headerExists := x.RenameHeader("ID", "IDnumber")

headerExists is false if the old header does not exist, or if IDnumber already exists as another column: header names must stay unique, so a rename that would produce a duplicate is rejected. Renaming a header to its own name is a no-op and is allowed.

Rows

Get

Get number of rows:

numberOfRows := x.GetNumberRows()

Get second row:

row, rowExists := x.GetRow(1)

Get second row as a map:

row, rowExists := x.GetRowAsMap(1)

Add

Add a slice to a row. The slice must have the same size as the CSV number of columns. If not wasSuccessful is false.

var wasSuccessful bool
x, wasSuccessful = x.AddRow([]string{"24", "M", "2986732"})

Add row from map: (If the map keys don't exist as columns, the value will be discarded. If a key does not exist, it will create empty cells.)

mymap := make(map[string]string)
mymap["Age"] = "62"
mymap["Gender"] = "F"
mymap["ID"] = "6463246"
var wasAdded bool 
x, wasAdded = x.AddRowFromMap(mymap)

Set

Set second row (1) from a slice. The length of the slice must be the same as the number of columns and the row must already exist. If there’s an error wasSet is false.

var wasSet bool
x, wasSet = x.SetRow(1, []string{"45", "F", "8356138"})

Set second row from map: If the map keys don't exist as columns, the value will be discarded. If a key does not exist, it will create empty cells.)

mymap2 := make(map[string]string)
mymap2["Age"] = "62"
mymap2["Gender"] = "F"
mymap2["ID"] = "6463246"
var wasSet bool
x, wasSet = x.SetRowFromMap(1, mymap2)

Update

Unlike SetRowFromMap, UpdateRowCellsFromMap does not erase the cells value just because the column names are not keys in the map. It updates the cells that have the column name in the map and maintains the value of all the others.

To update the age in row 1:

mymap3 := make(map[string]string)
mymap3["Age"] = "63"
var wasUpdated bool
x, wasUpdated = x.UpdateRowCellsFromMap(1, mymap3)

Delete

Delete second row: (If the row number is invalid, wasDeleted is false)

var wasDeleted bool
x, wasDeleted = x.DeleteRow(1)

Data rows

Get the number of data rows, excluding the header row (unlike GetNumberRows, which includes it):

numberOfDataRows := x.GetNumberDataRows()

Get all the data rows as copies (the header row is not included; changes to the returned rows don't affect the csv):

dataRows := x.GetDataRows()

Iterate over the data rows without touching the header: EachDataRow calls fn once per data row, in csv order, with the row index (starting at 1), a copy of the row, and a map view of it (header name to cell value). Mutating the row or the map inside fn does not affect the csv. fn may return false to stop the iteration early:

x.EachDataRow(func(rowIndex int, row []string, asMap map[string]string) bool {
    if asMap["status"] == "stop" {
        return false // stop the iteration
    }
    fmt.Printf("row %d: %v\n", rowIndex, row)
    return true
})

Slice

Head returns the header row plus the first n data rows (or all of them if there are fewer). n negative or zero means no data rows:

sample := x.Head(10)

Tail returns the header row plus the last n data rows:

recent := x.Tail(10)

SliceRows returns the header row plus the data rows whose csv row index is in [start, end) (half-open, data rows start at index 1). end beyond the last row is clamped. wasSliced is false if the csv is empty, start < 1 or end < start:

var wasSliced bool
x, wasSliced = x.SliceRows(1, 3) // data rows 1 and 2

Append

AppendRows appends the data rows of another csv with the same headers (same names, same order). wasAppended is false if either csv is empty or the headers differ:

var wasAppended bool
x, wasAppended = x.AppendRows(anotherCsv)

Concat

Concat stacks several csvs vertically in one call: the first non-empty csv supplies the headers and every csv with a header row must have the same headers (same names, same order); empty csvs are skipped. If there is no non-empty csv or the headers differ, it returns nil and false:

combined, ok := simplecsv.Concat(day1, day2, day3)

Concat by name

Monthly or vendor exports often add or reorder columns, and strict Concat rejects them. ConcatByName stacks csvs vertically aligning the columns by header name instead of by position: the result has the header row of the first non-empty csv, followed by the header names that only appear in later csvs (in order of first appearance). Each data row is placed under its header name, with empty strings where the csv has no column with that name:

january, _ := simplecsv.ReadCsvFile("january.csv") // columns: id, name
february, _ := simplecsv.ReadCsvFile("february.csv") // columns: name, bonus
combined, ok := simplecsv.ConcatByName(january, february)

ok is false if there is no non-empty csv, or a csv has duplicate header names (the result would be ambiguous). When every csv has the same headers in the same order, ConcatByName is equivalent to Concat.

Deduplicate

Unique removes exact duplicate data rows: for each full row (all columns) only the first occurrence is kept, in csv order. The header row is kept:

unique := x.Unique()

UniqueByFields removes data rows that share the same key built from the named columns; the first row per key is kept. wasUnique is false if the csv is empty, no fields are given, or a field name does not exist:

var wasUnique bool
onePerEmail, wasUnique = x.UniqueByFields("email")
onePerCountryCity, wasUnique = x.UniqueByFields("country", "city")

Distinct and counts

Distinct returns the distinct values of a column (data cells only, the header cell is never included), in order of first appearance. The returned slice is a copy:

countries, wasRead := x.Distinct("Country")

wasRead is false if the csv is empty or the field name does not exist. A csv with only the header row returns an empty slice and true.

ValueCounts returns a new csv with the fixed headers value and count: one row per distinct value of the column, in order of first appearance, with the number of data rows that have that value, as a decimal string:

counts, wasRead := x.ValueCounts("Country")

The original csv is not modified. wasRead is false if the csv is empty or the field name does not exist; in that case a copy of the csv is returned.

Group by

GroupBy collapses the data rows into one output row per group, where a group is all the data rows whose key fields have the same values. The output header row is the key field names followed by the As name of each aggregation; each output data row is the key values followed by one cell per aggregation. Groups appear in order of first occurrence:

var summary simplecsv.SimpleCsv
var wasGrouped bool
summary, wasGrouped = sales.GroupBy(
	[]string{"country"},
	[]simplecsv.Agg{{Op: simplecsv.AggCount, As: "orders"}, {Field: "amount", Op: simplecsv.AggSum, As: "total"}},
)

An aggregation is Field (the source column, ignored for AggCount), Op (the operation) and As (the output header, which must be unique among the key names and the other As names). The operations are:

• AggCount — counts the data rows of the group. Field may be empty.
• AggSum — adds the Field cells parsed with strconv.ParseFloat; cells that don't parse as numbers count as 0.
• AggMin / AggMax — the smallest / largest finite numeric Field cell; a group without finite numeric cells produces an empty cell.
• AggFirst / AggLast — the Field value of the first / last data row of the group.
• AggJoin — the non-empty Field values of the group joined with ,.

Numeric results are formatted with strconv.FormatFloat(f, 'f', -1, 64), so integers have no decimal noise.

wasGrouped is false if the csv is empty, no keys or no aggregations are given, a key or aggregation field does not exist, an As name is empty or duplicates another key or As name, or an aggregation operation is not one of the AggOp constants; in that case a copy of the csv is returned.

Column

Add

To add a column at the end of the CSV:

var wasSuccessful bool
x, wasSuccessful = x.AddEmptyColumn("NewColumn")

Remove

To remove a column at position 1 (second column, because it's zero based):

var wasRemoved bool
x, wasRemoved = x.RemoveColumn(1)

To remove a column by name:

var wasRemoved bool
x, wasRemoved = x.RemoveColumnByName("Gender")

Get

Get a copy of all the data cells of a column, skipping the header cell. The returned slice is a copy: changes to it don't affect the csv. columnExists is false if the csv is empty or the column position is not valid:

columnCells, columnExists := x.GetColumn(1)

The same by column name:

columnCells, fieldExists := x.GetColumnByField("Age")

fieldExists is false if the field does not exist.

Set

Replace the data cells of a column with a slice. The slice must have the same length as the number of data rows (the csv length minus the header row), because every data row must keep exactly one cell in the column. The header cell is not modified:

var wasSet bool
x, wasSet = x.SetColumn(1, []string{"24", "62", "45"})

The same by column name:

var wasSet bool
x, wasSet = x.SetColumnByField("Age", []string{"24", "62", "45"})

Map

MapColumnByField transforms the data cells of a column with a function. The function receives the cell value and the csv row index (1 for the first data row); the header cell is not transformed. The original csv is not modified:

var wasMapped bool
x, wasMapped = x.MapColumnByField("Name", func(value string, row int) string {
	return strings.ToUpper(value)
})

Fill

FillColumnByField sets every data cell of a column to the same value. Useful for constants and defaults:

var wasFilled bool
x, wasFilled = x.FillColumnByField("Source", "import")

Column shape

Add at position

AddEmptyColumnAt inserts an empty column at a position: index is the position of the new column and must be between 0 and the number of columns (inclusive; inserting at the number of columns appends, like AddEmptyColumn). The header cell of the new column is columnName and every data cell is empty:

var wasAdded bool
x, wasAdded = x.AddEmptyColumnAt("Source", 0)
x, wasAdded = x.AddEmptyColumnAt("Notes", 3)

wasAdded is false if the csv is empty, the column name already exists or index is out of range.

Add with values

AddColumnByField appends a column with the header name columnName and the given data cells. The values slice must have the same length as the number of data rows (the csv length minus the header row), because every data row must keep exactly one cell in the column; a csv with only the header row takes an empty values slice. The values are copied:

var wasAdded bool
x, wasAdded = x.AddColumnByField("Total", []string{"24", "62", "45"})

wasAdded is false if the csv is empty, the column name already exists or the length of the values slice does not match the number of data rows.

Compute from row map

AddComputedColumn appends a column whose data cells are computed by a function from the values of the other columns. The function is called once per data row, in csv order, with a map from header name to cell value: the map is built fresh for every row and is a copy, so the callback can read field names instead of column indexes, and mutating the map does not affect the csv. The string the function returns becomes the cell of the new column:

var wasComputed bool
x, wasComputed = x.AddComputedColumn("full_name", func(row map[string]string) string {
	return row["first"] + " " + row["last"]
})

wasComputed is false if the csv is empty or the column name already exists; in that case a copy of the csv is returned. A csv with only the header row takes the new header cell only and the function is not called.

Rename batch

RenameHeaders renames several headers in one call. Every key of the map must be an existing header, and the result must not contain duplicate header names: a rename fails if a key does not exist, two keys map to the same new name, or a renamed column collides with a header that is not renamed. The renames are applied simultaneously, so a swap ("a" → "b", "b" → "a") is applied correctly and map iteration order does not matter:

var wasRenamed bool
x, wasRenamed = x.RenameHeaders(map[string]string{"ID": "IdNumber", "Date": "Fecha"})

wasRenamed is false if the csv is empty or the renamed headers would contain duplicates. Renaming a header to its own name is a no-op for that column, and an empty map is a no-op that succeeds.

Move

MoveColumn moves the column with the header name columnName to position newIndex (0 to the number of columns minus 1), shifting the columns in between: the header and data cells of the moved column move together:

var wasMoved bool
x, wasMoved = x.MoveColumn("Age", 0)

wasMoved is false if the csv is empty, the column name does not exist or newIndex is out of range. Moving a column to its own position is a no-op and succeeds.

Split a field

SplitField replaces the column with the header name name by len(newNames) new columns in its position. Every data cell is split on sep with strings.Split, and the parts fill the new columns in order: if a cell has fewer parts than newNames, the remaining new columns get empty cells; if it has more parts, the extra parts are dropped. The original column is removed, so a new name may reuse it but must not collide with any other header:

var wasSplit bool
x, wasSplit = x.SplitField("coord", ",", []string{"lat", "lon"})
x, wasSplit = x.SplitField("last;first", ";", []string{"last", "first"})

wasSplit is false if the csv is empty, the field name does not exist, newNames is empty or contains duplicates, or a new name collides with another header.

Combine fields

CombineFields appends a column with the header name as whose data cells join the values of the named columns with sep (via strings.Join). The source columns stay in place:

var wasCombined bool
x, wasCombined = x.CombineFields([]string{"first", "last"}, " ", "full_name")
x, wasCombined = x.CombineFields([]string{"country", "sku"}, "-", "country_sku")

wasCombined is false if the csv is empty, names is empty, a name does not exist, names repeats a field, or as already exists as a header.

Cells

Get

Get value of the cell in the second column, second row:

cellValue, cellExists := x.GetCell(1, 1)

Get the value of the cell in the column Age, second row:

cellValue, cellExists := x.GetCellByField("Age", 1)

Set

Changes the value of the cell in the first column (0) and the second row (1) to "27":

var wasChanged bool 
x, wasChanged = x.SetCell(0, 1, "27")

The same, using the column name instead of the column position:

var wasChanged bool 
x, wasChanged = x.SetCellByField("Age", 1, "27")

Replace

ReplaceInField replaces the data cells of a column whose value is exactly equal to old (case-sensitive, whole cell) with new. The header cell is not modified. Useful to fix known bad values (N/A → empty) or rename codes:

var wasReplaced bool
x, wasReplaced = x.ReplaceInField("Status", "N/A", "")

Trim

TrimSpace removes the surrounding whitespace of every cell, including the header cells, with strings.TrimSpace:

var wasTrimmed bool
x, wasTrimmed = x.TrimSpace()

wasTrimmed is false if trimming makes two header names equal: header names must stay unique.

Find

Find in column

Find the word "27" in the first column (column 0):

rowsWithWord, validColumn := x.FindInColumn(0, "27")

It returns a slice of rownumbers (int) where you can find the word in the column position. Please note that in simplecsv all cells are strings.

If it doesn't find the value, it returns an empty slice.

In FindInColumn and in FindInField case does not matter. Foo = foo = FOO.

Find in field

The same as FindInColumn but using a column/field name instead of position. Please note that FindInField, unlike FindInColumn never includes the header in the search result.

rowsWithWord, validFieldName := x.FindInField("Age", "27")

If the field name does not exist, the second value returned (validFieldName) is false.

Match in column

Find where results match a regular expression in the third column (column 2):

rowsWithWord, areParamsOk := x.MatchInColumn(2, "p([a-z]+)ch$")

Use ^ and $ in the regular expression to match exact results.

Matching is case-insensitive by default: Foo = foo = FOO. Use MatchInColumnCaseSensitive when case matters.

Match in field

Same as with MatchInColumn, but with a field (column name). Find where results match a regular expression in the third column (column "ID"):

rowsWithWord, areParamsOk := x.MatchInField("ID", "p([a-z]+)ch$")

Use ^ and $ in the regular expression to match exact results.

Please note that MatchInField, unlike MatchInColumn never includes the header in the search result.

Matching is case-insensitive by default: Foo = foo = FOO. Use MatchInFieldCaseSensitive when case matters.

Case sensitive find

FindInColumn, FindInField, MatchInColumn and MatchInField ignore case by default. Use the *CaseSensitive variants when case matters:

rowsWithWord, validFieldName := x.FindInFieldCaseSensitive("Name", "Ana")

rowsWithWord, areParamsOk := x.MatchInFieldCaseSensitive("ID", "p([a-z]+)ch$")

Sort

Sort by field

Sorts the csv by a column name and returns a new sorted csv. The header row stays at the top and the original csv is not modified. Numbers are sorted numerically ("7" comes before "30"), other values as strings. The sort is stable.

sortedCsv, fieldExists := x.SortByField("Age", true) // true: ascending, false: descending

Sort by column

The same as SortByField but using a column position. It sorts all the rows, including the first one:

sortedCsv, validColumn := x.SortByColumn(0, true)

Sort by multiple fields

Sorts the csv by several columns, one level per field, like ORDER BY in SQL: a later field is only compared when every earlier field compares equal. The header row stays at the top and the original csv is not modified. Each field uses the same ordering as SortByField (numbers numerically, other values as strings), and the sort is stable. An empty ascending sorts every field ascending; otherwise it has one bool per field (true ascending, false descending):

var sortedCsv simplecsv.SimpleCsv
var wereFieldsFound bool
sortedCsv, wereFieldsFound = x.SortByFields([]string{"Country", "City"}, nil)
sortedCsv, wereFieldsFound = x.SortByFields([]string{"Country", "City"}, []bool{true, false})

wereFieldsFound is false if the csv is empty, no fields are given, a field name does not exist, or the length of ascending is neither 0 nor the number of fields; in that case a copy of the csv is returned.

Sort an index

SortIndex returns a sorted copy of an index:

sortedIndex := simplecsv.SortIndex([]int{5, 1, 3})

Filter rows

FilterRows returns a new csv with the rows where the predicate function returns true. If header is true, the first row is kept as the header:

adults := x.FilterRows(func(row []string) bool {
	age, _ := strconv.Atoi(row[1])
	return age >= 18
}, true)

Field filters

FilterByField keeps the data rows where the predicate returns true for the named column. The header cell is never passed to the predicate, and the field name is used instead of a column index:

var wasFiltered bool
adults, wasFiltered = x.FilterByField("age", func(value string) bool {
	n, _ := strconv.Atoi(value)
	return n >= 18
})

Where keeps the data rows where the named column is exactly equal to value (case-sensitive). wasWhere is false if the csv is empty or the field name does not exist:

var wasWhere bool
active, wasWhere = x.Where("status", "active")

WhereFold is the same but case-insensitive: Active, active and ACTIVE all match:

var wasWhereFold bool
active, wasWhereFold = x.WhereFold("status", "active")

Join

Inner join

JoinByField joins two csvs by a common field and returns a new csv with the rows where the field has the same value in both csvs. The result has all the columns of the first csv, followed by the columns of the second csv except the join column. If a join value shows up more than once, all combinations of rows are in the result. Values are compared exactly: case matters. The source csvs are not modified.

people, _ := simplecsv.ReadCsvFile("people.csv") // columns: ID, Name
ages, _ := simplecsv.ReadCsvFile("ages.csv")     // columns: ID, Age
joined, fieldExistsInBoth := people.JoinByField(ages, "ID")

fieldExistsInBoth is false if the field does not exist in one of the csvs.

Left join

LeftJoinByField is like JoinByField, but the rows of the first csv without a match in the second one are also in the result, with empty cells in the columns of the second csv:

joined, fieldExistsInBoth := people.LeftJoinByField(ages, "ID")

Join on different key names

Real files rarely share the same key name (id vs customer_id). Join and LeftJoin join by one field name per csv, so no rename is needed: the result is the same as joining on a common name. The join column of the second csv is dropped from the result; the join column of the first csv is kept.

orders, _ := simplecsv.ReadCsvFile("orders.csv") // columns: id, amount
joined, bothFieldsExist := people.Join(orders, "id", "customer_id")

bothFieldsExist is false if one of the fields does not exist in its csv.

LeftJoin is like Join, but the rows of the first csv without a match in the second one are also in the result, with empty cells in the columns of the second csv:

joined, bothFieldsExist := people.LeftJoin(orders, "id", "customer_id")

Right join

RightJoin is like Join, but every row of the second csv is in the result: rows without a match in the first csv are included with empty cells in the columns of the first csv, and rows of the first csv without a match are dropped. The rows of the second csv that had no match are appended at the end. Use it when the second file is the driver (e.g. a master product list):

joined, bothFieldsExist := people.RightJoin(orders, "id", "customer_id")

Full join

FullJoin is like Join, but every row of both csvs is in the result: rows without a match are included with empty cells in the columns of the other side. Use it to find unmatched keys on either side without two passes:

joined, bothFieldsExist := people.FullJoin(orders, "id", "customer_id")

Same name join

RightJoinByField and FullJoinByField are the same-name variants of RightJoin and FullJoin, like JoinByField:

joined, fieldExistsInBoth := people.RightJoinByField(ages, "ID")
joined, fieldExistsInBoth := people.FullJoinByField(ages, "ID")

Join on multiple columns

Composite keys (country + sku, date + store) are common. JoinOn, LeftJoinOn, RightJoinOn and FullJoinOn join on several columns at once: the i-th name of the first list must be a column of the first csv, the i-th name of the second list a column of the second csv, and the lists must have the same length (at least one name). A row of the first csv is joined to a row of the second when the values of all its left key columns are exactly equal to the values of the corresponding right key columns (case matters). The join key is built with a length-prefixed encoding, so values containing separators cannot collide. All the right key columns are dropped from the result; the left key columns are kept.

orders, _ := simplecsv.ReadCsvFile("orders.csv") // columns: country, sku, amount
prices, _ := simplecsv.ReadCsvFile("prices.csv") // columns: country, sku, price
joined, keysExist := orders.JoinOn(prices, []string{"country", "sku"}, []string{"country", "sku"})

keysExist is false if a csv is empty, the lists have different lengths or are empty, one of the fields does not exist in its csv, or the result headers would collide.

LeftJoinOn keeps the rows of the first csv without a match; RightJoinOn keeps every row of the second csv, appending the unmatched ones at the end with empty cells in the columns of the first csv; FullJoinOn keeps every row of both csvs. When both field lists have a single name, the *On functions are equivalent to their Join / LeftJoin / RightJoin / FullJoin counterparts.

Merge columns

Sometimes two csvs are already row-aligned (same order, same length) and only need their columns glued side by side; a join would be wrong or wasteful. MergeColumns places the columns of the second csv next to the columns of the first, row by row: the result has the header row of the first csv followed by the header row of the second, and each data row is the data row of the first csv followed by the data row of the second in the same position. Both csvs must have the same number of rows (header included) — padding is not allowed — and no header name of the second csv may collide with a header name of the first:

customers, _ := simplecsv.ReadCsvFile("customers.csv") // columns: id, name
emails, _ := simplecsv.ReadCsvFile("emails.csv")       // columns: email, verified
merged, wasMerged := customers.MergeColumns(emails)

wasMerged is false if a csv is empty, the row counts differ, or the result headers would collide.

Diff

"What changed between yesterday's export and today's?" DiffByKey compares the data rows of two csvs keyed by a column. added are the data rows of the second csv whose key is not in the first, removed are the data rows of the first csv whose key is not in the second, and changed are the data rows of the first csv whose key exists in the second but whose full row differs. changed keeps the first csv's headers and values; join on the key if you need the second csv's values:

yesterday, _ := simplecsv.ReadCsvFile("yesterday.csv")
today, _ := simplecsv.ReadCsvFile("today.csv")
added, removed, changed, ok := yesterday.DiffByKey(today, "id")

Each result keeps the rows in order of appearance in its source csv and is an independent csv, so it can be written out or inspected on its own. The key values must be unique within each csv: a key repeated in either csv makes the diff ambiguous and ok is false. ok is also false if either csv is empty or the key is not a header of either csv. A csv with only the header row is a valid input: all the other side's data rows are then added or removed.

Error variants

A boolean ok tells you that something failed but not why: "did the join fail because a field is missing or because the result headers would collide?" The E variants of the functions where the reason matters most return an error instead, with a message prefixed with simplecsv: that describes the problem. The bool-returning functions are unchanged, and the E variants return the same csv the bool variants return on failure (an independent copy of the receiver for methods; nil for ConcatE / ConcatByNameE):

joined, err := people.JoinE(orders, "id", "customer_id")
if err != nil {
	log.Fatal(err) // e.g. simplecsv: cannot join: key field "customer_id" not found in the right csv
}

LeftJoinE is the error variant of LeftJoin. The join errors say which csv is empty, which key field is missing in which csv, or which right columns would collide with left columns.

GroupByE is the error variant of GroupBy: it says whether the csv is empty, no keys or aggregations are given, a key appears more than once, a key or aggregation field does not exist, an As name is empty or duplicates another key or As name, or an aggregation operation is invalid:

summary, err := sales.GroupByE(
	[]string{"country"},
	[]simplecsv.Agg{{Field: "amount", Op: simplecsv.AggSum, As: "total"}},
)
if err != nil {
	log.Fatal(err)
}

ConcatE and ConcatByNameE are the error variants of Concat and ConcatByName: they say whether no csv has a header row, a csv has different headers (ConcatE), or a csv has duplicate header names (ConcatByNameE):

combined, err := simplecsv.ConcatE(day1, day2, day3)
if err != nil {
	log.Fatal(err)
}

Boolean functions

Use boolean functions AND, OR and NOT to combine indexes and produce other complex indexes that point to rows in the csv. Very useful to produce search results.

Indexes are slices of row numbers (integers between 0 or 1 and the length of the csv -1). The indexes returned by OrIndex, AndIndex and NotIndex are sorted in ascending order.

OR indexes

var w []int
w = simplecsv.OrIndex(a,b)
w = simplecsv.OrIndex(a,b,c,d,e)

OrIndex accepts any number of operands. With no operands it returns an empty slice; with one operand it returns a sorted, de-duplicated copy. With 2 or more operands it returns their union.

AND indexes

var p []int
p = simplecsv.AndIndex(a,b)
p = simplecsv.AndIndex(a,b,c,d,e)

AndIndex accepts any number of operands. With no operands it returns an empty slice; with one operand it returns a sorted, de-duplicated copy. With 2 or more operands it returns their intersection.

NOT an index

The code below returns the negative of the index g, between row 1 and row 4. If g is an index with the values {1, 2} the negative of g is {3, 4}. Because 3 and 4 are the integers between 1 and 4 that are not in g.

var g []int
min := 1
max := 4
p = simplecsv.NotIndex(g, min, max)

Note: For csvs with headers the min value is usually 1 and for csvs without headers the min value is usually 0.

Only

Only are 2 functions to simplify and sort a csv.

Only this rows

It removes rows that are not in the index and reorders a CSV by the index order. If header is true, it starts by the csv header. Note: if header is true and the index contains 0, the header row appears twice in the result (once as the header, once as row 0).

newIndex := []int{1,3}
header := true
x, _ = x.OnlyThisRows(newIndex, header)

Only this fields

Removes fields that are not in the list of fields, reorders the CSV by the list of fields and adds fields that do not exist as blank fields.

fieldsList := []string{"Age","ID"}
x, _ = x.OnlyThisFields(fieldsList)