Alasql: xlsx.js 0.8 does not work with AlaSQL

[mathiasrw]

_From: https://github.com/agershun/alasql/issues/650#issuecomment-219303137_

I had the exact same issue as angelok1. I've figured out that using xlsx.js version 0.7.12 instead of 0.8 as what alasql 0.2.6 is requesting as requirement worked for me importing xlsx files. Btw. I am using angular in my code but isolated a test using just alasql.js 0.2.6 and xlsx.js 0.8 and it was giving the same problem. Changing to xlsx.js 0.7.12 fixed the issue. One issue though is bower doesnt pick up version 0.7.12 I had to get it from here https://cdnjs.com/libraries/xlsx/0.7.12

We must find out what has changed in the API

[nbdamian]

I was looking at their repo, and I don't think they even have a tag for 1.7.12...

[mathiasrw]

0.7.12 is located at https://github.com/SheetJS/js-xlsx/tree/f9097d403b44a0165ecb45dd9a798492b462b339

Please use https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.7.12/xlsx.core.min.js

The commitment that broke out interaction with the latest version is this one: https://github.com/SheetJS/js-xlsx/commit/0d38d4c0a016029d83a80b2ad6f7af3fd8752c7d

[nowzig]

I inserted before this line this code

var data = new Uint8Array(data);
var arr = new Array();
for(var i = 0; i != data.length; ++i) arr[i] = String.fromCharCode(data[i]);
data = arr.join("");

and then the error is gone. Very helpful was the second code example in xlsx

[mathiasrw]

@nowzig

Thank you!

[mathiasrw]

Hmm... something is wrong. Had to remove the commit.

I get

Uncaught Error: Unsupported file NaN
at Object.readSync as read

[nowzig]

hm, strange, in my browserify environment it is working. I tested in Chrome and in Firefox. All the xls and xlsx files, I have, are read perfectly...

[mathiasrw]

Im running the test on node... hmmm. Interesting...

[mecorusfc]

Is there any hope of this getting fixed? Right now, in order to make it work, I have to have this in my bower.json:

"js-xlsx": "https://cdnjs.com/libraries/xlsx/0.7.12",
"alasql": "0.2.3"

That's too far back to rely on. It looks like nobody is assigned to it. Is it possible to retry that commit that was later removed but only have that code active for FILE so that it is used for browser based usage? Should we be looking at alternatives to alasql for importing Excel files?

[mecorusfc]

Ohhhh, I see now - there are two major blocks (one for text and the other for binary) that look a lot like each other. I got misaligned on the 0.2.4 release and was looking at the text block by accident.

[mecorusfc]

Sorry about my confusion above about the 15utility.js file. I was looking in the wrong block when comparing between 0.2.3 and 0.2.4. The blocks for loading text and binary files have the same structure so it is easy to get them confused. However, I'm still trying to figure out what busted in 0.2.4 and has stayed busted since then. I continued to look at 15utility.js. I found an issue with this title:

Fix: IE11: Reading Excel File

Issue #397. The change was in the area that handles the Event type of upload.

reader.readAsBinaryString(files[0]);

was changed to...

reader.readAsArrayBuffer(files[0]);

While the description talked about fixing IE11, I didn't see any if statement around it to verify that this code change would only happen when IE11 was used. Since it seems to break other browsers, I'd suggest the possibility of adding some kind of if statement to only make that change for IE11. I'm going to find a machine with IE11 to see if my failing code will work with an up-to-date version of alasql. Note that it works for me on Chrome on 0.2.3 with later versions of js-xlsx so the version of js-xlsx doesn't seem to be an issue as I thought it was before, based on reading above in the thread. I'll report back with more information on whether it works in IE11 but not in Chrome or other browsers.

[mathiasrw]

Would you be able to test if the problem is still there in 3.8? I just released it with #835 merged and the changes might have solved this one too.

Also, xlsx lib is now latest version (0.9.2)

[mecorusfc]

Thanks for giving it a try. When I changed the version in my bower.json to 0.3.8 and did a bower update, I got this error:

bower alasql#0.3.8 ENORESTARGET No version found that was able to satisfy 0.3.8

Should I wait for some time to allow the version to propagate to all the right repos?

Thanks again, Mike

[mathiasrw]

Strange. Both bower and npm has version 0.3.8

https://libraries.io/bower/alasql
https://libraries.io/npm/alasql

Does bower cache the results?

[mecorusfc]

These libraries.io URLs give a 524 timeout error. Is there a direct URL I can use to get to this version?

[mecorusfc]

I tried to go after 0.3.8 directly with a URL the way I was able to do with xlsx but I got a 403. Apparently, there is authorization set up around alasql that isn't on xlsx.

"alasql": "https://cdnjs.cloudflare.com/ajax/libs/alasql/0.3.8"

bower alasql#* EHTTP Status code of 403

[mecorusfc]

Our Nexus was caching something. After invalidating the cache, I'm able to get 0.3.8 but it still depends on xlsx 0.8 instead of 0.9.2. Here is the message from a bower update:

Unable to find a suitable version for js-xlsx, please choose one by typing one of the numbers below:
1) js-xlsx#~0.8 which resolved to v0.8.8 and is required by alasql#0.3.8
2) js-xlsx#0.9.2 which resolved to v0.9.2 and is required by

I'll go ahead and force 0.9.2 to see if it works.

[mecorusfc]

I'm happy to report that version 0.3.8 works for my use case. I'm importing an Excel file using the SELECT * FROM FILE with an event.

[mathiasrw]

Nice work everyone - thanks for all the bugreports, feedback, PRs, and testing!