Main Page » Mailing List » April 2022

Enclosing strings by double quotes for CSV data output

Friday, 1 April 2022 · 24 messages · last reply 8 Apr 2022
ME
Markus Elfring1 Apr 2022 12:45 #
Hello, I constructed a small XQuery script which contains the following specifications.
declare option output:method "csv";
declare option output:csv "header=yes, separator=|, quotes=yes";
…
The query execution succeeded (in principle) with the software “BaseX 9.7”. I observed data processing results where displayed columns contained space characters. I would expect that such data should be handled as text strings then. Thus I would expect also that these data should be enclosed by double quotes (according to the safe handling of CSV files). But this did not happen for my software test. Will any additional settings become relevant for this use case? Regards, Markus
CG
Christian Grün1 Apr 2022 12:57 #
Hi Markus,
Quoted text · Markus Elfring · 2 lines
> I observed data processing results where displayed columns contained space characters. > I would expect that such data should be handled as text strings then.
Double quotes in CSV files do not give any information on the data type of a value. Instead, they merely ensure that the data can be correctly parsed. If the pipe symbol is used as separator, there is no need to enclosed whitespaced string by quotes. Do you possibly have a processor that interprets the syntax of the incoming CSV data to detect and assign data types? Best, Christian
ME
Markus Elfring1 Apr 2022 14:30 #
Quoted text · Christian Grün · 2 lines
> Double quotes in CSV files do not give any information on the data > type of a value.
I came along software applications which would need the usage of double quotes for the distinction that provided data should be handled as text strings.
Quoted text · Christian Grün · 1 line
> Instead, they merely ensure that the data can be correctly parsed.
I suggest to reconsider this view a bit more for some data imports.
Quoted text · Christian Grün · 2 lines
> If the pipe symbol is used as separator, there is no need to enclosed > whitespaced string by quotes.
How do you think about to avoid the interpretation of a column delimiter character within strings as a CSV separator?
Quoted text · Christian Grün · 2 lines
> Do you possibly have a processor that interprets the syntax of the > incoming CSV data to detect and assign data types?
Occasionally, yes. Regards, Markus
CG
Christian Grün1 Apr 2022 14:37 #
Quoted text · Markus Elfring · 2 lines
> I came along software applications which would need the usage of double quotes > for the distinction that provided data should be handled as text strings.
So you would probably require all non-numeric values to be enclosed by quotes, no matter if it contains spaces? What about other data types (booleans, etc.)?
Quoted text · Markus Elfring · 2 lines
> How do you think about to avoid the interpretation of a column delimiter > character within strings as a CSV separator?
Wrapping will take place if the column delimiter occurs in the value.
ME
Markus Elfring1 Apr 2022 15:00 #
Quoted text · Christian Grün · 5 lines
> > I came along software applications which would need the usage of double quotes > > for the distinction that provided data should be handled as text strings. > > So you would probably require all non-numeric values to be enclosed by > quotes, no matter if it contains spaces?
The answer depends on the configuration for data import tools. Example: spreadsheet applications
Quoted text · Christian Grün · 1 line
> What about other data types (booleans, etc.)?
Further data type indications can eventually be omitted, can't they? Regards, Markus
CG
Christian Grün1 Apr 2022 15:03 #
Quoted text · Markus Elfring · 2 lines
> > What about other data types (booleans, etc.)? > Further data type indications can eventually be omitted, can't they?
A custom XQuery function may be the best option if you require a data-type specific output of CSV data. For further general information on CSV serialization and the handling of double quotes, feel free to check out the RFC: https://datatracker.ietf.org/doc/html/rfc4180
ME
Markus Elfring1 Apr 2022 15:23 #
Quoted text · Christian Grün · 4 lines
> > Further data type indications can eventually be omitted, can't they? > > A custom XQuery function may be the best option if you require a > data-type specific output of CSV data.
I hope that such code can be avoided if the BaseX CSV module could be adjusted accordingly. https://docs.basex.org/wiki/CSV_Module#Options Regards, Markus
CG
Christian Grün1 Apr 2022 15:31 #
Quoted text · Markus Elfring · 2 lines
> I hope that such code can be avoided if the BaseX CSV module could be adjusted accordingly. > https://docs.basex.org/wiki/CSV_Module#Options
I wouldn’t completely factor out this option, but we definitely need to define more rules to nail this down. Just some examples: 1. Is there any specification (RFC, ISO, something else) that gives us more details on your exact requirements? 2. What’s the name of the software applications that you use that requires double quotes? 3. What would be the determining factor for using double quotes? Is it the existence of whitespaces (spaces, tabs, nl, cr, …?), or would all non-numeric values need to be quoted?
ME
Markus Elfring4 Apr 2022 09:09 #
Quoted text · Christian Grün · 1 line
> > https://docs.basex.org/wiki/CSV_Module#Options
…
Quoted text · Christian Grün · 2 lines
> 2. What’s the name of the software applications that you use that > requires double quotes?
Text qualifiers can accordingly be configured for data exports and imports. Examples: * LibreOffice Calc * Microsoft Excel
Quoted text · Christian Grün · 1 line
> 3. What would be the determining factor for using double quotes?
A specific data type indication should occasionally be applied for texts.
Quoted text · Christian Grün · 1 line
> Is it the existence of whitespaces (spaces, tabs, nl, cr, …?),
This can happen. I observed that content from different XML elements can be joined by using space characters.
Quoted text · Christian Grün · 1 line
> or would all non-numeric values need to be quoted?
Probably, not. I am unsure under which circumstances additional delimiters would be requested for more fine-grained data type distinctions. Regards, Markus
CG
Christian Grün4 Apr 2022 13:25 #
Quoted text · Markus Elfring · 3 lines
> Examples: > * LibreOffice Calc > * Microsoft Excel
Double quotes are only required for parsing, they are not used to judge if the input is a string or number. A little example: 1;"1" If you save this one-liner as CSV file and open it with Excel, but values will be interpreted and formatted as numbers.
Quoted text · Markus Elfring · 2 lines
> > 3. What would be the determining factor for using double quotes? > A specific data type indication should occasionally be applied for texts.
I can’t follow, sorry. Examples might help. Maybe the issue you want to solve is a non-issue? How can we reproduce the problem you encountered with Excel?
ME
Markus Elfring4 Apr 2022 14:32 #
Quoted text · Christian Grün · 1 line
> Double quotes are only required for parsing,
I would like to know a bit more about corresponding requirements.
Quoted text · Christian Grün · 1 line
> they are not used to judge if the input is a string or number.
I got other impressions (or expectations).
Quoted text · Christian Grün · 5 lines
> A little example: > 1;"1" > > If you save this one-liner as CSV file and open it with Excel, > but values will be interpreted and formatted as numbers.
I suggest to take another look at available information sources. * Text Import Wizard by Microsoft Excel https://support.microsoft.com/en-gb/office/text-import-wizard-c5b02af6-fda1-4440-899f-f78bafe41857#ID0EBBJ-supTabControlContent-1 Step 2 of 3 (Delimited data) … Text qualifier … * Text Import by LibreOffice https://help.libreoffice.org/7.3/en-GB/text/shared/00/00000208.html#hd_id314847411 Regards, Markus
CG
Christian Grün4 Apr 2022 14:50 #
Quoted text · Markus Elfring · 1 line
> I would like to know a bit more about corresponding requirements.
Sorry again, maybe someone else can you help here. I simply don’t understand what you want to express.
Quoted text · Markus Elfring · 3 lines
> * Text Import Wizard by Microsoft Excel > https://support.microsoft.com/en-gb/office/text-import-wizard-c5b02af6-fda1-4440-899f-f78bafe41857#ID0EBBJ-supTabControlContent-1 > Step 2 of 3 (Delimited data) … Text qualifier …
I’ll quote the text of your quoted reference:
Quoted text · Markus Elfring · 1 line
> Text qualifier: Select the character that encloses values in your text file. When Excel encounters the text qualifier character, all of the text that follows that character and precedes the next occurrence of that character is imported as one value, even if the text contains a delimiter character. For example, if the delimiter is a comma (,) and the text qualifier is a quotation mark ("), "Dallas, Texas" is imported into one cell as Dallas, Texas. If no character or the apostrophe (') is specified as the text qualifier, "Dallas, Texas" is imported into two adjacent cells as "Dallas and Texas".
That’s what already happens. The double quote is the text qualifier that’s used for serializing fields with spaces. There’s currently no feature to choose an alternative character (such as the apostrophe), but I think that’s not what you are looking for anyway. Please help us if you want us to help you. Be more specific and spend more time in explaining your specific challenge. Give us a step-by-step description (e.g., a simple reproducible example) on a) what you do b) what you get and c) what you expect to get.
ME
Markus Elfring4 Apr 2022 15:40 #
Quoted text · Christian Grün · 2 lines
> That’s what already happens. The double quote is the text qualifier > that’s used for serializing fields with spaces.
I did not observe text quotation during my software tests.
Quoted text · Christian Grün · 1 line
> There’s currently no feature to choose an alternative character
This can be fine.
Quoted text · Christian Grün · 1 line
> (such as the apostrophe),
Applications can occasionally need additional data type indicators, can't they?
Quoted text · Christian Grün · 1 line
> but I think that’s not what you are looking for anyway.
I would like to enclose field data which correspond to text string data types by double quotes. Regards, Markus
CG
Christian Grün4 Apr 2022 16:55 #
Quoted text · Markus Elfring · 1 line
> I did not observe text quotation during my software tests.
See [1] for some instruction on how to create a reproducible example. Thanks in advance. [1] http://sscce.org/
ME
Markus Elfring5 Apr 2022 16:00 #
Quoted text · Christian Grün · 3 lines
> > I did not observe text quotation during my software tests. > > See [1] for some instruction on how to create a reproducible example.
I created a BaseX database from a single XML file which contains the following data for another test approach.
<test_data>
<row>
<ID>01</ID>
<T1>X Y</T1>
<T2>Y, X</T2>
</row>
</test_data>
I tried the following XQuery script out.
declare option output:method "csv";
declare option output:csv "header=yes, quotes=yes, separator=|";
for $x in //test_data/row
return
<csv>
<record>
<ID>{$x/ID/data()}</ID>
<T1>{$x/T1/data()}</T1>
<T2>{$x/T2/data()}</T2>
</record>
</csv>
Will the corresponding test result need any further clarification? ID|T1|T2 01|X Y|Y, X How good does such a data display fit to expectations on desirable software behaviour? https://docs.basex.org/wiki/CSV_Module#Options Regards, Markus
CG
Christian Grün5 Apr 2022 17:31 #
Thanks a lot for the example, Markus. No quotes will be added, as your delimiter does not occur in the text value. The result can successfully be imported in spreadsheet applications without quotes. The quotes will be added if the delimiter occurs in the texts:
declare option output:method 'csv';
declare option output:csv 'header=yes, quotes=yes, separator=|';
<csv>
  <record>
    <one>a b</one>
    <two>c|d</two>
  </record>
