Merge top few rows and promote them as Headers

Question

I am importing an Excel data extract into MSQuery using ODBC which has data that appears as below:

Col1   Col2   Col3   Col4   Col5   Col6   Col7   Col8   Col9   Col10   Col11
----------------------------------------------------------------------------
null   null   null   null   null   null   null   Units  Units  %Reach %Reach
Mkts   Dept   SCat   Cat    Seg   Brnd   UPC   4 W/E 10/06/17   4 W/E 11/03/17   4 W/E 12/01/17   4 W/E 02/02/17
ABC   Dept1  Cat1   FOOD   VEGG   XWAR   3939493   231.11   883.43   49.13
ABC   Dept1  Cat1   FOOD   VEGG   XWAR   5946942   422.32   222.64   91.84
ABC   Dept1  Cat1   FOOD   VEGG   XWAR   4938843   543.34   null     null
CDE   Dept2  Cat2   BEV    NVEG   SAG    0549403   null     2        null
DEF   Dept3  Cat3   UTL    DARY   MUG    4032850   null     null     null

Sometimes the data files may contain extra null rows on top with some one of the starting cells having some text.

Col1   Col2   Col3   Col4   Col5   Col6   Col7   Col8   Col9   Col10   Col11
----------------------------------------------------------------------------
sumtxt null   null   null   null   null   null   null   null   null    null
null   null   null   null   null   null   null   null   null   null    null
null   null   null   null   null   null   null   Units  Units  %Reach %Reach
Mkts   Dept   SCat   Cat    Seg   Brnd   UPC   4 W/E 10/06/17   4 W/E 11/03/17   4 W/E 12/01/17   4 W/E 02/02/17
ABC   Dept1  Cat1   FOOD   VEGG   XWAR   3939493   231.11   883.43   49.13
ABC   Dept1  Cat1   FOOD   VEGG   XWAR   5946942   422.32   222.64   91.84
ABC   Dept1  Cat1   FOOD   VEGG   XWAR   4938843   543.34   null     null
CDE   Dept2  Cat2   BEV    NVEG   SAG    0549403   null     2        null
DEF   Dept3  Cat3   UTL    DARY   MUG    4032850   null     null     null

Now, the row that is shown below is the Facts row:

null   null   null   null   null   null   null   Units  Units  %Reach %Reach

And the row that is below it is the Dimensions row:

Mkts   Dept   SCat   Cat    Seg   Brnd   UPC   4 W/E 10/06/17   4 W/E 

I want to somehow delete the top null rows, concatenate the Dimension rows with the Fact rows to get a single row. Then promote this row as the Header row. e.g.

Mkts   Dept   SCat   Cat    Seg   Brnd   UPC   Units~4 W/E 10/06/17   Units~4 W/E 11/03/17    %Reach~4 W/E 12/01/17   %Reach~4 W/E 02/02/17

Note: The Dimension row may vary and their names may be different in each data extract. Similarly, the Facts row may vary and their names may be different in each data extract.

Is this possible to do this transform in SQL, that too in MS Query, so that i get a clean table like this:

Mkts   Dept   SCat   Cat    Seg   Brnd   UPC   Units~4 W/E 10/06/17   Units~4 W/E 11/03/17    %Reach~4 W/E 12/01/17   %Reach~4 W/E 02/02/17
----------------------------------------------------------------------------
ABC   Dept1  Cat1   FOOD   VEGG   XWAR   3939493   231.11   883.43   49.13
ABC   Dept1  Cat1   FOOD   VEGG   XWAR   5946942   422.32   222.64   91.84
ABC   Dept1  Cat1   FOOD   VEGG   XWAR   4938843   543.34   null     null
CDE   Dept2  Cat2   BEV    NVEG   SAG    0549403   null     2        null
DEF   Dept3  Cat3   UTL    DARY   MUG    4032850   null     null     null

Answer 1

Rough outline--

' FindFolder that has the XLS files to import

' myFile = Dir *.xls

' Do While myFile <> ""

   ' Open the xls file

   ' if sheetName = "Fixed" then delete that sheet ' we will recreate it

   ' Select sheetName to import

   ' Activate that sheet

   ' Find Facts row and put values into one-based array FactsRow()
   ' Find Dimensions row and put values into one-based array DimenRow()
   ' Save row# of Dimensions row

   ' If ColHeaders ok (no Facts or Dimen rows), then 
      ' MSQuery import from Existing sheet to MSAccess
      ' jump to Dir stmt
   ' endif

   ' Create new sheet, and columns using FactsRow and DimenRow, per the following--
   ' https://stackoverflow.com/questions/49832151/how-to-create-a-new-sheet-table-in-an-xlsx-file-using-ado-in-excel-vba

   ' copy DataRows from DimenRowNum+1 thru end to Fixed sheet

   ' Save and close this XLS

   ' do MSQuery to import from Fixed sheet to MSAccess

   ' myFile = Dir  ' get filename of next xls file
' Loop  ' until all xls files processed