1. If your header is in the top of the empty rows, how is showed below, you don't need to worry about those empty rows, in the tests that I did here those empty rows do not affected the information.
| Year | Customer | Value |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| 2007 | Customer1 | 2 |
| 2007 | Customer2 | 3 |
| 2007 | Customer3 | 4 |
| 2007 | Customer4 | 5 |
| 2008 | Customer1 | 1 |
| 2008 | Customer2 | 4 |
| 2008 | Customer3 | 3 |
| 2008 | Customer4 | 4 |
2. This situation with lines before the header is more complicated. I didn't found a way to get the header names on the LOAD. What I did is create a "where" to not bring inside the fields the header name.
For example:
| | | |
| | | |
| | | |
| | | |
| | | |
| Year | Customer | Value |
| 2007 | Customer1 | 2 |
| 2007 | Customer2 | 3 |
| 2007 | Customer3 | 4 |
| 2007 | Customer4 | 5 |
| 2008 | Customer1 | 1 |
| 2008 | Customer2 | 4 |
| 2008 | Customer3 | 3 |
| 2008 | Customer4 | 4 |
LOAD A,
B,
C
FROM
[Book1.xlsx]
(ooxml, no labels, table is Sheet1)
where A<>'Year';
Hope it helps.
Gabriel
