In Chrome for CSV file Date format converts days to months #2704

New Issue

2022-06-06T08:58:17Z

konrazem commented

2022-06-06 08:58:17 +00:00

(Migrated from github.com)

After import of CSV file where columns have values like 31.10.2022 which is format "dd.MM.yyyy" with setting cellDates false or true format on Chrome browser (language set to en or pl version (both tested)) is changed to mm/dd/yyyy sometimes m/d/yy.

It is impossible to detect if the day is a month or a month is a day when values are smaller than 12. There is no workaround for this issue. Note that resolved data in case of Date (cellDates: true) for xlsx file also convert days to months. In CSV files forcing columns to have a specific date format doesn't help.

For Firefox (which uses Geko engine, not V8) keep the good format.

My method to convert File to JSON.

import { read, utils, WorkBook } from 'xlsx';

export const importFromFile = (file: File) =>
  new Promise<never[]>((resolve, reject) => {
    const reader = new FileReader();
    const isBinary = !!reader.readAsBinaryString;

    if (isBinary) { 
     //  CSV is binary !!!
      reader.readAsBinaryString(file);
    } else {
      reader.readAsArrayBuffer(file);
    }

    reader.onabort = () => {
      reject(Error('file reading was aborted'));
    };

    reader.onerror = () => {
      reject(new Error('file reading has failed'));
    };

    reader.onload = () => {
      const binaryStr = reader.result;
      // https://docs.sheetjs.com/docs/api/parse-options/
      const wb: WorkBook = read(binaryStr, {
        type: isBinary ? 'binary' : 'array',
        cellDates: false, // same for true
        dateNF: 'dd"."mm"."yyyy', // same when commented
      });

      const wsname = wb.SheetNames[0];
      const ws = wb.Sheets[wsname];
      const data: never[] = utils.sheet_to_json(ws, {
        raw: false,
        // TODO ?
        // header: 1,
      });

      resolve(data);
    };
  });

You should be able to recreate this issue here: oss.sheetjs.com with CSV with columns of format dd.MM.yyyy

After import of CSV file where columns have values like 31.10.2022 which is format "**dd.MM.yyyy**" with setting cellDates false or true format on Chrome browser (language set to en or pl version (both tested)) is changed to **mm/dd/yyyy** sometimes **m/d/yy**. It is impossible to detect if the day is a month or a month is a day when values are smaller than 12. There is no workaround for this issue. Note that resolved data in case of Date (cellDates: true) for xlsx file also convert days to months. **In CSV files forcing columns to have a specific date format doesn't help.** For Firefox (which uses Geko engine, not V8) keep the good format. My method to convert File to JSON. ``` import { read, utils, WorkBook } from 'xlsx'; export const importFromFile = (file: File) => new Promise<never[]>((resolve, reject) => { const reader = new FileReader(); const isBinary = !!reader.readAsBinaryString; if (isBinary) { // CSV is binary !!! reader.readAsBinaryString(file); } else { reader.readAsArrayBuffer(file); } reader.onabort = () => { reject(Error('file reading was aborted')); }; reader.onerror = () => { reject(new Error('file reading has failed')); }; reader.onload = () => { const binaryStr = reader.result; // https://docs.sheetjs.com/docs/api/parse-options/ const wb: WorkBook = read(binaryStr, { type: isBinary ? 'binary' : 'array', cellDates: false, // same for true dateNF: 'dd"."mm"."yyyy', // same when commented }); const wsname = wb.SheetNames[0]; const ws = wb.Sheets[wsname]; const data: never[] = utils.sheet_to_json(ws, { raw: false, // TODO ? // header: 1, }); resolve(data); }; }); ``` You should be able to recreate this issue here: [oss.sheetjs.com](https://oss.sheetjs.com/) with CSV with columns of format dd.MM.yyyy

😕 1

SheetJSDev commented

2022-06-06 13:50:28 +00:00

(Migrated from github.com)

CSV date parsing eventually passes through the Date constructor, and Chrome's parser is notoriously problematic. Can you run new Date("5.6.2022") in the browser console to determine which interpretation is used by default?

CSV date parsing eventually passes through the Date constructor, and Chrome's parser is notoriously problematic. Can you run `new Date("5.6.2022")` in the browser console to determine which interpretation is used by default?

konrazem commented

2022-06-06 14:21:23 +00:00

(Migrated from github.com)

Output from console

const test = new Date('5.6.2022')
undefined
copy(test)
undefined
navigator.appVersion
'5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.5005.63 Safari/537.36'
test
Fri May 06 2022 00:00:00 GMT+0200 (Central European Summer Time)

When I do copy(test) and paste in notepad it gives me "2022-05-05T22:00:00.000Z".

Output from console ``` const test = new Date('5.6.2022') undefined copy(test) undefined navigator.appVersion '5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.5005.63 Safari/537.36' test Fri May 06 2022 00:00:00 GMT+0200 (Central European Summer Time) ``` When I do copy(test) and paste in notepad it gives me "2022-05-05T22:00:00.000Z".

nirajbisht commented

2022-07-02 18:21:14 +00:00

(Migrated from github.com)

any solution for this issue even though I m facing the same issue while converting the CSV to xlsx date format is getting changes from dd-mm-yyyy to mm/dd/yyyy when downloading the file

attaching the code below if anyone has any suggestions it will be great help

file = XLSX.read(content, { type: 'string', cellText: false, cellDates: true, cellNF: true }); const XLSXFileData = XLSX.write(file, {buffer, bookType:'xlsx;,

any solution for this issue even though I m facing the same issue while converting the CSV to xlsx **date format is getting changes** from **dd-mm-yyyy** to **mm/dd/yyyy** when downloading the file attaching the code below if anyone has any suggestions it will be great help `file = XLSX.read(content, { type: 'string', cellText: false, cellDates: true, cellNF: true }); const XLSXFileData = XLSX.write(file, {buffer, bookType:'xlsx;, `

konrazem commented

2022-08-12 11:20:47 +00:00

(Migrated from github.com)

Sorry to say that but because of this problem we moved to the other library which solved this issue.
https://gitlab.com/catamphetamine/read-excel-file

Sorry to say that but because of this problem we moved to the other library which solved this issue. https://gitlab.com/catamphetamine/read-excel-file

Sign in to join this conversation.