Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
konrazem opened this issue Jun 6, 2022 · 5 comments
Open

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

konrazem opened this issue Jun 6, 2022 · 5 comments

Comments

@konrazem
Copy link

konrazem commented Jun 6, 2022

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

@SheetJSDev
Copy link
Contributor

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
Copy link
Author

konrazem commented Jun 6, 2022

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
Copy link

nirajbisht commented Jul 2, 2022

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
Copy link
Author

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

@yuniit
Copy link

yuniit commented Oct 7, 2024

Hi, is there any update on this issue?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants