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.

=Rules=Conversion Formats==

{{Version|7.7.2}}===XML: the conversion rules have been updated and aligned with the JSON parser.Direct, Attributes===

If the {{Code|direct}} or {{Code|attributes}} format is chosen, a CSV string is converted to XML as follows:

# * The resulting XML document has a {{Code|<csv/>}} root elementselement.# * 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, 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|laxformat}} option is set to {{Code|falsedirect}}, invalid characters will be rewritten to an underscore and the character’s four-digit Unicodefield names are encoded, as described in the [[Conversion Module#Keys|Conversion Module]], and underscores will be represented used as two underscores ({{Code|__}}). The resulting element names may be less readable, but can always be converted back to the original field names.# If ** Otherwise, if {{Code|format}} is set to {{Code|attributes}}, the field names will be stored in name attributes.

In '''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}} entry and an optional {{Code|names}} entry.* Records are organized as a sequence of arrays. An array contains the effects entries of a single record.* The {{Code|names}} entry contains an array with the column names. It is generated if the {{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 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:<pre lang='xquery'>for $record at $pos in $csv?recordsreturn $pos || ". " || string-join($record?*, ', ')</pre> 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 lists recommended options for data that is processed with Excel or Open/Libre Office.

! width="140" | ParameterOption! width="50%" | Description

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

| Specifies the format of the XML for converting CSV data([[#Conversion Formats|see above]]). The format is only relevant if the {{Code|header}} option is activated:<br/>* In the {{Code|direct}} conversion format, field names are represented as element names* In the {{Code|attributes}} conversion, field names are stored in {{Code|name}} attributes| {{Code|direct}}, {{Code|attributesxquery}}

| Specifies if a [[Conversion Module#Keys|lax approach is conversion rules]] are used to convert QNames to JSON names.| {{Code|yes}}, {{Code|no}}| {{Code|yes}}| ''parse'', ''serialize''| {{Code|no}}|- valign="top"| {{Code|quotes}}| Specifies how quotes are parsed:* Parsing: If the option is enabled, quotes at the start and end of a value will be treated as control characters. Separators and newlines within the quotes will be adopted without change.* Serialization: If the option is enabled, the value will be wrapped with quotes if it contains characters that might be treated as control characters. A quote character in the value will be encoded according to the rules of the {{Code|backslashes}} option.

|-valign="top"| width='120' | '''SignaturesSignature'''|{{Func|csv:parse|$input as xs:string|document-node(element(csv))}}<br/pre>{{Func|csv:parse|( $input value as xs:string?, $options as itemmap(*)|document-node(element? := map { }) as item(csv))}}?</pre>|-valign="top"

|Converts the CSV data specified by {{Code|$inputvalue}} to XML, and returns the result as {{Code|<csv/>}} an XQuery value.<br/>The {{Code|$options}} argument can be used to control the way the input is converted. Options can either be specified<br />* as children of an {{Code|<csv:options/>}} element; e.g.:<pre class- valign="brush:xmltop"><csv:options> <csv:separator value=';'/> ...</csv:options></pre>* or as map, which contains all key/value pairs:<pre class="brush:xquery">{ 'separator': ';', ... }</pre>|-

|{{Error|BXCS0001parse|#Errors}} the specified input cannot be converted.<br/>{{Error|BXCS0003|#Errors}} the specified separator must be a single characterparsed as CSV document.

|-valign="top"| width='120' | '''SignaturesSignature'''|{{Func|<pre>csv:serialize|( $input as nodeitem()?, $options as itemmap(*)|? := map { }) as xs:string}}</pre>|-valign="top"

|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 Option]] 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}} parameter.|- valign="top"| '''Errors'''|{{Error|serialize|#Errors}} argument, the way the node is input cannot be serialized can be controlled. Options can either be specified<br />* |} =Examples= '''Example 1:''' Converts CSV data to XML, interpreting the first row as children of an table header: '''Input''' {{Code|<addressbook.csv:options/>}} element; e.g.:<pre classlang="brush:xml"><csv:options>Name,First Name,Address,City <csv:separator value=';'/> ...</csv:options>Huber,Sepp,Hauptstraße 13,93547 Hintertupfing

* or as '''Query:'''<pre lang='xquery'>let $text := file:read-text('addressbook.csv')return csv:parse($text, map, which contains all key{ 'header': true() })</value pairspre> '''Result:'''<pre classlang="brushxml"><csv> <record> <Name>Huber</Name> <First_Name>Sepp</First_Name> <Address>Hauptstraße 13</Address> <City>93547 Hintertupfing</City> </record></csv></pre>''' '''Example 2:''' Converts some CSV data to XML and back, and checks if the input and output are equal. The expected result is {{Code|true}}: '''Query:'''<pre lang='xquery'>let $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</pre> '''Example 3:''' Converts CSV data to XQuery and returns distinct column values: '''Query:'''<pre lang='xquery"'>let $text := ``[Name,CityJack,ChicagoJack,WashingtonJohn,New York]``let $options := map { 'separatorformat' : ';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), ', ') ))</pre> '''Result:'''<pre lang='xquery'>Distinct values:* Name: Jack, John* City: Chicago, Washington, New York

|-valign="top"|{{Code|BXCS0001parse}}| The input cannot be convertedparsed.|-valign="top"|{{Code|BXCS0002serialize}}

* Updated: return type of [[#csv:parse{{Function||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}}.* Added: {{Code|format}} and {{Code|lax}} parametersoptions