csvToTable

Load data from a CSV file and return the table name.

csvToTable(
    path,
    query="",
    delimiter=",",
    encoding="utf-8",
    dateColumns=array()
)

Arguments

path

A string representation of the path to the CSV file.

Both absolute and relative paths are supported. If the path is relative, it's considered relative to the model file (e.g. the inputs workbook). Environment variables (e.g. %temp% or %homepath%) are also supported.

query

An optional query to be applied to the table. See SQL queries for details.

An empty argument or empty string here means that no query will be performed, i.e. all data from the CSV file will be used.

delimiter

The character that separates columns within each line of text.

For CSV (comma-separated values) files, this is a comma, but there are other types of delimiter-separated values files, e.g. using ; instead.

Note that the file extension does not indicate the delimeter, but rather the content of the file (for example *.csv files may contain data separated by |, or ;)

encoding

The character encoding of the text file.

This refers to how characters are represented in storage, i.e. "the ones and zeroes" that make up the text file. Modern text files use UTF-8. Another common encoding is ISO-8859-1, sometimes known as latin-1.

dateColumns

The names of columns whose values should be interpreted as dates.

Since CSV files are text files, their values can only be strings. While numbers are easy to detect in strings (we may readily assume they are written in decimal, using only digits and a decimal separator), dates are trickier, since there are many valid ways to represent a date (e.g. 2 November 2020, 20201102T00:00:00+Z, 2020-11-02, 2020-02-11, etc.), and some strings or numbers may look like dates, while they may represent something else (e.g. the number 20201102, the name Jan). Therefore, we should explicitly state which columns are expected to contain dates.

Examples

Get rows from file.csv where the value in column c is greater than 5. The file uses ; to separate values, and is encoded in ISO-8859-1. Columns a and b are expected to contain dates.

csvToTable(
    "file.csv",
    "where c>5",
    ";",
    "ISO-8859-1",
    array("a","b")
)