Excel - remove first blank row

Question

I have a really big excel file with first row blank, second row with header and rest rows containing data.

I need to select only part of data from that file.

Because it should be done on client side in javascript I decided to use AlaSql library (alasql.org), where I can precisely select data I need with sql language.

Fe: first I count number of rows than select some of data (with headers) using range:

var sql = "SELECT value COUNT(*) FROM FILE(?,{headers:true})"; //count rows
alasql(sql,[event],function(numberofrows){ 
var sql2 = "select column1, column2 from FILE(?, headers:true,range:'A2:Z"+numberofrowss"'})"; 
//I need only two columns I can use here where/having/limit etc. conditions
alasql(sql2,[event],function(res){
... // other steps
}}

Is there more effective way to do the job (not counting rows first)? Maybe it can be used something similar to range defined like 'A2:ZZ'? Using range A2:A10000000 consumes all memory...

Any suggestions? Other library? Maybe there is a way to remove blank row first?

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Are you tried to import data without range?

I never seen this library before (now thank you) and just used sample file with data like you described and examples from lib docs and got correct results:

alasql('SELECT header1, header2 FROM XLS("https://dl.dropboxusercontent.com/u/802266/a.xls",{headers:true})',[],function(data){
        console.log(data);
    });

result:

0: Object
header1: "aaa1"
header2: "aaa2"
1: Object
header1: "sss1"
header2: "sss2"
2: Object
header1: "xxx1"
header2: "xxx2"

`Working example: http://jsfiddle.net/xdfyxupL/

Also if you still need rows count, you can use ROWNUM

Excel - remove first blank row

1 Answers1