</csv>
… yields … one|two a b|"c|d" Hope this helps, Christian
ME
Markus Elfring6 Apr 2022 15:25 #
Quoted text · Christian Grün · 1 line
> No quotes will be added, as your delimiter does not occur in the text value.
How does this feedback fit to previously provided information? A) https://docs.basex.org/wiki/CSV_Module#Options “… Serialization: If the option is enabled, the value will be wrapped with quotes if it contains characters that might be treated as control characters. …” B) https://mailman.uni-konstanz.de/pipermail/basex-talk/2022-April/017038.html “… The double quote is the text qualifier that’s used for serializing fields with spaces. …” Another XQuery script example:
declare option output:method "csv";
declare option output:csv "header=yes, quotes=yes, separator=|";
<csv>
<record>
<T3>line 1
line 2?</T3>
<T4>line 3\nline 4?</T4>
</record>
</csv>
Test result: T3|T4 "line 1 line 2?"|line 3\nline 4? Will any more clarification help here? Regards, Markus
ME
Markus Elfringin reply to Bridger Dyson-Smith, 6 Apr 2022, 17:586 Apr 2022 16:34 #
Quoted text · Bridger Dyson-Smith · 9 lines
> > Test result: > > T3|T4 > > "line 1 > > line 2?"|line 3\nline 4? > > > > > > Will any more clarification help here? > > …, but if you're asking "Why are there double quotes around my `T3` field?",
Partly, yes. (But not really.)
Quoted text · Bridger Dyson-Smith · 1 line
> then the answer appears to be in the CSV specification[1].
Which impressions did you get from the other BaseX information sources which I pointed out?
Quoted text · Bridger Dyson-Smith · 1 line
> So, the behavior you're seeing in your sample script matches the CSV RFC.
What is your opinion for the data processing of the fields “T1”, “T2” and “T4”? Regards, Markus
ME
Markus Elfringin reply to Bridger Dyson-Smith, 6 Apr 2022, 19:006 Apr 2022 17:16 #
Quoted text · Bridger Dyson-Smith · 1 line
> My opinion of these results is that they are correct - they meet my expectations.
I find this view interesting.
Quoted text · Bridger Dyson-Smith · 1 line
> Do you feel like these results are missing something?
Yes.
Quoted text · Bridger Dyson-Smith · 1 line
> If so, what specifically?
I am missing text quoting for the fields “ID”, “T1”, “T2” and “T4”.
Quoted text · Bridger Dyson-Smith · 1 line
> Is there something in the documentation that could change to make things more explicit or provide better clarity?
I got such an impression. Regards, Markus
BD
Bridger Dyson-Smithin reply to Markus Elfring, 6 Apr 2022, 15:256 Apr 2022 17:58 #
Hi Markus -
Quoted text · Markus Elfring · 42 lines
On Wed, Apr 6, 2022 at 9:25 AM Markus Elfring <Markus.Elfring at web.de> wrote: > > No quotes will be added, as your delimiter does not occur in the text > value. > > How does this feedback fit to previously provided information? > > A) > https://docs.basex.org/wiki/CSV_Module#Options > “… > Serialization: If the option is enabled, the value will be wrapped with > quotes if it contains characters that might be treated as control > characters. > …” > > B) > https://mailman.uni-konstanz.de/pipermail/basex-talk/2022-April/017038.html > “… > The double quote is the text qualifier that’s used for serializing fields > with spaces. > …” > > > Another XQuery script example: > declare option output:method "csv"; > declare option output:csv "header=yes, quotes=yes, separator=|"; > <csv> > <record> > <T3>line 1 > line 2?</T3> > <T4>line 3\nline 4?</T4> > </record> > </csv> > > Test result: > T3|T4 > "line 1 > line 2?"|line 3\nline 4? > > > Will any more clarification help here? > > I confess that I'm not sure, but if you're asking "Why are there double
quotes around my T3 field?", then the answer appears to be in the CSV specification[1]. So, the behavior you're seeing in your sample script matches the CSV RFC. If that wasn't your question, it might help to restate, or reframe, your questions and concerns.
Quoted text · Markus Elfring · 3 lines
> Regards, > Markus >
Best wishes, Bridger [1] https://datatracker.ietf.org/doc/html/rfc4180#section-2, specifically part 6:
Quoted text · Markus Elfring · 8 lines
Fields containing line breaks (CRLF), double quotes, and commas > should be enclosed in double-quotes. For example: > > "aaa","b CRLF > bb","ccc" CRLF > zzz,yyy,xxx > >
ZD
Zimmel, Danielin reply to Markus Elfring, 6 Apr 2022, 17:166 Apr 2022 18:26 #
If any CSV parser complains about the perfectly valid data in this example, I would definitely say it is poorly implemented, because it does not comply to the RFC. I took a glance at the mail thread and I could not find a hint where exactly you are having trouble with the quotes missing apart from saying that they are missing - is this a piece of software that cannot handle CSV properly? If so, I would definitely switch my parser. Daniel
Quoted text · Markus Elfring · 16 lines
-----Ursprüngliche Nachricht----- Von: BaseX-Talk <basex-talk-bounces at mailman.uni-konstanz.de> Im Auftrag von Markus Elfring Gesendet: Mittwoch, 6. April 2022 17:17 An: Bridger Dyson-Smith <bdysonsmith at gmail.com> Cc: BaseX <basex-talk at mailman.uni-konstanz.de> Betreff: Re: [basex-talk] Enclosing strings by double quotes for CSV data output > My opinion of these results is that they are correct - they meet my expectations. I find this view interesting. > Do you feel like these results are missing something? Yes. > If so, what specifically? I am missing text quoting for the fields “ID”, “T1”, “T2” and “T4”. > Is there something in the documentation that could change to make things more explicit or provide better clarity? I got such an impression. Regards, Markus
BD
Bridger Dyson-Smithin reply to Markus Elfring, 6 Apr 2022, 16:346 Apr 2022 19:00 #
Markus - Another confession: I've never been very good at puzzles and riddles :( so...
Quoted text · Markus Elfring · 24 lines
On Wed, Apr 6, 2022 at 10:34 AM Markus Elfring <Markus.Elfring at web.de> wrote: > > > Test result: > > > T3|T4 > > > "line 1 > > > line 2?"|line 3\nline 4? > > > > > > > > > Will any more clarification help here? > > > > …, but if you're asking "Why are there double quotes around my `T3` > field?", > > Partly, yes. > (But not really.) > > That's certainly a cryptic response! :) > > > then the answer appears to be in the CSV specification[1]. > > Which impressions did you get from the other BaseX information sources > which I pointed out? > > My impressions with the BaseX documentation, generally speaking, are very
positive, and when I have questions (or need clarification, or help, etc), my impressions of the community here are even more positive: what a welcoming and generous group of people. I'm lucky to participate.
Quoted text · Markus Elfring · 5 lines
> > So, the behavior you're seeing in your sample script matches the CSV RFC. > > What is your opinion for the data processing of the fields “T1”, “T2” and > “T4”? >
Wrapping up the other examples you've shared in this mail thread, along with some changes: e.g.
xquery version "3.1";
declare option output:method "csv";
declare option output:csv "header=yes,quotes=yes,separator=|";
declare variable $input :=
<test_data>
  <row>
    <ID>01</ID>
    <T1>X Y</T1>
    <T2>Y, X</T2>
    <T3>line 4
    or a line break in the T3 field.</T3>
    <T4>line 6\nor something - maybe this is line 7? It might not be: what
