Processing excel files with type integer fails validation about assembler HOT 4 CLOSED

@zelima
I'm not sure which Python library will take care of inferring the types instead of datahub-client. Where is tableschema-py's infer being used in this flow?
If I understand correctly, there's a mismatch between tableschema-js (which infers some values) and tabulator (which reads these values with different types). One of these needs to be patched to resolve the issue. Which one exactly needs to be discussed with the frictionless data team - I suggest we open matching issues in the FD repos (for tabulator and tableschema-js) and also start a discussion in the gitter channel.

from assembler.

@akariv

I'm not sure which Python library will take care of inferring the types instead of datahub-client. Where is tableschema-py's infer being used in this flow?

Nowhere, filed types are forced to be strings. Just thought we could use infer method there, instead of forcing them to be strings.

If I understand correctly, there's a mismatch between tableschema-js (which infers some values) and tabulator

Yes, correct

Which one exactly needs to be discussed with the frictionless data team

I think tablaeschema-js is working fine in this case, as the types are guessed correctly. I've already opened issue for tabulator here frictionlessdata/tabulator-py#230

from assembler.

Ok so diving deeply into this I've got the following:

Excel treats all numbers as floats. In general, it doesn't care whether your_number % 1 == 0.0 is true or not.
https://stackoverflow.com/a/8826770/6056362

Also, similar problem with openpyxl used by tabulator to parse .xlsx data

iter_worksheet casts all numeric values as floats.
https://bitbucket.org/openpyxl/openpyxl/issues/131/incorect-reading-integer-in-use_iterators#comment-10399143

Meaning that any number coming from Excel is float and python parses it as float, So when stream_remote_resources complains can't cast value "1.0" for type "integer" it actually complains correctly.

On the other hand we have XLSX library from NPM used in CLI to infer a schema for later use. XLSX library treats number got from excel as JS number (Number(n)) https://www.npmjs.com/package/xlsx#data-types

Playing around a bit with JS number I've got the following:

let a = Number(1)
let b = Number(1.0)

console.log('Number(1)='+a, '|', 'Number(1.0)='+b)
>>> Number(1)=1 | Number(1.0)=1
console.log(a === b)
>>> true
console.log(a == b)
>>> true

In any case Number(n) returns "number". Meaning when data.js reads Excel workbook used in datahub-client it gets list of JS numbers. That list of rows (and not path to the file or file buffer) is later used to infer schema by tableschema-js, Leaving us with type: number if there is not at least one actual float Eg 1.1

So Think if even the excel file would like this

one,two,three
1.0,2.0,3.0
4.0,5.0,6.0
...

We would infer the same exact schema. (Not sure how CSV parser works when it detects correctly)

As a solution, I suggest forcing type integer to number if data is xls(x) somewhere here https://github.com/datahq/datahub-client/blob/master/lib/utils/datahub.js#L429

from assembler.

FIXED in frictionlessdata/tabulator-py@9675ead

from assembler.