CODEWITHSUNDEEP
javascriptcsvexport-to-excelsheetjs
Paul Z.
Paul Z.

Reputation: 153

SheetJS library treats text as date incorrectly

There is a CSV file, which I try to read and it contains field with value

"Aprobil P 0.1%"

the short example of CSV:

"Country";"Product Family"
"Germany";"Aprobil P 0.1%"

conversion to workbook is the following:

var workbook = XLSX.read(csvData, {
                    type:'string',
                    dateNF: 'D-M-YYYY',
                    cellDates:true,
                    cellText:true,
                    cellNF: false,
                raw:false});

after conversion I save the XLS, where value "Aprobil P 0.1%" is converted to a date 01.04.00

Looking into the worksheet model and getting the certain cell, it contains:

{
      t: 'd',
      v: 'Sat Apr 01 2000 00:00:00 GMT+0300 (Eastern European Summer Time)',
      z:undefined
}

The best way I see at the moment is to set the raw to true and process the values in my own way. Or 2) replace all occurrences of Aprobil to something similar, but looks like I need to to the same trick for all the 12 months...

Is there any other way to cover this case?

Upvotes: 4

Views: 3442

Answers (2)

Benjamín Blanco
Benjamín Blanco

Reputation: 1

I also found that cellDates is rounding some values: Issue

It happens in some scenarios when the raw flag is disabled. As a workaround, I converted another JSON and compared It with the original one, changing the date on the format that I want.

let jsonByPage = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName],{header:"A", raw:false });
    jsonByPage = cleanDates(buf,jsonByPage,sheetName,"YYYY-MM-DD");

Here is the cleanDates method:

cleanDates(buf,oldJSONByPage,sheetName, format){
      //Get the data with dates and parsing the other values like Strings,Integers.
      let newWoorkbook = XLSX.read(buf,{ cellNF:false,cellText:false, cellDates:true});
      let jsonByPage = XLSX.utils.sheet_to_json(newWoorkbook.Sheets[sheetName],{header:"A", raw:true });
      //Loop the sheet
      for(const index of jsonByPage.keys()) {
        let rowWithDate = jsonByPage[index];
        //Iterate the row
        for (const key in rowWithDate)
        {
          let value =rowWithDate[key];
          if (value instanceof Date)
          {
            oldJSONByPage[index][key] = moment(value).format(format);
          }
        };
      }
      return oldJSONByPage;
    }

Best regards.

Upvotes: 0

Charles G
Charles G

Reputation: 1381

Try this:

read options : {cellText:false,cellDates:true}

In your conversion function:

var data = XLSX.utils.sheet_to_json(ws, {header:1, raw:false, dateNF:'D-M-YYYY'});

Upvotes: 1

Related Questions