Still a problem with date parsing #1005

New Issue

2018-02-21T10:50:05Z

svendeckers commented

2018-02-21 10:50:05 +00:00

(Migrated from github.com)

I've read issues similar to mine, but I haven't found a solution yet.
I made a CSV-file in a texteditor, VI, so no Excel-involvement here.
I entered a date in DD/MM/YYYY notation ("01/04/2018"), so this is the first of April.
Any way I try to read the date, I always get the 4th of January returned:

let workbook = XLSX.readFile("./dateTest.csv", {cellDates:true,dateNF:'dd/mm/yyyy'});
let worksheet = workbook.Sheets[workbook.SheetNames[0]];
let cell = worksheet["A2"];
console.log(cell.w);
Results in "04/01/2018".

dateTest.csv:
DATE;
01/04/2018;

It seems the value is interpreted in the local settings, regardless of the dateNF.
I cannot ask my clients to change their settings.
Am I missing something or are we waiting for the locale option to be passed with readFile?

I've read issues similar to mine, but I haven't found a solution yet. I made a CSV-file in a texteditor, VI, so no Excel-involvement here. I entered a date in DD/MM/YYYY notation ("01/04/2018"), so this is the first of April. Any way I try to read the date, I always get the 4th of January returned: `let workbook = XLSX.readFile("./dateTest.csv", {cellDates:true,dateNF:'dd/mm/yyyy'});` `let worksheet = workbook.Sheets[workbook.SheetNames[0]];` `let cell = worksheet["A2"];` `console.log(cell.w);` Results in "04/01/2018". dateTest.csv: `DATE;` `01/04/2018;` It seems the value is interpreted in the local settings, regardless of the dateNF. I cannot ask my clients to change their settings. Am I missing something or are we waiting for the `locale` option to be passed with `readFile`?

sangeethaharidass commented

2018-04-05 07:28:04 +00:00

(Migrated from github.com)

Use xlsx.read function procedure to read a file.

svendeckers commented

2018-04-05 13:56:19 +00:00

(Migrated from github.com)

I'm glad you took the effort, but unfortunately to no avail.

When I read the file using fs.readFile and I print the contents, I get this:
01/04/2018
Then I do:
let workbook = XLSX.read(contents, {cellDates:true,dateNF:'dd/mm/yyyy'});
let worksheet = workbook.Sheets[workbook.SheetNames[0]];
let jsonData = XLSX.utils.sheet_to_json(worksheet, {header: headers, defval: ''});
and print the jsonData and I get:
04/01/2018

I'm glad you took the effort, but unfortunately to no avail. When I read the file using `fs.readFile` and I print the contents, I get this: `01/04/2018` Then I do: `let workbook = XLSX.read(contents, {cellDates:true,dateNF:'dd/mm/yyyy'});` `let worksheet = workbook.Sheets[workbook.SheetNames[0]];` `let jsonData = XLSX.utils.sheet_to_json(worksheet, {header: headers, defval: ''});` and print the `jsonData` and I get: `04/01/2018`

ArturPrzybysz commented

2018-07-05 13:51:19 +00:00

(Migrated from github.com)

Any progress on the topic?

svendeckers commented

2018-07-16 07:30:06 +00:00

(Migrated from github.com)

Not from my side

bean5 commented

2018-09-19 21:01:45 +00:00

(Migrated from github.com)

I am seeing DateTimes in CSV treated differently than those in XLSX files. See also: https://github.com/SheetJS/SheetJS.github.io/issues/14