413: Spec for CSV parsing with fn:parse-csv() - #533

Looks like I need to remind myself how to sign commits...

@fidothe Thank you! As the author of a book on XQuery that has a whole section on parsing CSV with XQuery 3.1, I know that readers will appreciate having this built in to the language!

I wonder if you would consider a slightly different format - returning header and body rows in arrays instead of sequences.

So instead of:

map{"headers": map{"name":1, "city":2}, "body": [("Bob","Berlin"), ("Alice","Aachen")]}

It would return:

map{"headers": ["name", "city"], "body": [["Bob","Berlin"], ["Alice","Aachen"]]}

Using your format, I struggled to write a function that would transform the parsed CSV into an HTML table—a standard use case. But using mine, this would be straightforward:

xquery version "3.1";

declare function local:csv-to-table($csv-map) {
    <table>
        <thead>
            <tr>{
                for $header in $csv-map?headers?* 
                return
                    <th>{$header}</th>
            }</tr>
        </thead>
        <tbody>{
            for $row in $csv-map?body?*
            return
                <tr>{
                    for $cell in $row?* 
                    return
                        <td>{$cell}</td>
                }</tr>
        }</tbody>
    </table>    
};

let $parsed-csv := map{"headers": ["name", "city"], "body": [["Bob","Berlin"], ["Alice","Aachen"]]}
return
    local:csv-to-table($parsed-csv)

This returns:

<table>
    <thead>
        <tr>
            <th>name</th>
            <th>city</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>Bob</td>
            <td>Berlin</td>
        </tr>
        <tr>
            <td>Alice</td>
            <td>Aachen</td>
        </tr>
    </tbody>
</table>

The difficulty with your format is in getting at the rows enclosing the cells. I think users will have to use map:keys() for the header row and array:for-each() for the body rows, since $parsed-csv?headers?* would return numbers and $parsed-csv?body?* would return each a sequence of all cells, instead of a sequence of rows.

xquery version "3.1";

declare function local:csv-to-table($csv-map) {
    <table>
        <thead>
            <tr>{
                for $header in map:keys($csv-map?headers)
                return
                    <th>{$header}</th>
            }</tr>
        </thead>
        <tbody>{
            array:for-each(
                $csv-map?body, 
                function($sequence) { 
                    <tr>{
                        for $item in $sequence
                        return
                            <td>{$item}</td>
                    }</tr>}
                )
        }</tbody>
    </table>    
};

let $parsed-csv := map{"headers": map{"name":1, "city":2}, "body": [("Bob","Berlin"), ("Alice","Aachen")]}
return
    local:csv-to-table($parsed-csv)

Particularly for the body rows, I think most XQuery users would prefer the convenience of querying nested arrays over the complexity of arrays of sequences.

I committed the cardinal sin of going on holiday the day after opening this PR, so I am writing this from a beach on a phone…

I need to have access to a computer to check, but I thought I could use arrays-of-sequences to build a tbody like this:

<tbody>{
            for $row in $csv-map?body?*
            return
                <tr>{
                    for $cell in $row
                    return
                        <td>{$cell}</td>
                }</tr>
        }</tbody>

and it sounds like for-ing over an array rather than a sequence has more problems than my basic XQuery and reading of the spec suggested.

I have some specific issues with arrays-of-fields around behaviour when accessing fields missing because of variable-length rows/records, but I’ll wait till I’m back to continue this discussion…

We're introducing "for member $x in $array" so iterating over an array becomes easier...

Having established that my understanding of ?* when applied to an array-of-sequences was lacking (sorry about that), and having been rescued by @michaelhkay and for member ... in ..., we could use the following to do the looping over an array-of-sequences:

<tbody>{
  for member $row in $csv-map?body
    return
      <tr>{
        for $cell in $row return <td>{$cell}</td>
      }</tr>
}</tbody>

I still think array-of-sequences is the best option for the low-level case because access by field index (directly or via a header lookup) cannot be guaranteed not to raise an exception if the record is an array thanks to being unable to guarantee all records have the same number of fields.

