dateNF parsing option ignored #658

New Issue

2017-05-17T13:25:11Z

mmancosu commented

2017-05-17 13:25:11 +00:00

(Migrated from github.com)

I still have a parsing problem in version 0.10.2.

dateNF is used for formatting the output but not as parsing option.

In my example

COD,DES,DATE,NUM
01,ZEROONE,12/01/2009,1.2
02,ZEROTWO,16/02/2016,3

dates are in dd/mm/yyyy format.

The parsing system from base64 text returns 12/01/2009 as 30/11/2009 (with time zone problem), clearly reading from the format mm/dd/yyyy. 16/02/2016 returns data type string, because 16 is not a valid month.

Fiddle proving it: https://jsfiddle.net/92qu04vd/2/

I still have a parsing problem in version 0.10.2. dateNF is used for formatting the output but not as parsing option. In my example > COD,DES,DATE,NUM 01,ZEROONE,12/01/2009,1.2 02,ZEROTWO,16/02/2016,3 dates are in dd/mm/yyyy format. The parsing system from base64 text returns 12/01/2009 as 30/11/2009 (with time zone problem), clearly reading from the format mm/dd/yyyy. 16/02/2016 returns data type string, because 16 is not a valid month. Fiddle proving it: [https://jsfiddle.net/92qu04vd/2/](https://jsfiddle.net/92qu04vd/2/)

SheetJSDev commented

2017-05-17 14:51:05 +00:00

(Migrated from github.com)

@mmancosu thanks for continuing this :) There are two questions to ask: what does Excel do and what should we do?

"What does Excel do?" Excel 2016 US locale treats 16/02/2016 as a string and 12/01/2009 as a "date with timezone problem", precisely how we handle it right now:

To reproduce, type '16/02/2016 in A2 to force text interpretation, type 16/02/2016 in B2, and =IF(TYPE(B2)=2,"TEXT",IF(TYPE(B2)=1,"NUMBER","OTHER")) in C2

"What should we do?" As for dateNF, that controls the final object but not how the values are read. Now that you mention it, that's not a bad idea (interpret the dateNF as a mask, and if the text matches the mask then interpret the numbers accordingly).

@mmancosu thanks for continuing this :) There are two questions to ask: what does Excel do and what should we do? "What does Excel do?" Excel 2016 US locale treats `16/02/2016` as a string and `12/01/2009` as a "date with timezone problem", precisely how we handle it right now: <img width="231" alt="screen shot 2017-05-17 at 10 43 18" src="https://cloud.githubusercontent.com/assets/6070939/26159837/a72d7e0a-3aed-11e7-950c-fcd565921f53.png"> To reproduce, type `'16/02/2016` in A2 to force text interpretation, type `16/02/2016` in B2, and `=IF(TYPE(B2)=2,"TEXT",IF(TYPE(B2)=1,"NUMBER","OTHER"))` in C2 "What should we do?" As for `dateNF`, that controls the final object but not how the values are read. Now that you mention it, that's not a bad idea (interpret the dateNF as a mask, and if the text matches the mask then interpret the numbers accordingly).

mmancosu commented

2017-05-17 15:11:49 +00:00

(Migrated from github.com)

@SheetJSDev thanks for your work :)
My need is to parse the date string and understand which date is. Excel does exactly what did you show us. It will be great if dateNF could be interpreted as a mask. The following part of Data Number Format documentation made me suppose that dateNF worked exactly in the way I need:

...
To get around this ambiguity, parse functions accept the dateNF option to override the interpretation of that specific format string.

@SheetJSDev thanks for your work :) My need is to parse the date string and understand which date is. Excel does exactly what did you show us. It will be great if dateNF could be interpreted as a mask. The following part of Data Number Format documentation made me suppose that dateNF worked exactly in the way I need: > ... To get around this ambiguity, parse functions accept the dateNF option to override the interpretation of that specific format string.

SheetJSDev commented

2017-05-17 16:03:54 +00:00

(Migrated from github.com)

The context of that paragraph was:

Format 14 (m/d/yy) is localized by Excel: even though the file specifies that number format, it will be drawn differently based on system settings. It makes sense when the producer and consumer of files are in the same locale, but that is not always the case over the Internet.

Specifically for that format, Excel will use the client settings to draw the date. If you go to regional and language settings, you can change the computer locale and Excel will show different text! That only happens for one number format, everything else remains the same. https://github.com/SheetJS/js-xlsx/issues/326#issuecomment-286014758 The original locale isn't stored in the file (its stored for some XLS files but XLSX and other formats have no provision for saving the original locale) so you can't magically deduce what was originally used, hence the option.

But your interpretation makes more sense :) What we can do is specifically test for the number format first.

The context of that paragraph was: > Format 14 `(m/d/yy)` is localized by Excel: even though the file specifies that number format, it will be drawn differently based on system settings. It makes sense when the producer and consumer of files are in the same locale, but that is not always the case over the Internet. Specifically for that format, Excel will use the client settings to draw the date. If you go to regional and language settings, you can change the computer locale and Excel will show different text! That only happens for one number format, everything else remains the same. https://github.com/SheetJS/js-xlsx/issues/326#issuecomment-286014758 The original locale isn't stored in the file (its stored for some XLS files but XLSX and other formats have no provision for saving the original locale) so you can't magically deduce what was originally used, hence the option. But your interpretation makes more sense :) What we can do is specifically test for the number format first.

mmancosu commented

2017-05-18 07:51:58 +00:00

(Migrated from github.com)

Yeah, I misunderstood the meaning of Format 14 :)
What do you mean with your last words?

Yeah, I misunderstood the meaning of Format 14 :) What do you mean with your last words?

mmancosu commented

2017-05-26 15:40:44 +00:00

(Migrated from github.com)

Hi @SheetJSDev, is there something new about this issue? :)

SheetJSDev commented

2017-06-01 21:27:41 +00:00

(Migrated from github.com)

A complete solution would be the inverse of SSF, but for now the date format is tokenized and a "fixed" date is produced by matching fields and rewriting as ISO8601 (if the value matches the date format).

Sign in to join this conversation.