Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Blank lines in Excel when using Table Wizard

Assume two Excel 2010 workbooks, 1 worksheet each.  Worksheets are identical in terms of data elements and the location of data elements in each worksheet, From both workbooks, I am loading only one data element and it is "JobNumber" contained in cell D40 in each workbook.  The label "JobNumber" is contained in cell D39 in both workbooks.  Both workbooks have other data that I am not loading.

Workbook A:

Rows 5, 10, and 15 are blank

Table wizard tells me

//Load Job Number from Workbook A

LOAD JobNumber

FROM

(ooxml, embedded labels, header is 36 lines, table is Sheet1);

Workbook B:

No rows are blank

Table wizard tells me

//Load Job Number from Workbook A

LOAD JobNumber

FROM

(ooxml, embedded labels, header is 39 lines, table is Sheet1);

From this I conclude that Table Wizard ignores blank lines when counting necessary lines for the header.

My problem is that I have several hundred workbooks in folder "Test" - not just two.  Workbook file names are unique.   I am actually loading 26 data elements from each workbook -- the same  data elements from each workbook.  However, in the context of this example, some workbooks have no blank lines, some have 1, some have 2, some have 15, etc.    I am trying to load all the required data with one load script - e.g. in the context of the above example the load script is:

//Load Job Number from all workbooks

LOAD JobNumber

FROM

(ooxml, embedded labels, header is 36 lines);

The "header is 36 lines" part only works for the workbooks that have three blank lines.

It is not practical for me to manually enter some dummy data in each workbook so all workbooks have no blank rows.

Is there a way to load all the required data, from each of several hundred workbooks, with one load statement - e.g. get QV to count blank rows when interpreting the number of lines in the header?  Maybe on the front end of the load create a new field "EventNumber" and have QV assign a sequential number as data in each (blank and non-blank) row in each workbook?

My QV skills are liminted - any suggestions would be appreciated.

4 Replies

Blank lines in Excel when using Table Wizard

As a work around, I'd suggest loading all lines, with no Header, and use functions Peek() or Previous() to compare the previous line Header to "Job Number" and based on this condition, loading the following line as the job number. I know it's a bit ugly, but maybe it can get you around the problem...

cheers,

Oleg

Not applicable

Blank lines in Excel when using Table Wizard

Thanks Oleg - Don't you work with Phil?

Bill

evan_kurowski
Valued Contributor

Re: Blank lines in Excel when using Table Wizard

Hello Bgulledge,

See the responses in this thread for tabulating blank Excel spreadsheet rows via the ODBC connection
http://community.qlik.com/create-advanced-comment.jspa

Luminary
Luminary

Re: Blank lines in Excel when using Table Wizard

Have a look at Jonathan Poole's post in this thread:

Need to import blank lines from Excel Workbook

Edit: Oh well, that post is from 2012

Community Browser