I also think that the straightforward just-build-a-table-in-XML case, where the records and fields of a CSV are effectively one-to-one mapped onto rows and cells in an XML/HTML table, should be better supported, but I'm still trying to figure out whether that should be via more options to fn:parse-csv() or a new function (csv:to-xml() perhaps?) that takes a function executed per-record as an argument, whose return value should be the XML for the record.

There are also a couple of other basic options I'm thinking about for fn:parse-csv() after I encountered some CSVs with fixed-width cells padded with spaces, namely that an option to trim whitespace would be useful enough to have in the base function, and an option to treat empty-string fields as being the empty sequence in any csv:to-xml()-type function for iterating over a defined size csv.

I am inclined to agree with @joewiz that just returning a map of the headers is insufficient. Given that we won't be returning duplicate header strings, or empty-string header strings, you'd need to use map:size(), find the maximum value, and then hope that your CSV had no empty fields in the header record after the end to get the number of cells right when making a <thead>. I'm going to add a header-record key to the returned map that contains the raw headers as a sequence, just like all the other records.

I've now added a csv-to-xml function to the spec that makes the kinds of straightforward CSV-to-tabular-XML conversion simpler. It would perform the iteration, passing each record to a function argument. An option allows you to choose which fields you want and in which order, should you need that.

Some comments.

Starting with the examples in 15.7.3

First example:

let $parsed-csv := fn:parse-csv("name,city\r\nBob,Berlin\r\n"),
    $headers := $parsed-csv?headers 
return $parsed-csv?body(1)[$headers("name")]
(: "Bob" :)

Is the escape sequence \r\n correct? XPath doesn't recognise this as representing a newline, is parse-csv supposed to recognise it? Note that an XPath string can contain a literal newline:

let $csv :=
"name,city
Bob,Berlin
"
so it might be better to write it that way. Though it's a bit precarious if nested in XSLT attributes...

I wonder if the name "columns" might be better than "headers"? $column["name"] feels more natural.

I'm also wondering if the returned record could include an arity-2 function "cell" allowing

let $parsed-csv := fn:parse-csv("name,city\r\nBob,Berlin\r\n"),
return $parsed-csv?cell(1, "name")

Second example

let $my-headers := map{"first-name": 1, "last-name": 2},
    $parsed-csv := fn:parse-csv("name,,city\r\nBob,Mustermann,Berlin\r\n")
    $data := $parsed-csv?body
return string-join(($data(1)[$my-headers?last-name], $data[$my-headers?first-name]), ", ")
(: "Mustermann, Bob" :)

Is (1) missing from the extraction of the first-name?

I'm not sure why you would want to do it this way. Wouldn't it be simpler to do:

let $FIRST := 1, $LAST := 2,
    $parsed-csv := fn:parse-csv("name,,city\r\nBob,Mustermann,Berlin\r\n")
    $data := $parsed-csv?body
return string-join(($data(1)[$LAST], $data(1)[$FIRST), ", ")
(: "Mustermann, Bob" :)

Also this is a good case for a string template:

let $row := $data(1)
return {$row[$LAST]}, {$row[$FIRST]}

Fourth example (15.7.3.2)

I think it would be good to show this in XSLT as well as XQuery (and to identify the language used).

Perhaps rename ?headers-record as ?column-names (because it isn't a record in the XPath sense, it's a sequence of strings).

Shouldn't the thead contain a tr?

I think I would write the array:for-each as

for member $row in $parsed-csv?body
return <tr>{ for $field in $row return <td>{ $field }</td> }</tr>

15.7.3.3 csv-to-xml

This example doesn't suggest there is any significant benefit in using csv-to-xml in preference to doing it "by hand".

15.7.3.4 mapping field types

The example uses the variable $headers before it is declared.

Again an XSLT version would be nice.

We should really have an fn:substitute() function so instead of

for $index in (1 to count($record))
        return if ($headers?Date = $index)
            then xs:date($record[$index])
            else $record[$index]
    }

