Bureaucrats, editor, reviewer, Administrators
13,554
edits
Changes
Jump to navigation
Jump to search
m
Text replacement - "syntaxhighlight" to "pre"
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===
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?names?*</code> returns the names of all fields (if available)
* Return enumerated strings for all records:
<syntaxhighlight pre lang="'xquery"'>
for $record at $pos in $csv?records
return $pos || ". " || string-join($record?*, ', ')
</syntaxhighlightpre>
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 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"
| {{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:parsedoc==
{| width='100%'
|-valign="top"| width='120' | '''SignaturesSignature'''|{{Func|csv:parse|$string as xs:string?|item()?}}<br/pre>{{Func|csv:parse|doc( $string href as xs:string?, $options as map(*)?| := map { }) as item()?}}</pre>|-valign="top"
| '''Summary'''
|Converts Fetches the CSV document referred to by the given {{Code|$stringhref}} 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:docparse==
{| width='100%'
|-valign="top"| width='120' | '''SignaturesSignature'''|{{Func|csv:doc|$uri as xs:string?|item()?}}<br /pre>{{Func|csv:doc|parse( $uri value as xs:string?, $options as map(*)?| := map { }) as item()?}}<br /pre>|-valign="top"
| '''Summary'''
|Fetches Converts the CSV document referred to by the given {{Code|$urivalue}} 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.
|}
{| 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}}:
<syntaxhighlight pre lang="xml">
Name,First Name,Address,City
Huber,Sepp,Hauptstraße 13,93547 Hintertupfing
</syntaxhighlightpre>
'''Query:'''
<syntaxhighlight pre lang="'xquery"'>
let $text := file:read-text('addressbook.csv')
return csv:parse($text, map { 'header': true() })
</syntaxhighlightpre>
'''Result:'''
<syntaxhighlight pre lang="xml">
<csv>
<record>
</record>
</csv>
</syntaxhighlightpre>
'''
'''Query:'''
<syntaxhighlight 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
</syntaxhighlightpre>
'''Example 3:''' Converts CSV data to XQuery and returns distinct column values:
'''Query:'''
<syntaxhighlight pre lang="'xquery"'>
let $text := ``[Name,City
Jack,Chicago
)
)
</syntaxhighlightpre>
'''Result:'''
<syntaxhighlight pre lang="'xquery"'>
Distinct values:
* Name: Jack, John
* City: Chicago, Washington, New York
</syntaxhighlightpre>
=Errors=
! 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: [[#csv:doc{{Function||csv:doc]]}}
; Version 9.1
* Updated: [[#csv:parse{{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.
