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.

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 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, 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 conversion effects of CSV to XML and the effects some of the single conversion options.

===MapXQuery===

If With the {{Code|format}} is set to {{Code|mapxquery}}format, the CSV data will be records are converted to an XQuery mapa sequence of arrays:

* All The resulting value will be a map with a {{Code|records are enumerated with positive integers}} and an optional {{Code|names}} key.* By default, all Records are organized as a sequence of arrays. A single array contains the entries of a records are represented in a sequencesingle record.* If the The column names will be available if {{Code|header}} option is set to {{Code|true}}, a . The CSV map is created, which contains 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 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 its valuescan be directly accessed without conversion. 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" | Option! 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 data. The format is only relevant if the {{Code|header}} option is activated:<br/>

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

The CSV function signatures provide an {{Code|$options}} argument. Options can either be specified=Functions=* as children of an {{Code|<csv:options/>}} element; e.g.:<pre class="brush:xml"><csv:options> <=csv:separator valuedoc=';'/> ...</csv:options></pre>* or as map, which contains all key/value pairs:<pre class="brush:xquery">{ 'separator': ';', ... }</pre>

{| width=Functions'100%'|- valign="top"| width='120' | '''Signatures'''|{{Func|csv:doc|$uri as xs:string?|item()?}}<br />{{Func|csv:doc|$uri as xs:string?, $options as map(*)?|item()?}}<br />|- valign="top"| '''Summary'''|Fetches the CSV document referred to by the given {{Code|$uri}} and converts it to an XQuery value. The {{Code|$options}} argument can be used to control the way the input is converted.|- valign="top"| '''Errors'''|{{Error|parse|#Errors}} the specified input cannot be parsed as CSV document.<br/>{{Error|options|#Errors}} the specified options are conflicting.|}

|-valign="top"

|{{Func|csv:parse|$input string as xs:string?|document-node(elementitem(csv))?}}<br/>{{Func|csv:parse|$input string as xs:string?, $options as itemmap(*)?|item()?}}|-valign="top"

|Converts the CSV data specified by {{Code|$inputstring}} to an XML document or a mapXQuery value. The {{Code|$options}} argument can be used to control the way the input is converted.|-valign="top"

|{{Error|BXCS0001parse|#Errors}} the specified input cannot be parsedas CSV document.

|-valign="top"

|{{Func|csv:serialize|$input as nodeitem()?|xs:string}}<br/>{{Func|csv:serialize|$input as item()?, $options as itemmap(*)?|xs:string}}|-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.Items Values can also be serialized as JSON if CSV with the standard [[Serialization|Serialization Parameter]] feature of XQuery:* The parameter {{Code|method}} is needs to be set to {{Code|csv}}.<br/>The , and* the options presented in this article need to be assigned to the {{Code|$optionscsv}} argument can be used to control the way the input is serializedparameter.|-valign="top"

|{{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 $options output := { 'lax': 'no' }let $xml :input = > csv:parse($text, $options)let $csv := > csv:serialize($xml, $options)return $text input eq $csvoutput</presyntaxhighlight>

'''Example 3:''' Converts CSV data to an XQuery map item and serializes its contentsreturns distinct column values:

<pre classsyntaxhighlight lang="brush:xquery">let $text := "``[Name;,City" || outJack,ChicagoJack,WashingtonJohn,New York]``let $options := map { 'format':nl() || "John;Newton" || out'xquery', 'header':nltrue() || "Jack;Oldtown"}let $options csv := <csv:parse($text, $options>)return ( <csv 'Distinct values:separator value=';'/>, <csv let $records :format value=$csv('maprecords'/>) < for $name at $pos in $csv:header value=('yesnames'/>)?* </csv:options>let $map values := csv:parse$records?($text, $optionspos) return map( '* ' || $name || ':serialize' || string-join(distinct-values($mapvalues), ', ') ))</presyntaxhighlight>

<pre classsyntaxhighlight lang="brush:xmlxquery">{ 1Distinct values: { "City"* Name: "Newton"Jack, "Name": "John" }, 2: { "* City": "Oldtown"Chicago, Washington, "Name": "Jack" }}New York</presyntaxhighlight>

|-valign="top"|{{Code|BXCS0001parse}}

|-valign="top"|{{Code|BXCS0002serialize}}

* Updated: [[#csv:parse{{Function||csv:parse]] }} now returns a document node instead of an element, or an XQuery map if {{Code|format}} is set to {{Code|map}}.