you can write
$record => substitute($headers?Date, xs:date#1)

I'll add that as an issue...

Now the spec of the function itself.

There's a problem that record-separator is defined as a string, but the default is a sequence of strings. Perhaps it should be defined as a regex, as in tokenize()?

If field-separator and quote-character are required to be single characters then we should say so and define an error code for when they aren't.

I'm wondering about the precise relationship of our spec to the RFC. At the moment we just include it as a normative reference, without any glossing. But we're also clearly permitting variations, e.g. different characters for separators. Do we need to say a bit more about this? For example we're leaving the reader to work out that if the quote character is set to apostrophe, then it gets escaped as two apostrophes, but there's nothing either in our spec or the RFC that spells this out.

Also the RFC uses "should" and "may" rather a lot (e.g. "Each line should contain the same number of fields throughout the file" (By "line" they mean "record"). Do we want to be more definitive? Must an implementation of our function accept a variable number of fields?

I would expect to see some language along the lines of "An implementation MUST accept an input CSV that .... and MAY accept an input CSV that ....".

I can't see the point of the return-headers option. If it's useful then add some motivation?

trim-whitespace: normally in XPath and XSD we do normalize (or collapse) whitespace, where as well as removing leading and trailing whitespace, we collapse multiple internal whitespace into a single space character.

Is the escape sequence \r\n correct? XPath doesn't recognise this as representing a newline, is parse-csv supposed to recognise it? Note that an XPath string can contain a literal newline:

No, it's not correct.

There's a tension here between an example that will run if copied and pasted into a script, and an example which communicates as simply as possible, and in the case of CSV there is a meaningful distinction between CRLF, CR, and LF, which needs to be unambiguously communicated.

The main source of input for fn:parse-csv() is the result of a call to fn:unparsed-text(). In order to avoid indirection in the simple examples, I want to just use a string literal, and I can't show the CR and LF characters without resorting to some kind of escaping. In an XPath-only example I don't have entities to use (
, 
), and if I just include the characters you can't see them in the example. The \r\n convention is familiar to most, so it seems like the least-worst option at the moment. Ideally we would have a way to represent those characters that would be unambiguous to a reader, and be copy-pasteable into an XPath-only, XQuery or XSLT context. That doesn't seem possible to me given my understanding of the markup available in the spec (that understanding is not especially comprehensive...), so I would love suggestions for alternatives that allow unambiguous representation of CR and LF and will parse in the contexts we'd like them to.

I presume this is also a problem when talking about the output of fn:unparsed-text() more generally, but the documentation for that function is able to sidestep the problem by not showing any output...

I wonder if the name "columns" might be better than "headers"? $column["name"] feels more natural.

Perhaps "field" - the RFC 4180 terminology is "records" and "fields" rather than rows and columns, and I'm trying to follow that where possible. (All things being equal, I'd rather have "rows" and "columns", but following the RFC seems worth doing even if I am more used to the more spreadsheet-centric way of thinking about CSVs)

I'm also wondering if the returned record could include an arity-2 function "cell" allowing

let $parsed-csv := fn:parse-csv("name,city\r\nBob,Berlin\r\n"),
return $parsed-csv?cell(1, "name")

I wondered about something like that, but including functions in the parsed-csv-structure-record seemed like it might go against the goal of making parse-csv() as primitive as possible. I have no real objection, but I would want something that took a row ("record" in RFC 4180 terms, which seems more annoying now I'm talking about CSV records and XPath records in the same sentence) instead of the index of a row:

let $parsed-csv := fn:parse-csv("name,city\r\nBob,Berlin\r\n"), 
    $row := $parsed-csv?body(1)
return $parsed-csv?cell($row, "name")

I'd suggest writing

let $nl := char('#x0D')||char('#x0A')
let $csv := `name, age{$nl}John,23{$nl}`

There's a problem that record-separator is defined as a string, but the default is a sequence of strings. Perhaps it should be defined as a regex, as in tokenize()?

The default case, in most parser implementations, and in the latest draft of RFC 4180 (but not the published version), is to allow CRLF, CR, or LF and basically pick the one to use based on the first unquoted line ending candidate you encounter. Outside of that, I think we want to require a simple string record separator rather than permitting regexes.

I'm just not sure how to represent that in the documentation.

If field-separator and quote-character are required to be single characters then we should say so and define an error code for when they aren't.

That sounds good to me.

go against the goal of making parse-csv() as primitive as possible

There's a goal to ensure that all the information in the CSV is available without loss, but there's also a goal to provide the information with as much ease of use as possible. Adding one or more access functions alongside the raw data seems to achieve that.

The default case, in most parser implementations, and in the latest draft of RFC 4180 (but not the published version), is to allow CRLF, CR, or LF and basically pick the one to use based on the first unquoted line ending candidate you encounter.

I suggest the default should be the same as for unparsed-text-lines: which is CRLF, CR, or LF with no requirement for consistency within the file. I think we just end up with the situation that the default is not one of the values that can be explicitly requested; the formal default becomes () which is interpreted as meaning use the unparsed-text-lines() rules.

It might be worth a note that line-endings appearing in quoted fields are not normalized. Presumably trailing end-of-line spaces are also signficant.

I'm wondering about the precise relationship of our spec to the RFC. At the moment we just include it as a normative reference, without any glossing. But we're also clearly permitting variations, e.g. different characters for separators. Do we need to say a bit more about this? For example we're leaving the reader to work out that if the quote character is set to apostrophe, then it gets escaped as two apostrophes, but there's nothing either in our spec or the RFC that spells this out.

Also the RFC uses "should" and "may" rather a lot (e.g. "Each line should contain the same number of fields throughout the file" (By "line" they mean "record"). Do we want to be more definitive? Must an implementation of our function accept a variable number of fields?

I would expect to see some language along the lines of "An implementation MUST accept an input CSV that .... and MAY accept an input CSV that ....".

The RFC describes an idealised representation of a data format that has significant variation in the wild. The as-published RFC 4180 is a lot more conservative than the current drafts (Many Excel-produced CSVs of the time of its publication would not meet the spec in the RFC, and no CSVs produced in Germany or any other country using , as a decimal separator would), but it's pretty clear that implementations should be liberal in what they accept. Writing a generator that followed the RFC a lot more closely makes a lot more sense. Later drafts make more concessions to the reality of international conventions in CSVs, but in a wooly way (there's a lot of "Implementors should be aware that some applications may choose to use a different mechanism"). (see https://datatracker.ietf.org/doc/html/draft-shafranovich-rfc4180-bis-04).

I think that I have made too many assumptions that implementations of fn:parse-csv() will be liberal in the way I expect, and I should take another pass through the text looking specifically at those.

As to the terminology, the RFC starts off using "record" and "field" to mean "row" and "column", but often slips into using "line" in place of "record". I would like to be consistent in our use of terminology, and would be happy to switch to "row" and "column" if people felt that the clash with XPath's "Record"-is-a-map-based-data-structure was too confusing.

As to the terminology, the RFC starts off using "record" and "field" to mean "row" and "column", but often slips into using "line" in place of "record". I would like to be consistent in our use of terminology, and would be happy to switch to "row" and "column" if people felt that the clash with XPath's "Record"-is-a-map-based-data-structure was too confusing.

record and field sounds more intuitive to me (but maybe I've just become used to it when working with CSV data).

I've now added a csv-to-xml function to the spec that makes the kinds of straightforward CSV-to-tabular-XML conversion simpler. It would perform the iteration, passing each record to a function argument. An option allows you to choose which fields you want and in which order, should you need that.

Sorry for my scarce feedback (I'm still abroad). As we already have fn:json-to-xml and fn:xml-to-json, it would be very helpful then to also have fn:xml-to-csv. Even if we decide to add that function in a future version, we should ensure that the mapping is bidirectional, i.e. that the data (with possibly empty or redundant header names) can be converted back to the original format (this has turned out to be an essential requirement of our own CSV Module).

For reference, the CoNLL-U format is defined at https://universaldependencies.org/format.html. That has a set of externally defined named headers. The extended format defines a custom set of columns as a comment on the first line -- that could be extracted and parsed separately, e.g. in combination with parsed-lines, so having the ability to pass the line sequence would be useful, as it would prevent reparsing the file.

That format uses # as a line comment where sentence metadata is located, so being able to extract the comments (and then perform additional parsing) would be useful here.

It would be useful to access these fields by name, e.g.:

for $row in fn:parse-csv("en_ewt-ud-dev.conllu")
return $row?LEMMA

Regarding Sasha's performance comment, the English training file is 14MB, so it would be useful for implementors to be able to create a streaming implementation, e.g. by returning a sequence of arrays or maps instead of a single map with a body.

I like the idea of a csv-to-xml function (along the lines of fn:analyze-string) which just delivers an XML structure of the form:

<data>
  <record>
    <field name="First">Michael</field>
    <field name="Last">Kay</field>
    <field name="Date of Birth">....</field>
  </record>
  <record>
     ....
  </record>
</data>

For many XSLT users this may well be the simplest form of result to manipulate. I don't think there's even any need to customise the element names -- we don't do it with fn:analyze-string -- and people know how to transform this into the form they want. There could be an option to force-fit the column headers to NCNames so it becomes

<record>
    <First>Michael</First>
    <Last>Kay</Last>
    <Date_of_Birth>...</Date_of_Birth>

(Algorithm: simply replace any disallowed character by underscore; note that there's no need to make the names unique).

I like the idea of a csv-to-xml function

In our implementation, we support different target formats for CSV via a format option: csv:parse($input, map { 'format': '...' }). As the specification already provides fn:json-to-xml, though, it may be more intuitive to have a fn:csv-to-xml($csv-string) function.

(Algorithm: simply replace any disallowed character by underscore; note that there's no need to make the names unique).

That’s also what we do if the target format is XML (with lax conversion enabled is enabled) if there’s no need to re-create the original structure. Empty strings are represented by a single underscore character.

Based on feedback from the QTCG meeting I'm looking at implementing the following headline changes:

• Rename parts of parsed-csv-structure-record ('records' and 'fields') to bring it more in line with spreadsheet-like concepts: rows, columns, cells.
• Add support for #-preceded comment lines
• Allow specifying a column name to position map in the options to fn:parse-csv()
• There is confusion over the fn:csv-to-xml() function, and a function that took a CSV string/stream and returned generic XML would be well received. What the current proposed function does, which is make regularising and filtering the data easier, and makes iteration more straightforward, might better belong in parse-csv() behind options.

I'm going to learn more about the practicalities of streaming from both the user and implementer side and have another crack at those aspects, which may affect some of the above.

The rewrite discussed on the QT4CG call on 2023-07-11 has been landed in 9e4331c. The biggest discussed issue remaining is functions for generating CSV strings from XDM input.

There's also an open question about mapping from xs:string to other data types, either as part of fn:csv-to-xdm or as something to process its returned data with.

@ChristianGruen made a lot of typographic amends related to, primarily, proper typographic quotes, which I implemented. After that I realised that actually the FO spec almost entirely uses prime (') and double-prime (") instead of proper opening and closing quotes. I think that it would be good to have a style choice for the whole document (I would prefer proper quotes), but maybe switching from the existing style would be better done as a separate PR covering the whole spec later on...

@ChristianGruen made a lot of typographic amends related to, primarily, proper typographic quotes, which I implemented.

Thank you, Matt.

After that I realised that actually the FO spec almost entirely uses prime (') and double-prime (") instead of proper opening and closing quotes.

I hoped this was fixed. The formatting was aligned with the XSLT spec, which uses typographic quotes: https://github.com/qt4cg/qtspecs/pull/511/files

Are you sure you are looking at the current version of the spec / can you give some examples?

I fixed some remnants; maybe you’ve encountered exactly those: #634.

I've squashed the commits and rebased off master ahead of the CG meeting today (2023-07-25). I've also tried signing the commit, but I'm not sure I've got that down correctly...

Now with fixed commit signature

The CG agreed to merge this issue at meeting 043