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.
.png "Luminary Alumni"){kind=icon}