Issue with reading dates

[agershun]

Hi Every one,
I have facing a problem in read dates. I am using the following query to read data from xls but all the date column of xls data automatically converted into integer why occur this problem? how to fix this problem?

alasql query:

alasql('SELECT * FROM FILE(?,{headers:true})', [event], function (res) { });

01/02/2015 this format is converted into 42006(int) Why? please help me

[mathiasrw]

Excel stores dates as numbers - check out http://www.cpearson.com/excel/datetime.htm

I cant figure out if its up to alasql to convert this to a date. It probably is - but then we get the whole issue of formatting dates.

You can try to mark the colmn and format cells as text

[lganet]

I'm with this problem too.

[mathiasrw]

Hi @lganet

Sorry to hear that you are having same issues still not saved.

If you find any way to sort out the data from excel please share your inputs here.

[UdayangaSeram]

Hi mathiasrw and lganet

This solution for it. Used this code to convert date.
`

 <script type="text/javascript">
    
    alasql('SELECT * FROM FILE(?,{headers:true})', [event], function (data) {
        $.each(data, function (index, value) {
            if (value.Tag != undefined) {
                alert(ExcelDateToJSDate(value.Date));
            }
        });

    });

    function ExcelDateToJSDate(serial) {
        var utc_days = Math.floor(serial - 25569);
        var utc_value = utc_days * 86400;
        var date_info = new Date(utc_value * 1000);

        var fractional_day = serial - Math.floor(serial) + 0.0000001;

        var total_seconds = Math.floor(86400 * fractional_day);

        var seconds = total_seconds % 60;

        total_seconds -= seconds;

        var hours = Math.floor(total_seconds / (60 * 60));
        var minutes = Math.floor(total_seconds / 60) % 60;

        return new Date(date_info.getFullYear(), date_info.getMonth(), date_info.getDate(), hours, minutes, seconds);
    };
    </script>`

[mathiasrw]

Awesome! Thank you so much.

I will see if we can get this into the lib some way

[caesarsol]

Hi, just adding this way of calculating which seems simpler:

function sheetDateToJSDate(n) {
  const d = new Date(1899, 11, 30)
  d.setDate(d.getDate() + n)
  // Rounds milliseconds to seconds
  d.setSeconds(d.getSeconds() + Math.round(d.getMilliseconds() / 1000))
  d.setMilliseconds(0)
  return d
}

[RGKrish183]

Hi,,

I have also get same issue, above solution gives approximate datetime values only.

ACTUAL DATETIME VALUE IN EXCEL 01-10-2017 10:07

@UdayangaSeram solutions gives 43009.4222222222 ' : ' 2017-10-01T04:38:00.000Z

@caesarsol solutions gives 43009.4222222222 ' : ' 2017-10-01T04:38:00.000Z

After some googled i found the solutions use this npm package to solve

npm i excel-date-to-js

> const { getJsDateFromExcel } = require('excel-date-to-js');
> getJsDateFromExcel(nclmn_item.D)
> // 43009.4222222222 ' : ' 2017-10-01T10:07:59.999Z

Credits goes to :

1. christopherscott,
2. oleg-koval

Thanks,
Gopal R.

[SamanthaAdrichem]

xlsx library actually supports this already by setting 'cellDates: true' in the read function. (it becomes a date object)

try

let reader = new FileReader();
reader.onload = function () {
    let workbook: WorkBook = XLSX.read(reader.result, {type: 'binary'});
    console.log(workbook);
};
reader.readAsBinaryString(file);

(Sheets -> Sheet1 -> A1: {t: "n", v: 43436, w: "12/2/18"})

vs

let reader = new FileReader();
reader.onload = function () {
    let workbook: WorkBook = XLSX.read(reader.result, {type: 'binary', cellDates: true});
    console.log(workbook);
};
reader.readAsBinaryString(file);

(Sheets -> Sheet1 -> A1: {t: "d", v: Sat Dec 01 2018 23:59:28 GMT+0100 (Midden-Europese standaardtijd), w: "12/2/18"})

Is there any way to pass config to the xlsx library via alasql?

[mathiasrw]

Is there any way to pass config to the xlsx library via alasql?

Hmmm - it should already just pass the config array to xlsx... If anyone can look into the src folder that would be awesome (I'm at vacation at the moment)

[barakplasma]

@SamanthaAdrichem @mathiasrw I think we can pass the cellDates:true option in these lines:

alasql/src/84from.js

Line 387 in b5c449d

var workbook = X.read(data, {type: 'binary'});

alasql/src/833xlsx.js

Line 46 in b5c449d

wb = XLSX.read(data, {type: 'binary'});

My question is should this change be behind a feature flag or alasql config option (to avoid a breaking change for people who worked around this issue)?

EDIT: think I found a better way in #1481 ; let the user provide options for XLSX.read themselves.
EDIT-2: My PR below resolves this issue

[mathiasrw]

Thanks @barakplasma

Released as part of https://github.com/AlaSQL/alasql/releases/tag/v2.0.1

AlaSQL will now default to read dates as JS objects (cellDates = true). Read more on: https://github.com/AlaSQL/alasql/wiki/XLSX at the bottom.