I appreciate the response Vishwarath. If it were a .csv or other text file there'd be a way to get a handle on blank rows by reading as 'Fixed Record', no matter where they reside in the file. (thus I'd be able to count blanks before & after the header).
However, the approach here is that I am collecting information from .xls/.xlsx content of an indeterminate volume, and spreadsheets are deposited in bulk in a repository. The formats of the multiple inputs may have variances and altering the file type, or modifying the raw inputs should be a last resort.
When I use the RecNo() function, both via file wizard & via ODBC, I'm able to detect blank rows that occur AFTER the header row in a spreadsheet, and mark accordingly. The issue is when 1..n blank rows occur BEFORE the first header row, they are collapsed into a single null row in the QV load (*edit: they are undetectable via file import wizard or ODBC) , and this prevents the RecNo() from aligning to the spreadsheet cells.
I think it won't be possible because skipping the empty records before the header is a feature from the odbc-driver and the query really starts with the header-line.
If you really need to synchronize the row-numbers from excel with the recno() from qlikview you will need additionally steps from the outside - maybe by opening the excel with a vbs-macro and checking with them if there are such empty records or not - but do you really need this? What is the aim behind this? For a data-quality check which is rather seldom needed and requires often a manual approach you could just take a offset-number in mind to find your salience.
Hello Marcus, thanks for the response.
Yes, this is from a validation perspective, ideally to navigate between the QV app & the Excel input.
I could definitely see how ignoring blank space prior to the first .xls row containing non-blanks would be preferable (even a feature, perhaps). That way you don't have to repeatedly deal with searching downward to find data sections.
When the .xls is exported to .xml, I see some XML tags which might help measure null space preceding the header: i.e. <Table ss:ExpandedRowCount="2", x:FullRows="1">
But rather than trying to scoop this measurement out through extended automation, for my particular use-case it would be finished much sooner just opening the individual files, deleting the blank rows, and saving.
Once the blanks preceding the header are removed, RecNo() syncs again with the .xls row # (and can even detect blanks in the data area, so long as they appear after the header).
Thanks all for your assistance on this. ~E