interprets the '\n' character? BaseX? Something else? Maybe we want the {
string-join(('&amp;','#','10',';','')) } character (linefeed literal)?</T4>
  </row>
  <row>
    <ID>02</ID>
    <T1>A 2nd row</T1>
  </row>
</test_data>;

<csv>
  {
    for $row in $input//row
    return (
      <record>
        {
          for $field in $row/child::*
          return (
            $field
          )
        }
      </record>
    )
  }
</csv>
Provides the results that I would expect:
ID|T1|T2|T3|T4
01|X Y|Y, X|"line 4
    or a line break in the T3 field."|line 6\nor something - maybe this is
line 7? It might not be: what interprets the '\n' character? BaseX?
Something else? Maybe we want the &#10; character (linefeed literal)?
02|A 2nd row|||
My opinion of these results is that they are correct - they meet my expectations. Do you feel like these results are missing something? If so, what specifically? Is there something in the documentation that could change to make things more explicit or provide better clarity?
Quoted text · Markus Elfring · 4 lines
> > Regards, > Markus >
Best, Bridger
TM
Tamara Marnellin reply to Markus Elfring, 6 Apr 2022, 17:166 Apr 2022 21:02 #
Hello Markus, In the Microsoft documentation you linked, the name "text qualifier" is misleading. It doesn't mean the value will always be formatted as text by the program. For example, I tried making this CSV: ID|T1|T2 "01"|"April 6, 2022"|"01234567890123456789" In a new spreadsheet in Excel, I imported the CSV with the custom delimiter pipe (|), and all three of the values were interpreted as formats other than text. "01" became the integer 1, "April 6, 2022" the date 4/6/2022, and the long number became 1.234567E+18. So adding double quotes around all values to designate a "text" datatype will not work for Excel; users still need to manually change the formats of the cells. The "Format quoted field as text" is a LibreOffice-specific option that does format all of the values as text, not numbers or dates. To accomplish the same in Excel requires several complicated steps. Examples: https://www.winhelponline.com/blog/stop-excel-convert-text-to-number-date-format-csv-file/ Also, a user might want "April 6, 2022" to be interpreted as a date for calculations, so whitespace in a value doesn't necessarily mean it's supposed to be treated as text. The current behavior of BaseX will output a CSV that can be correctly interpreted by both Calc and Excel with the correct separators/delimiters set, so I don't believe it should be changed, and even if it were it wouldn't accomplish what you want. -Tamara
Quoted text · Markus Elfring · 25 lines
On Wed, Apr 6, 2022 at 8:17 AM Markus Elfring <Markus.Elfring at web.de> wrote: > > My opinion of these results is that they are correct - they meet my > expectations. > > I find this view interesting. > > > > Do you feel like these results are missing something? > > Yes. > > > > If so, what specifically? > > I am missing text quoting for the fields “ID”, “T1”, “T2” and “T4”. > > > > Is there something in the documentation that could change to make things > more explicit or provide better clarity? > > I got such an impression. > > Regards, > Markus >
-- Tamara Marnell Program Manager, Systems Orbis Cascade Alliance (orbiscascade.org <https://www.orbiscascade.org/>) Pronouns: she/her/hers
ME
Markus Elfring8 Apr 2022 14:33 #
Dear Tamara, Thanks for your detailed feedback.
Quoted text · Tamara Marnell · 1 line
> In the Microsoft documentation you linked, the name "text qualifier" is misleading.
Can this view trigger any further consequences?
Quoted text · Tamara Marnell · 1 line
> It doesn't mean the value will always be formatted as text by the program.
This aspect is interesting, isn't it?
Quoted text · Tamara Marnell · 1 line
> "01" became the integer 1, …
Which data types can handle “numbers” so that leading zeros would be preserved?
Quoted text · Tamara Marnell · 1 line
> So adding double quotes around all values to designate a "text" datatype will not work for Excel;
This can be fine in some use cases.
Quoted text · Tamara Marnell · 1 line
> users still need to manually change the formats of the cells.
This can happen also.
Quoted text · Tamara Marnell · 1 line
> The "Format quoted field as text" is a LibreOffice-specific option
Would you like to take related program variants better into account?
Quoted text · Tamara Marnell · 1 line
> that does format all of the values as text,
Do you like such a software functionality?
Quoted text · Tamara Marnell · 1 line
> not numbers or dates.
I find such a setting occasionally useful.
Quoted text · Tamara Marnell · 1 line
> To accomplish the same in Excel requires several complicated steps.
Do any advanced users require special CSV data import configurations?
Quoted text · Tamara Marnell · 1 line
> Examples: https://www.winhelponline.com/blog/stop-excel-convert-text-to-number-date-format-csv-file/
Will the attention grow for further clarification also according to information from the article “Stop Excel from Converting Text to Number or Date format when Opening a CSV file”?
Quoted text · Tamara Marnell · 1 line
>…, and even if it were it wouldn't accomplish what you want.
I suggest to reconsider this view a bit more. Do I eventually need to reimplement CSV export functionality by XQuery means of customised string concatenations? Regards, Markus

Written for BaseX 9.7. BaseX may have changed since: please check the current documentation.

« Support for performing queries on query resultscopy-paste, can't copy in BaseX and then paste into Emacs (Debian) »
⚡Generated with XQuery