Retain original date format in sheet_to_json #2628

New Issue

2022-03-25T16:07:45Z

tomyates commented

2022-03-25 16:07:45 +00:00

(Migrated from github.com)

I know there are a lot of similar issues, but I've not found one that solves my problem and I have the feeling is might not be possible...

A user uploads an xlsx document, and one of the columns is a date, and it's displayed as "25/03/1981"
I'd like to configure sheet_to_json so it returns "25/03/1981" as a string. Ie, return exactly as how it's being displayed in Excel.
I've tried many different configurations, and can't get it to work - it'll return in the format "MM/DD/YYYY", or days since the epoch, or as a JS Date stamp, but not the original format.

Note: I cannot use dateNF: "DD/MM/YYYY" to format after, as I don't know in advance what format they are uploading.
Eg, they could upload an xlsx document with dates such as "1981-03-26 12:00"

Any ideas?
Thanks

I know there are a lot of similar issues, but I've not found one that solves my problem and I have the feeling is might not be possible... A user uploads an xlsx document, and one of the columns is a date, and it's displayed as "25/03/1981" I'd like to configure sheet_to_json so it returns "25/03/1981" as a string. Ie, return exactly as how it's being displayed in Excel. I've tried many different configurations, and can't get it to work - it'll return in the format "MM/DD/YYYY", or days since the epoch, or as a JS Date stamp, but not the original format. Note: I cannot use dateNF: "DD/MM/YYYY" to format after, as I don't know in advance what format they are uploading. Eg, they could upload an xlsx document with dates such as "1981-03-26 12:00" Any ideas? Thanks

reviewher commented

2022-03-25 17:35:57 +00:00

(Migrated from github.com)

raw: false will use the formatted value. The default will use the raw value. https://docs.sheetjs.com/#json

`raw: false` will use the formatted value. The default will use the raw value. https://docs.sheetjs.com/#json

tomyates commented

2022-03-25 19:26:14 +00:00

(Migrated from github.com)

Thanks for replying.
I've tried that but for the file attached, it returns US format (mm/dd/yyyy) rather than UK format (dd/mm/yyyy).

UAT_dataset1.xlsx

Output:

{
  ID: '35854DBB-7B17-5DB3-4DBA-5AE52C381A21',
  First_Name: 'Jaquelyn',
  Second_Name: 'Oneill',
  DateOfBirth: '3/20/28',
  Email: 'JaquelynOneill@email.com'
},
{
  ID: 'DE9E114E-DA61-3148-2FDC-4DFB9EF5B812',
  First_Name: 'Jared',
  Second_Name: 'Serrano',
  DateOfBirth: '4/26/19',
  Email: 'JaredSerrano@email.com'
}

Code:

const XLSX = require("xlsx");

const wb = XLSX.readFile('test.xlsx');
const sheet = wb.Sheets[wb.SheetNames[0]];

const results = XLSX.utils.sheet_to_json(sheet, {raw:false});

console.log(results);

Thanks for replying. I've tried that but for the file attached, it returns US format (mm/dd/yyyy) rather than UK format (dd/mm/yyyy). [UAT_dataset1.xlsx](https://github.com/SheetJS/sheetjs/files/8353325/UAT_dataset1.xlsx) ![image](https://user-images.githubusercontent.com/785769/160187873-6320f903-5862-47cc-abdc-68280b5afcb5.png) Output: ``` { ID: '35854DBB-7B17-5DB3-4DBA-5AE52C381A21', First_Name: 'Jaquelyn', Second_Name: 'Oneill', DateOfBirth: '3/20/28', Email: 'JaquelynOneill@email.com' }, { ID: 'DE9E114E-DA61-3148-2FDC-4DFB9EF5B812', First_Name: 'Jared', Second_Name: 'Serrano', DateOfBirth: '4/26/19', Email: 'JaredSerrano@email.com' } ``` Code: ``` const XLSX = require("xlsx"); const wb = XLSX.readFile('test.xlsx'); const sheet = wb.Sheets[wb.SheetNames[0]]; const results = XLSX.utils.sheet_to_json(sheet, {raw:false}); console.log(results); ```

tomyates commented

2022-03-27 11:31:18 +00:00

(Migrated from github.com)

@reviewher how can I re-open this ticket? Thank you.

tomyates commented

2022-03-28 18:04:05 +00:00

(Migrated from github.com)

Has anyone got an idea why I can't the dates as formatted in the sheet?

tomyates commented

2022-04-12 08:18:42 +00:00

(Migrated from github.com)

I've uniziped the XLSX file and looked at the style sheet and realised the issue is to do with the number format numFmtId="14".

Apparently OpenXML / ECMA-376 (edition 1) gives the wrong date format for format code 14.

More here: https://github.com/SheetJS/sheetjs/issues/181
and here: https://social.msdn.microsoft.com/Forums/office/en-US/e27aaf16-b900-4654-8210-83c5774a179c/xlsx-numfmtid-predefined-id-14-doesnt-match?forum=oxmlsdk

I've uniziped the XLSX file and looked at the style sheet and realised the issue is to do with the number format `numFmtId="14"`. Apparently OpenXML / ECMA-376 (edition 1) gives the wrong date format for format code 14. More here: https://github.com/SheetJS/sheetjs/issues/181 and here: https://social.msdn.microsoft.com/Forums/office/en-US/e27aaf16-b900-4654-8210-83c5774a179c/xlsx-numfmtid-predefined-id-14-doesnt-match?forum=oxmlsdk

SheetJSDev commented

2022-04-12 08:23:00 +00:00

(Migrated from github.com)

Date code 14 is localized, see https://github.com/SheetJS/sheetjs/issues/326 for more details. The dateNF option was added as a workaround, but the real solution is to ship tables for each relevant locale (or try to speculate using Intl)

Date code 14 is localized, see https://github.com/SheetJS/sheetjs/issues/326 for more details. The `dateNF` option was added as a workaround, but the real solution is to ship tables for each relevant locale (or try to speculate using `Intl`)

Sign in to join this conversation.