合并前几行并将它们提升为标题

Question

我正在使用ODBC将Excel数据提取导入MSQuery，其中的数据如下所示：

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

有时，数据文件可能在顶部包含额外的空行，其中某个起始单元格具有一些文本。

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

现在，下面显示的行是"事实"行：

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 

我想以某种方式删除顶部的空行，将维度行与事实行连接起来以获得一行。然后将此行提升为标题行。例如

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

注意：维度行可能会有所不同，并且它们的名称可能会有所不同 每个数据提取。同样，"事实"行可能会有所不同，其名称也会有所不同 每个数据提取中可能不同。

这是否可以在 SQL 中进行此转换，在 MS 查询中也是如此，以便我得到一个干净的表，如下所示：

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

粗略的轮廓--

' 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