Unexpected quoting of cell values that contain leading or trailing spaces

[tartale]

What OS?

• Mac

Description

When working with a file that has cells with leading or trailing spaces, quotes are always added to those cells, ignoring the values of the related settings:

"csv-edit.newColumnQuoteInformationIsQuoted": false,
"csv-edit.quoteAllFields": false,
"csv-edit.quoteEmptyOrNullFields": "false",
"csv-edit.retainQuoteInformation": true,

Expected behavior

When loading, modifying, and saving a file with the above settings, existing cell values that have extra spaces should not be automatically quoted.

Steps to reproduce

I've only tried this with pipe "|" delimiters, but the following example should demonstrate the issue...

• Create a file (e.g. "test.csv" with the following contents:

foo     |   bar  |biz
baz     |boz| buz 

• Make a minor change to one of the cells and save:
  

• Quotes are introduced into the original file:

"foo     "|"   bar  "|bizb
"baz     "|boz|" buz "

Per the rules described here, I assumed that the row starting with foo would be used to determine quotes, and none of the cells in that row have quotes.

It's possible that this is correct behavior, or that the settings to get the behavior I want are different than what I'm using.

The desired behavior would be very useful when working with files that have aligned columns.

Workaround

I found a workaround with the Rainbow CSV extension; if I "shrink" values, then use Edit CSV, and then re-align columns, I can get the end result I wanted, but the extra steps are a bit tedious.

[janisdd]

The Retain Quote Information Rules settings work as expected but is not related to this.

This comes from papaparse, which handles the parsing of CSV files itself.
It always adds quotes when necessary (necessary in the sense of papaparse). This is also the reason why |boz and bizb are not put in quotes.
Quotes are added if the field itself contains the delimiter or quotes and begins or ends with a space.

The part starts or ends with a space is a decision made by papaparse to make the csv file more deterministic.
Some csv parsers might ignore the spaces between the text and the delimiter, so the spaces would be lost.
By inserting quotes, all parsers are forced to take the spaces into account.

Just to be sure, do you expect the spaces to be ignored when the file is opened, or should only the quotes be removed?

[tartale]

@janisdd - thanks for the response!
Ideally, if the cell doesn't have spaces to begin with, if I make a change to that cell, I'd like the changed file to also not have spaces

So in the example above, I'd simply end up with:

foo     |   bar  |bizb
baz     |boz| buz 

(no quotes are added to any of the cells, and the cell that I modified also does not have quotes, because it didn't in the first place)

Here's another example; suppose I have this initial input:

foo     |"   bar|  "|bizb
baz     |boz| buz 

In this example, bar must be quoted because it has a pipe character in it. So, if I edit that cell, and remove the pipe, I should end up with:

foo     |"   bar  "|bizb
baz     |boz| buz 

In this example, the plugin respected that the cell had quotes to begin with, so it included those when it wrote back out.

Now that I know that the settings are working as-expected, this issue is more like a feature request than a bug. It also sounds like the dependency on papaparse might prevent this capability.

[janisdd]

When the content is written back to the file, the quotes are probably not necessary if there are only spaces. I'm not sure why papaparse does this, but I maintain my own copy of papaparse for this plugin. I can take a look at the code and see if I can find anything there.

The other problem concerns the course information when reading the file. Currently, the course information is only stored per column (first cell in each column). This is to reduce memory usage (and was somewhat easier to implement).
But using more memory is probably ok if it implements the function.

The only question would be how to set the quote information for newly created fields. Probably not quoting would be best. (Or change the option newColumnQuoteInformationIsQuoted to newCellQuoteInformationIsQuoted and set it to false).

Ultimately, saving the initial quote for each field is probably inevitable as the user expects it.

However, changing all this is a lot of work and will take some time, and I'll have to see if the first problem can be solved.

[janisdd]

Just a quick update: I found an issue at papaparse that raises an interesting question in this context (issue 941).

If you have

a,b,c

you expect a, b, c

If you have

a, b, c

you expect a, b, c (with space)

but if you have

a, b, "c"

what should one expect to happen? The quotes clearly indicate that c should have no leading space.
However, what should be done with the space after the , and before the "?

[tartale]

Here are my thoughts:

Basically, having an "aligned" version of a CSV seems like a common use case, and it makes sense for a tool to do this by introducing leading and trailing spaces.

When a user puts a explicit quotes into the cell, it should indicate that the entire cell is "verbatim", i.e. everything in there, including leading spaces, trailing spaces, and instances of the separator, are to be treated as part of the cell value.

With papaparse, it is assumed that leading/trailing whitespace should always be quoted; this happens at this part in that source code. FWIW, this is in the main branch as well, and is pretty old, looking at the blame

IMHO, the assumption that PapaParse makes is somewhat debatable. I would expect that a parse->unparse that makes no changes should yield no changes to the input file, but papaparse doesn't support that.

Since you're maintaining a copy of papaparse, probably the simplest way to get this behavior is to have an option that just flat-out disables the quoting, so that whatever you put in, you get out (minus edits, of course).

I took a crack at this; I'll post a PR. It will likely need some changes, as I've not worked with vscode plugins before.

[janisdd]

I agree that parsing->unparsing should cause no changes. If the user does not make any changes, this is easy. However, the user can change the structure of the table (e.g. delete rows/cols, insert rows/cols, sorting). Every edit has to be tracked and synced with the quote information.

When parsing, quotes must be respected. If unparsing disables quoting (disableAutomaticQuoting), the result will definitely be different from the input (if the input had quotes).
Also, in some cases having quotes in the output is inevitable (the other conditions in the if):

• cell value contains "
• cell value contains the delimiter
• cell value contains a new line

This may not be the case with the original entry, but the user can make changes to the cell values.

Thanks for the PR, but I think I'll try to track each cell individually.

BTW, my copy of papaparse is maintained in https://github.com/janisdd/PapaParse in the branch fix609_main (in case I need to use it somewhere else).
I already added some options to disable leading and trailing spaces here: janisdd/PapaParse@cbaa3e5#diff-fbb8d0b19dc4586fadd80f52a7b49415d2d41a5fdccaeaa728f7fca51cfbbb29R595

The latest version can already output information about the input file, which cells have been quoted.
Only the hard part with the tracking of structural changes to the table is still missing (in this extension).

Anyway, when this is completed, the leading and trailing spaces in the input file still become spaces in the cell values, just the quotes in the output are missing. This is what the csv rfc says and what I (and probably others) would expect from the csv parser.

[tartale]

okay! I'll close the PR, and try out the plugin when the change is available. Thank you!

[janisdd]

Initial tests look good... However, I discovered some major problems with handsontable (index handling). Fixing this problem could potentially affect every function of handsontable. It happens whenever the visually displayed rows (visual indexes) and the actual rows (physical indexes) are different, e.g. after sorting or moving rows/columns in combination with adding/deleting rows/col.
This means that the problem also affects the quote information, as we must track all changes (indices).

It is also the reason why some features don't work as expected, e.g. #131

The long-term goal would be to fix the core index issue, but this requires reviewing and testing every handsontable function that uses indexes (almost all of them).

I'm a bit hesitant to release this as it can mess up quote information in some circumstances (described above)...
However, for most use cases it will work fine. Perhaps the previous behaviour that the first cell in the column determines whether a cell is quoted can be kept and the option to track each cell individually can be enabled manually (with a note that there are some issues).

---

I have also added the options forceQuoteLeadingWhitespace and forceQuoteTrailingWhitespace which can be used to force quotes in cells with leading or trailing spaces.