search

janisdd / vscode-edit-csv

vs code extension to edit csv files with an excel like table ui
MIT License
211 stars 30 forks source link

non-en number styles are escaped when applying changes and/or saving file #145

Closed hammerandpick closed 5 months ago

hammerandpick commented 6 months ago

What OS?

Description

When using a file with non-en number formats, these numbers are excaped during saving the file. This is not expected an should be handled when non-en Numbers are used in the file.

Upon loading of file there it is performed well. image

Numbers may be edited, no negative behaviour may be seen in this step. image

After applying changes to file or saving file these non-en number formats are escaped. image

Quote all fields was turned of. Presetting number formats in configuration hat no effect on this. All numbers are shown corretly when in edit mode.

Expected behavior

non-en number formats should be handled correctly. When detecting other delimiters it should be possible to prevent converting numbers to escaped strings.

Steps to reproduce

  1. Save this content to csv-file:

    "Saldo";"SdoWaehr";"AgBlz";"AgKto";"AgName1";"Storno";"OrigBtg";"Betrag";"BtgWaehr" 449,69;"EUR";"XXX";"XXX123";;0;449,69;449,69;"EUR" 449,69;"EUR";"XXX";"XXX123";;0;449,69;449,69;"EUR" 449,69;"EUR";"XXX";"XXX123";;0;449,69;449,69;"EUR"

  2. Open this csv-file and click on Edit csv.
  3. Edit a numerical value. Numbers are handled fine.
  4. Click on "Apply Changes to file" or "... and save"
  5. Check file in raw data format. All non-en numbers are now escaped.

    "Saldo";"SdoWaehr";"AgBlz";"AgKto";"AgName1";"Storno";"OrigBtg";"Betrag";"BtgWaehr" "449,69";"EUR";"XXX";"XXX123";;"0";"449,69";"449,69";"EUR" "449,68";"EUR";"XXX";"XXX123";;"0";"449,69";"449,69";"EUR" "449,69";"EUR";"XXX";"XXX123";;"0";"449,69";"449,69";"EUR"`

hammerandpick commented 6 months ago

Workaround: After modifying data use VisualStudio Code's replace function with RegEx enabled. image

  1. Enter "(-?\d+,?\d*)" in search field.
  2. Activate "Use Regular Expressions (Alt-R)" .
  3. Enter $1 in replace field.
  4. Execute replace all.
janisdd commented 6 months ago

Well, this actually has nothing to do with number formats. In fact, the number format is only intended for the "number sum" and is not used for other formatting.

The "problem" here is that the first line is in quotes. Currently, the extension only stores the quote information column by column and uses the first row to determine if the cells in a column should be quoted. If you remove the quotation marks in the first row, it works as expected.

You can prevent the extension from using the first row for quote information by setting csv-edit.retainQuoteInformation to false. However, this will not use quotation mark information from the original file. In your case, the quotation marks will be removed from the first line if they are not needed.

janisdd commented 5 months ago

This would only be possible if the quote information was stored for each field. This increases memory and raises a lot of questions when the shape of the table is changed (add/delete operations). There are currently no plans to support this.