Blank lines in Excel when using Table Wizard

Mon, 14 May 2012 20:56:03 GMT

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.

Blank lines in Excel when using Table Wizard

Oleg_Troyansky — Tue, 15 May 2012 01:24:55 GMT

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

Blank lines in Excel when using Table Wizard

Tue, 15 May 2012 12:54:37 GMT

Thanks Oleg - Don't you work with Phil?

Bill

Re: Blank lines in Excel when using Table Wizard

evan_kurowski — Tue, 02 Sep 2014 17:26:09 GMT

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

Re: Blank lines in Excel when using Table Wizard

simondachstr — Tue, 02 Sep 2014 17:30:06 GMT

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

topic Blank lines in Excel when using Table Wizard in QlikView

Blank lines in Excel when using Table Wizard

Blank lines in Excel when using Table Wizard

Blank lines in Excel when using Table Wizard

Re: Blank lines in Excel when using Table Wizard

Re: Blank lines in Excel when using Table Wizard