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

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.

XQuery

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

• The resulting value will be a map with a records entry and an optional names entry.
• Records are organized as a sequence of arrays. An array contains the entries of a single record.
• The names entry contains an array 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?records[5] returns all entries of the 5th record (row)
• $csv?records?(2) returns all entries of the 2nd field (column)
• $csv?names?* returns the names of all fields (if available)
• Return enumerated strings for all records:

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

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.

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(
  $href     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': 'xquery', 'header': true() }
let $csv := csv:parse($text, $options)
return (
  'Distinct values:',
  let $records := $csv('records')
  for $name at $pos in $csv('names')?*
  let $values := $records?($pos)
  return (
    '* ' || $name || ': ' || string-join(distinct-values($values), ', ')
  )
)

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

Errors

Changelog

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