Sheetjs: Reading from TSV - date (dd-MON-yy) comes through as integer

[psalmody]

Lines in TSV look like (see e-mail for full file) (and yes, this is fake data):

1   Emily   Fisher  [email protected]  Female  161.31.81.163   89.64   15-NOV-2015 29-JAN-2016 89.62
2   Justin  Shaw    [email protected]   Male    20.170.71.46    90.5    21-JUL-2016 28-MAR-2016 41.77

Doing a simple:

  let wb = XLSX.readFile('./local/input/tsv/SHORT_MOCK.txt', {
    FS: '\t',
    cellDates: true
  })
  XLSX.writeFile(wb, 'tsv.SHORT_MOCK.xlsx')

And my excel sheet dates look like:

[SheetJSDev]

@psalmody good catch, and thanks for sending the file! There are actually 2 related errors (both in dates and in the IP addresses), boiling down to inappropriate use of parseFloat:

> var ip = "161.31.81.163", date = "15-NOV-2015";
> Number(ip)
NaN
> parseFloat(ip)
161.31 // <-- this is the value you see in the ip_address column
> Number(date)
NaN
> parseFloat(date)
15 // <-- this is the value you see rather than the date

The isNaN checks don't work as expected with parseFloat, which is easy to fix: the DSV check should be replaced with the equivalent check for the DIF format.

There are also two other items to address:

• the dateNF / cellDates option should be applied for the DSV formats
• we should investigate how to deduce the date format (e.g. "dd-mmm-yyyy" from "15-NOV-2015")

We're going to push a change sometime in the next day or two with this fix as well as a few other CSV-related issues

[psalmody]

Any status on this?

[SheetJSDev]

We're testing 0.10.0 right now :)

[SheetJSDev]

We just pushed 0.10.0. This version addresses most of the points here and you should see reasonable results. However, we are not done here.

The missing feature is preserving the original date format when reading the CSV (detecting that "15-NOV-2015" is actually of the form dd-mmm-yyyy) and we need an eversion of the SSF formatting process. Keep open for now

[psalmody]

So it's somewhat better - now running data outputs a date value, but the cells are formatted as "General" still. Tried this with both DD-MON-YYYY format and YYYY-MM-DD

[SheetJSDev]

I can't reproduce the issue. Using the original text file I tried:

var XLSX = require('xlsx');
var wb = XLSX.readFile('SHORT_MOCK.txt', {cellDates:true});
XLSX.writeFile(wb, 'SHORT_MOCK.txt.xlsx');

Since you said it was fake data, hopefully posting the output isn't an issue: SHORT_MOCK.txt.xlsx

[psalmody]

OK - my only difference was the last line:

XLSX.writeFile(wb, 'SHORT_MOCK.txt.xlsx', {cellDates: true});

[SheetJSDev]

Hmm My reading of the spec suggests that writing a date in XLSX would automatically imply the general date format. I guess not. That will be fixed in the next release