Bureaucrats, editor, reviewer, Administrators
13,554
edits
Changes
Jump to navigation
Jump to search
m
{{Mark|Updated with Version 9.0:}}
This format has been introduced with {{Version|9.0}}. It is more flexible and light-weight than the old, discarded {{Code|map}} format.
{{Mark|Updated with Version 9.0:}}
Text replacement - "syntaxhighlight" to "pre"
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.
=Conventions=
All functions and errors in this module are assigned to the <code><nowiki>http://basex.org/modules/csv</nowiki></code> namespace, which is statically bound to the {{Code|csv}} prefix.<br/>
==ConversionFormats==
===XML: Direct, Attributes===
If the {{Code|direct}} or {{Code|attributes}} format is chosen, a CSV string is converted to XML:
* 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 or (when invalid as first character of an element name) prefixed with an underscore.** 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.
'''A little advice''': in the Database Creation dialog of the GUI, if you select CSV Parsing and switch to the ''Parsing'' tab, you can see the effects of some of the conversion options.
===XQuery===
With the {{Code|xquery}} format, CSV records are converted to a sequence of arrays:
* The resulting value will be a map with a {{Code|records}} entry and an optional {{Code|names}} keyentry.* Records are organized as a sequence of arrays. A single An array contains the entries of a single record.* The {{Code|names}} entry contains an array with the column names will be available . 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 2nd field (column)
* <code>$csv?names?*</code> returns the names of all fields (if available)
* Return enumerated strings for all records:
<pre classlang="brush:'xquery"'>
for $record at $pos in $csv?records
return $pos || ". " || string-join($record?*, ', ')
==Options==
In the following table, all available options are listed. The Excel column indicates what are the preferred lists recommended options for data that is to be imported, processed with Excel or has been exported from ExcelOpen/Libre Office.
{| class="wikitable sortable" width="100%"
|- valign="top"
! width="140" | Option! width="50%" | Description
! Allowed
! Default
! Direction
! Excel
|- valign="top"
| {{Code|comma}}, {{Code|semicolon}}, {{Code|colon}}, {{Code|tab}}, {{Code|space}} or a ''single character''
| {{Code|comma}}
| ''parse'', ''serialize''| {{Code|semicolon}}or {{Code|comma}}, depending on the region
|- valign="top"
| {{Code|header}}
| {{Code|yes}}, {{Code|no}}
| {{Code|no}}
| ''parse'', ''serialize''
|
|- valign="top"
| {{Code|format}}
| Specifies the format of the XML for converting CSV data:<br/>* With {{Code([[#Conversion Formats|direct}} conversion, field names are represented as element names* With {{Code|attributes}} conversion, field names are stored in {{Code|name}} attributes* With {{Code|xquery}} conversion, the input is converted to an XQuery mapsee above]]).
| {{Code|direct}}, {{Code|attributes}}, {{Code|xquery}}
| {{Code|direct}}
| ''parse'', ''serialize''
|
|- valign="top"
| {{Code|lax}}
| 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"
| 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 quotesif 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.
| {{Code|yes}}, {{Code|no}}
| {{Code|yes}}
| ''parse'', ''serialize''
| {{Code|yes}}
|- valign="top"
| {{Code|yes}}, {{Code|no}}
| {{Code|no}}
| ''parse'', ''serialize''
| {{Code|no}}
|- valign="top"
| {{Code|skip-empty}}
| {{Announce|Version 11:}} Indicates if empty fields are included in the result. Only gets effective for the formats {{Code|direct}} or {{Code|attribute}}, and if the {{Code|header}} option is enabled. Please note that if this option is used and the data is serialized again, the resulting CSV header may be incomplete.
| {{Code|yes}}, {{Code|no}}
| {{Code|no}}
| ''parse''
| {{Code|no}}
|- valign="top"
| {{Code|allow}}
| In Excel, a value will be evaluated if it starts with the character {{Code|-}}, {{Code|+}}, <code>=</code>, {{Code|@}}, {{Code|\t}} or {{Code|\r}}. A regular expression can be specified to reject data that will be handled differently than expected by an application, or that may be malicious (see https://owasp.org/www-community/attacks/CSV_Injection for more details).
| ''string''
|
| ''serialize''
| <code><nowiki>[^-+=\t\r].*|[-+]\d*([,.]\d+)?</nowiki></code>
|}
=Functions=
==csv:doc==
{| width='100%'
|- valign="top"
| width='120' | '''Signature'''
|<pre>csv:doc(
$href as xs:string?,
$options as map(*)? := map { }
) as item()?</pre>
|- valign="top"
| '''Summary'''
|Fetches the CSV document referred to by the given {{Code|$href}} 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.
|}
==csv:parse==
{| width='100%'
|-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 map(*)?| := map { }) as item()}}?</pre>|-valign="top"
| '''Summary'''
|Converts the CSV data specified by {{Code|$inputvalue}} 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"
| '''Errors'''
|{{Error|parse|#Errors}} the specified input cannot be parsedas CSV document.
|}
{| width='100%'
|-valign="top"| width='120' | '''SignaturesSignature'''|{{Func|csv:serialize|$input as item()?|xs:string}}<br/pre>{{Func|csv:serialize|( $input as item()?, $options as map(*)?| := map { }) as xs:string}}</pre>|-valign="top"
| '''Summary'''
|Serializes the specified {{Code|$input}} as CSV, using the specified {{Code|$options}}, and returns the result as string.
* The parameter {{Code|method}} needs to be set to {{Code|csv}}, and
* the options presented in this article need to be assigned to the {{Code|csv}} parameter.
|-valign="top"
| '''Errors'''
|{{Error|serialize|#Errors}} the input cannot be serialized.
'''Input''' {{Code|addressbook.csv}}:
<pre classlang="brush:xml">
Name,First Name,Address,City
Huber,Sepp,Hauptstraße 13,93547 Hintertupfing
'''Query:'''
<pre classlang="brush:'xquery"'>
let $text := file:read-text('addressbook.csv')
return csv:parse($text, map { 'header': true() })
'''Result:'''
<pre classlang="brush:xml">
<csv>
<record>
'''Query:'''
<pre classlang="brush:'xquery"'>
let $options := map { 'lax': false() }
let $input := file:read-text('some-data.csv')
'''Query:'''
<pre classlang="brush:'xquery"'>
let $text := ``[Name,City
Jack,Chicago
let $records := $csv('records')
for $name at $pos in $csv('names')?*
let $values := $records?($pos)
return (
'* ' || $name || ': ' || string-join(distinct-values($values), ', ')
'''Result:'''
<pre classlang="brush:'xquery"'>
Distinct values:
* Name: Jack, John
=Errors=
{| class="wikitable" width="100%"
! width="110"|Code
|Description
|-valign="top"
|{{Code|parse}}
| The input cannot be parsed.
|-valign="top"
|{{Code|serialize}}
| The node cannot be serialized.
=Changelog=
;Version 11
* Added: [[#Options|Options]]: <code>skip-empty</code> option.
;Version 9.7
* Added: [[#Options|Options]]: <code>allow</code> option.
;Version 9.4
* Added: {{Function||csv:doc}}
; Version 9.1
* Updated: {{Function||csv:parse}} can be called with empty sequence.
;Version 9.0
;Version 7.8
* 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}}.
* Added: {{Code|format}} and {{Code|lax}} options
The module was introduced with Version 7.7.2.
