sheet_to_json converts dates differently in Chrome vs Firefox/Safari (browser difference) #3192

New Issue

alifeee · 2024-09-03T12:13:20Z

alifeee commented

2024-09-03 12:13:20 +00:00

Consider the following CSV

one,two,three
1,2,3
9-2,is,not a date
10-4,10-5,10-6

In Firefox and Safari, when run through SheetJS via sheet_to_json, it renders as:

one,two,three
1,2,3
9-2,is,not a date
10-4,10-5,10-6

In Chrome, it renders as:

one,two,three
1,2,3
37136,is,not a date
37168,37169,37170

i.e., it replaces "9-2" etc. with serial numbers (https://support.microsoft.com/en-gb/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487)

Using https://serialnumbertodate.com/, we can see that the serial numbers are

I have created a replication of this issue on http://alifeee.co.uk/sheetjs_error_replication/. Please view this site on both Chrome and a non-Chrome browser (Firefox or Safari). I attach Screenshots below

Chrome

Firefox (please ignore the font)

Consider the following CSV ```csv one,two,three 1,2,3 9-2,is,not a date 10-4,10-5,10-6 ``` In Firefox and Safari, when run through SheetJS via [`sheet_to_json`](https://docs.sheetjs.com/docs/api/utilities/array/#array-output), it renders as: ```csv one,two,three 1,2,3 9-2,is,not a date 10-4,10-5,10-6 ``` In Chrome, it renders as: ```csv one,two,three 1,2,3 37136,is,not a date 37168,37169,37170 ``` i.e., it replaces "9-2" etc. with serial numbers (<https://support.microsoft.com/en-gb/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487>) Using <https://serialnumbertodate.com/>, we can see that the serial numbers are ```text 2001-09-02 2001-10-04 2001-10-05 2001-10-06 ``` I have created a replication of this issue on <http://alifeee.co.uk/sheetjs_error_replication/>. Please view this site on both Chrome and a non-Chrome browser (Firefox or Safari). I attach Screenshots below Chrome ![image](/attachments/0531b65d-5897-4525-892e-581dec84b781) Firefox (please ignore the font) ![image](/attachments/1c511cf5-4f97-43bc-9d47-5a01f94e837c)

image.png

63 KiB

image.png

64 KiB

sheetjs commented

2024-09-03 14:40:16 +00:00

This is due to an ambiguity in the JavaScript specification.

https://tc39.es/ecma262/multipage/numbers-and-dates.html#sec-date.parse

If the String does not conform to that format the function may fall back to any implementation-specific heuristics or implementation-specific date formats.

It's likely we need to shift away from the Date constructor.

This is due to an ambiguity in the JavaScript specification. https://tc39.es/ecma262/multipage/numbers-and-dates.html#sec-date.parse > If the String does not conform to [that format](https://tc39.es/ecma262/multipage/numbers-and-dates.html#sec-date-time-string-format) the function may fall back to any implementation-specific heuristics or implementation-specific date formats. It's likely we need to shift away from the `Date` constructor.

alifeee commented

2024-09-04 12:26:16 +00:00

I see. Thank you for the reply.

My overall desire was to disable date formatting overall, which I now discover you can do by setting raw: false.

const data_array = XLSX_lib.utils.sheet_to_json(sheet, {
  header: 1,
  raw: false,
});

I had tried using raw: true (as I expected "raw values" to mean "strings", and not vice versa).

The help text for raw, to me, was confusing

Use raw values (true) or formatted strings (false)

as I expected "raw values" to be "strings", and was not sure what "formatted strings" meant, which is why I did not discover how to disable date-parsing.

I see. Thank you for the reply. My overall desire was to disable date formatting overall, which I now discover you can do by setting `raw: false`. ```js const data_array = XLSX_lib.utils.sheet_to_json(sheet, { header: 1, raw: false, }); ``` I had tried using `raw: true` (as I expected "raw values" to mean "strings", and not vice versa). The [help text](https://docs.sheetjs.com/docs/api/utilities/array/#array-output) for `raw`, to me, was confusing > Use raw values (true) or formatted strings (false) as I expected "raw values" to be "strings", and was not sure what "formatted strings" meant, which is why I did not discover how to disable date-parsing.

Sign in to join this conversation.