Changes

This [[Module Library|XQuery Module]] contains a single function to parse CSV input. [httphttps://en.wikipedia.org/wiki/Comma-separated_values CSV] (comma-separated values) is a popular representation for tabular data, exported e. g. from Excel.

All functions and errors in this module are assigned to the {{Code|<code><nowiki>http://basex.org/modules/csv}} </nowiki></code> namespace, which is statically bound to the {{Code|csv}} prefix.<br/>All errors are assigned to the {{Code|http://basex.org/errors}} namespace, which is statically bound to the {{Code|bxerr}} prefix.

CSV is converted to ===XML as follows:Direct, Attributes===

# The resulting XML document has a {{Code|<csv>}} root element.# Rows are represented via {{Code|<record>}} elements.# Fields are represented via {{Code|<entry>}} elements. The value of a field is represented as text node.# If the {{Code|headerdirect}} option is set to or {{Code|trueattributes}}, the first text line format is parsed as table headerchosen, and the {{Code|<entry>}} elements are replaced with the field names:## Empty names are represented by a single underscore ({{Code|_}}), and characters that are not valid in element names are replaced with underscores.## If the {{Code|lax}} option CSV string is set to {{Code|false}}, invalid characters will be rewritten to an underscore and the character’s four-digit Unicode, and underscores will be represented as two underscores ({{Code|__}}). The resulting element names may be less readable, but can always be converted back to the original field names.# If {{Code|format}} is set to {{Code|attributes}}, field names will be stored in name attributes.XML:

In * The resulting XML document has a {{Code|csv}} root element.* Rows are represented via {{Code|record}} elements.* Fields are represented via {{Code|entry}} elements. The value of a field is represented as text node.* If the {{Code|header}} option is set to {{Code|true}}, the first text line is parsed as table header:** If {{Code|format}} is set to {{Code|direct}}, the field names are encoded, as described in the [[Conversion Module#Keys|Conversion Module]], and used as element names.** Otherwise, if {{Code|format}} is {{Code|attributes}}, the field names will be stored in name attributes. '''A little advice''': in the Database Creation dialog of the GUI, when the if you select CSV parser is selected, Parsing and switch to the ''Parsing'' tab demonstrates , you can see the effects of some of the conversion of options. ===XQuery=== With the {{Code|xquery}} format, CSV records are converted to XML a sequence of arrays: * The resulting value will be a map with a {{Code|records}} and an optional {{Code|names}} key.* Records are organized as a sequence of arrays. A single array contains the entries of a single record.* The column names will be available if {{Code|header}} option is set to {{Code|true}}. The CSV map can e.g. be accessed as follows: * <code>$csv?records[5]</code> returns all entries of the effects 5th record (row)* <code>$csv?records(2)</code> returns all entries of the single 2nd field (column)* <code>$csv?names?*</code> returns the names of all fields (if available)* Return enumerated strings for all records:<syntaxhighlight lang="xquery">for $record at $pos in $csv?recordsreturn $pos || ". " || string-join($record?*, ', ')</syntaxhighlight> The resulting representation consumes less memory than XML-based formats, and values can be directly accessed without conversion options. Thus, it is recommendable for very large inputs and for efficient ad-hoc processing.

The In the following table, all available options are available:listed. The Excel column indicates what are the preferred options for data that is to be imported, or has been exported from Excel.

| Defines the character which separates the entries values of a single record in a single line.

| Specifies the format of the XML data. The format is only relevant if the {{Code|header}} option is activated:<br/>

* With {{Code|xquery}} conversion, the input is converted to an XQuery map| {{Code|direct}}, {{Code|attributes}}, {{Code|xquery}}

{| width='100%'|-| width='120' | '''Signatures'''|{{VersionFunc|7.8}}csv:parse|$string as xs: the return type has been changed from {{Codestring?|elementitem(<csv>)?}} to <br/>{{CodeFunc|csv:parse|document-node$string as xs:string?, $options as map(element*)?|item(<csv>))?}}, and |-| '''Summary'''|Converts the CSV {{Code|format$string}} and to an XQuery value. The {{Code|lax$options}} options have been addedargument can be used to control the way the input is converted.|-| '''Errors'''|{{Error|parse|#Errors}} the specified input cannot be parsed as CSV document.|} ==csv:doc==

|{{Func|csv:parsedoc|$input uri as xs:string?|document-node(elementitem(csv))?}}<br/>{{Func|csv:parsedoc|$input uri as xs:string?, $options as itemmap(*)?|document-nodeitem(element(csv))?}}<br />

|Converts Fetches the CSV data specified document referred to by the given {{Code|$inputuri}} and converts it to XML, and returns the result as {{Code|<csv/>}} an XQuery value. The {{Code|$options}} argument can be used to control the way the input is converted.

|{{Error|BXCS0001parse|#Errors}} the specified input cannot be parsedas CSV document.<br/>{{Error|options|#Errors}} the specified options are conflicting.

|{{Func|csv:serialize|$input as nodeitem()?|xs:string}}<br/>{{Func|csv:serialize|$input as item()?, $options as itemmap(*)?|xs:string}}

|Serializes the node specified by {{Code|$input}} as CSV data, and returns using the result as specified {{Code|xs:string$options}}, and returns the result as string.<br />XML documents Values can also be serialized as CSV if with the standard [[Serialization|Serialization]] feature of XQuery:* The parameter {{Code|method}} is needs to be set to {{Code|csv}}.<br/>With , and* the options presented in this article need to be assigned to the {{Code|$optionscsv}} argument, the way the node is serialized can be controlledparameter.

|{{Error|BXCS0002serialize|#Errors}} the input cannot be serialized.

<pre classsyntaxhighlight lang="brush:xml">

</presyntaxhighlight>

<pre classsyntaxhighlight lang="brush:xquery">

return csv:parse($text, map { 'header': 'true' () })</presyntaxhighlight>

<pre classsyntaxhighlight lang="brush:xml">

</presyntaxhighlight>

<pre classsyntaxhighlight lang="brush:xquery">let $text options := map { 'lax': false() }let $input := file:read-text('some-data.csv')let $output := $input => csv:parse($options) => csv:serialize($options)return $input eq $output</syntaxhighlight> '''Example 3:''' Converts CSV data to XQuery and returns distinct column values: '''Query:'''<syntaxhighlight lang="xquery">let $text := ``[Name,CityJack,ChicagoJack,WashingtonJohn,New York]``let $options := map { 'laxformat': 'noxquery', 'header' : true() }let $xml csv := csv:parse($text, $options)return ( 'Distinct values:', let $csv records := $csv('records') for $name at $pos in $csv('names')?* let $values :serialize= $records?($xml, $optionspos) return ( '* ' || $text eq name || ': ' || string-join(distinct-values($csvvalues), ', ') ))</syntaxhighlight> '''Result:'''<syntaxhighlight lang="xquery">Distinct values:* Name: Jack, John* City: Chicago, Washington, New York</presyntaxhighlight>

|{{Code|BXCS0001parse}}

|{{Code|BXCS0002serialize}}

* Updated: return type of [[#csv:parse|csv:parse]] changed from now returns a document node instead of an element, or an XQuery map if {{Code|element(<csv>)format}} is set to {{Code|document-node(element(<csv>))map}}.