Skip to main content

Csv

Library of functions for CSV data.

Functions​

InferAndParse​

Reads a CSV using schema detection (inference).

Syntax:
Csv.InferAndParse(stringData: string, sampleSize: optional int, encoding: optional string, hasHeader: optional bool, delimiters: optional list, nulls: optional list, nans: optional list, skip: optional int, escape: optional string, quotes: optional string, preferNulls: optional bool)
Parameters​

  • stringData: The data in string format to infer and parsed.

  • sampleSize: Specifies the number of rows to sample within the data.

note

If a large sampleSize is used, the detection takes more time to complete, but has a higher chance of detecting the correct format. To force the detection to read the full data, set sampleSize to -1.

  • encoding: Specifies the encoding of the data.
note

If the encoding is not specified it is determined automatically.

  • hasHeader: Specifies whether the data has a header or not, e.g. true or false.
note

If not specified, the inference tries to detect the presence of a header.

  • delimiters: Specifies a candidate list of delimiters, e.g. [",", "|"].
note

If not specified, the inference tries to detect the delimiter.

  • nulls: Specifies a candidate list of strings to interpret as null, e.g. ["NA"].
note

If not specified, the inference does not detect nulls.

  • nans: Specifies a candidate list of strings to interpret as NaN, e.g. ["nan"].
note

If not specified, the inference does not detect NaNs.

  • skip: Number of rows to skip from the beginning of the data. Defaults to 0.

  • escape: The escape character to use when parsing the CSV data, e.g. "\\".

  • quotes: Specifies a candidate list of quote characters to interpret as quotes, e.g. ["\""].

note

If not specified, the inference tries to detect the quote char. f the set to null then no quote character is used.

  • preferNulls: If set to true and during inference the system does read the whole data, marks all fields as nullable. Defaults to true.
Returns​
  • collection: A collection with the data parsed from the CSV string.
Example:
Csv.InferAndParse(\"\"\"value1;value2\"\"\")

InferAndRead​

Reads a CSV using schema detection (inference).

Syntax:
Csv.InferAndRead(location: location or string, sampleSize: optional int, encoding: optional string, hasHeader: optional bool, delimiters: optional list, nulls: optional list, nans: optional list, skip: optional int, escape: optional string, quotes: optional string, preferNulls: optional bool)
Parameters​

  • location: The location or url of the data to read.

  • sampleSize: Specifies the number of rows to sample within the data.

note

If a large sampleSize is used, the detection takes more time to complete, but has a higher chance of detecting the correct format. To force the detection to read the full data, set sampleSize to -1.

  • encoding: Specifies the encoding of the data.
note

If the encoding is not specified it is determined automatically.

  • hasHeader: Specifies whether the data has a header or not, e.g. true or false.
note

If not specified, the inference tries to detect the presence of a header.

  • delimiters: Specifies a candidate list of delimiters, e.g. [",", "|"].
note

If not specified, the inference tries to detect the delimiter.

  • nulls: Specifies a candidate list of strings to interpret as null, e.g. ["NA"].
note

If not specified, the inference does not detect nulls.

  • nans: Specifies a candidate list of strings to interpret as NaN, e.g. ["nan"].
note

If not specified, the inference does not detect NaNs.

  • skip: Number of rows to skip from the beginning of the data. Defaults to 0.

  • escape: The escape character to use when parsing the CSV data, e.g. "\\".

  • quotes: Specifies a candidate list of quote characters to interpret as quotes, e.g. ["\""].

note

If not specified, the inference tries to detect the quote char. f the set to null then no quote character is used.

  • preferNulls: If set to true and during inference the system does read the whole data, marks all fields as nullable. Defaults to true.
Returns​
  • collection: A collection with the data read from the CSV file.
Example:
Csv.InferAndRead("http://server/file.csv")

Parse​

Parses a CSV from a string.

Syntax:
Csv.Parse(string: string, type: type, delimiter: optional string, nulls: optional list, nans: optional list, skip: optional int, escape: optional string, quote: optional string, timeFormat: optional string, dateFormat: optional string, timestampFormat: optional string)
Parameters​

  • string: The string containing the CSV to parse.

  • type: The type of the data in the CSV.

  • delimiter: Specifies the delimiter to use. Defaults to ",".

  • nulls: Specifies a candidate list of strings to interpret as null, e.g. ["NA"]. Defaults to [""].

  • nans: Specifies a candidate list of strings to interpret as NaN, e.g. ["nan"]. Defaults to [].

  • skip: Number of rows to skip from the beginning of the data. Defaults to 0.

  • escape: "Specifies a escape character while parsing the CSV data. Defaults to "\\".

  • quote: Specifies the quote character, e.g. "\"". Defaults to double quote.

  • timeFormat: Specifies the format to parse time fields. Defaults to "HH:mm[:ss[.SSS]]".

  • dateFormat: Specifies the format to parse date fields. Defaults to "yyyy-M-d".

  • timestampFormat: Specifies the format to parse timestamp fields. Defaults to "yyyy-M-d['T'][ ]HH:mm[:ss[.SSS]]".

Returns​
  • collection: A collection with the data parsed from the CSV string.
Example:

let
  personType = type record(name: string, age: int, salary: double),
  data = """name, age, salary
                 john, 34, 14.6
                 jane, 32, 15.8
                 Bob, 25, 12.9 """
in
  // skip = 1 to skip the header
  Csv.Parse(data, personType, skip = 1)

Read​

Reads a CSV.

Syntax:
Csv.Read(location: location or string, type: type, encoding: optional string, delimiter: optional string, nulls: optional list, nans: optional list, skip: optional int, escape: optional string, quote: optional string, timeFormat: optional string, dateFormat: optional string, timestampFormat: optional string)
Parameters​

  • location: The location or url of the data to read.

  • type: The type of the data in the CSV.

  • encoding: Specifies the encoding of the data. Defaults to "utf-8".

  • delimiter: Specifies the delimiter to use. Defaults to ",".

  • nulls: Specifies a candidate list of strings to interpret as null, e.g. ["NA"]. Defaults to [""].

  • nans: Specifies a candidate list of strings to interpret as NaN, e.g. ["nan"]. Defaults to [].

  • skip: Number of rows to skip from the beginning of the data. Defaults to 0.

  • escape: "Specifies a escape character while parsing the CSV data. Defaults to "\\".

  • quote: Specifies the quote character, e.g. "\"". Defaults to double quote.

  • timeFormat: Specifies the format to parse time fields. Defaults to "HH:mm[:ss[.SSS]]".

  • dateFormat: Specifies the format to parse date fields. Defaults to "yyyy-M-d".

  • timestampFormat: Specifies the format to parse timestamp fields. Defaults to "yyyy-M-d['T'][ ]HH:mm[:ss[.SSS]]".

Returns​
  • collection: A collection with the data read from the CSV file.
Example:
let
  personType = type record(name: string, age: int, salary: double)
in
  Csv.Read("http://server/file.csv", personType)