Resolving Incorrect Parsing of Parentheses-Inclusive Strings in CSV file #2968

New Issue

BitYoungjae · 2023-06-20T06:29:29Z

BitYoungjae commented

2023-06-20 06:29:29 +00:00

First-time contributor

There is an issue where if a cell in a CSV contains a string like 256(3),
it is incorrectly parsed as -2563.

What could be the reason for this, and how can i solve the problem?

There is an issue where if a cell in a CSV contains a string like `256(3)`, it is incorrectly parsed as `-2563`. What could be the reason for this, and how can i solve the problem?

sheetjs commented

2023-06-20 06:39:24 +00:00

CSV and other plaintext formats mix "content" (the raw value) and "presentation" (the formatted text). When parsing CSV, the library applies heuristics (it "guesses" the implied value) to recover values. This is how a row like 1,2,3 is interpreted as 3 numeric cells.

Excel can generate that specific CSV string 256(3) in at least 3 different ways:

a string cell with value 256(3)
the number 2563 with format #(#)
the number -2563 with format #(#);#(#)

Pass raw: true to XLSX.read or XLSX.readFile to completely suppress value parsing (https://docs.sheetjs.com/docs/api/parse-options in the docs)

If you have any other misparsed cells, please comment here and we'll take a closer look.

CSV and other plaintext formats mix "content" (the raw value) and "presentation" (the formatted text). When parsing CSV, the library applies heuristics (it "guesses" the implied value) to recover values. This is how a row like `1,2,3` is interpreted as 3 numeric cells. Excel can generate that specific CSV string `256(3)` in at least 3 different ways: - a string cell with value `256(3)` - the number `2563` with format `#(#)` - the number `-2563` with format `#(#);#(#)` Pass `raw: true` to `XLSX.read` or `XLSX.readFile` to completely suppress value parsing (https://docs.sheetjs.com/docs/api/parse-options in the docs) If you have any other misparsed cells, please comment here and we'll take a closer look.

👍 1

Sign in to join this conversation.