CSV Functions

This module contains a single function to parse CSV input. CSV (comma-separated values) is a popular format for tabular data, exported e.g. from Excel.

Conventions

All functions and errors in this module are assigned to the http://basex.org/modules/csv namespace, which is statically bound to the csv prefix.

Conversion Formats

XML: Direct, Attributes, W3-XML

If the direct or attributes format is chosen, a CSV string is converted to XML:

• The resulting XML document has a csv root element.
• Rows are represented via record elements.
• Fields are represented via entry elements. The value of a field is represented as text node.
• If the header option is set to true, the first text line is parsed as table header:
  • If format is set to direct, the field names are encoded, as described in the Conversion Functions, and used as element names.
  • Otherwise, if format is attributes, the field names will be stored in name attributes.

If the w3-xml format is chosen, a CSV string is converted to XML according to the rules of function fn:csv-to-xml. More details can be found there. This also applies to the interpretation of the options.

Map: W3

Updated: Revised (old name: xquery): Renamed, more options, header represented as sequence.

With the w3 format, CSV records are converted to a sequence of arrays:

• The resulting value will be a map with a rows entry and an optional columns entry.
• Rows are organized as a sequence of arrays. An array contains the entries of a single record.
• The columns entry contains a sequence with the column names. It is generated if the header option is set to true.

The CSV map can e.g. be accessed as follows:

• $csv?rows[5] returns all entries of the 5th record (row)
• $csv?rows?2 returns all entries of the 2nd field (column)
• $csv?columns returns the names of all fields (if available)
• Return enumerated strings for all rows:

for $row at $pos in $csv?rows
return $pos || ". " || string-join($row?*, ', ')

The resulting representation consumes less memory than XML-based formats, and values can be directly accessed without conversion. Thus, it is recommendable for very large inputs and for efficient ad-hoc processing.

With the w3 format, CSV data is converted to a map, containing a sequence of arrays, according to the rules of function fn:parse-csv. More details can be found there. This also applies to the interpretation of the options.

Arrays: W3-Arrays

With the w3-arrays format, CSV rows are converted to a sequence of arrays, according to the rules of function fn:csv-to-arrays. More details can be found there. This also applies to the interpretation of the options.

Options

In the following table, all available options are listed. The Excel column lists recommended options for data that is processed with Excel or Open/Libre Office.

Functions

csv:doc

csv:doc(
  $source   as xs:string?,
  $options  as map(*)?     := {}
) as item()?

csv:parse

csv:parse(
  $value    as xs:string?,
  $options  as map(*)?     := {}
) as item()?

csv:serialize

csv:serialize(
  $input    as item()?,
  $options  as map(*)?  := {}
) as xs:string

Examples

Example 1: Converts CSV data to XML, interpreting the first row as table header:

Name,First Name,Address,City
Huber,Sepp,Hauptstraße 13,93547 Hintertupfing

let $text := file:read-text('addressbook.csv')
return csv:parse($text, { 'header': true() })

<csv>
  <record>
    <Name>Huber</Name>
    <First_Name>Sepp</First_Name>
    <Address>Hauptstraße 13</Address>
    <City>93547 Hintertupfing</City>
  </record>
</csv>

Example 2: Convert some CSV data to XML and back, and check if the input and output are equal. The expected result is true:

let $options := { 'lax': false() }
let $input := file:read-text('some-data.csv')
let $output := $input => csv:parse($options) => csv:serialize($options)
return $input eq $output

Example 3: Convert CSV data to XQuery and returns distinct column values:

let $text := ``[Name,City
Jack,Chicago
Jack,Washington
John,New York
]``
let $options := { 'format': 'w3', 'header': true() }
let $csv := csv:parse($text, $options)
return (
  'Distinct values:',
  let $rows := $csv?rows
  for $column at $pos in $csv?columns
  let $values := $rows?$pos
  return (
    '* ' || $column || ': ' || string-join(distinct-values($values), ', ')
  )
)

Distinct values:
* Name: Jack, John
* City: Chicago, Washington, New York

Errors

Changelog

• Added: Options: row-delimiter, quote-character, trim-whitespace, trim-rows, strict-quoting, select-columns options.
• Updated: W3 Conversion Format: Revised (old name: xquery): Renamed, more options, header represented as sequence.
• Updated: Options: separator renamed to field-delimiter.

• Added: Options: skip-empty option.
• Removed: Options: allow option.

• Added: Options: allow option.

• Added: csv:doc

• Updated: csv:parse can be called with empty sequence.

• Added: xquery option
• Updated: error codes updated; errors now use the module namespace
• Removed: map option

• Updated: Options: improved Excel compatibility

• Added: backslashes option

• Added: format and lax options
• Updated: csv:parse now returns a document node instead of an element, or an XQuery map if format is set to map.

• Added: New module added.