Align RecNo() to spreadsheet rows that encounter empties either in body of data or before first header

[Anonymous]

Hello All,

I have spreadsheets with a varying number of Null rows before encountering the first row containing any sort of data.

When using an ODBC connection, it has been capable of tracking blank entries in the past, so that the RecNo() function can align to the spreadsheet.  But it does not seem to be working when the blank rows precede the first header line.

Any varying number of fully blank rows before the header are still not getting tabulated by the RecNo() function when loading via ODBC.   Is there a way to pick up the amount of Null rows that appear before the header?

[vishsaggi]

Check here might help you?

How to Identify if first row is BLANK

[Anonymous]

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.

[vishsaggi]

Ok. Never tried this before. May be other experts can help here!. Sorry could not be of much help. However, if you find any other solution please do share. Would be interested to know.

[marcus_sommer]

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.

- Marcus

[Anonymous]

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