spark sql合并小文件_SQL递归多级文件夹多工作薄合并

年终了，大家肯定有很多数据需要处理，然后还要各种收集合并分析。

财务狗到年终，尤其焦躁，在集团的小伙伴你们还好吗？

电商的老铁，几千份明细数据清洗好了吗？分析出来了吗？

NO…………

不用急，今天我们就来分享小编前几日写的利用SQL，无基础实现多工作薄合并 ……

一招搞定合并，关键是小编的代码是支持多级子文件夹合并的额……

已启用递归方式处理，多少级理论都可以……

代码截图如下：

'作者：Excel办公实战-小易
'时间：2019-1-13
'功能：SQL多工作薄合并
'*************************************************************
Public ArrPth()   '文件全路径
Public Fso As Object
Public n  As Integer '记录文件数
Sub getSQL()
    Call allPath
    Dim wb As Workbook, arr(), k As Integer
    Application.ScreenUpdating = False
    For i = 0 To UBound(ArrPth)
        Set wb = Workbooks.Open(ArrPth(i), False, True)
        For j = 1 To wb.Sheets.Count
            k = k + 1
            ReDim Preserve arr(1 To k)
            arr(k) = "select * from " & "[" & ArrPth(i) & "]." & _
            "[" & wb.Sheets(j).Name & "$]"
        Next
        wb.Close 0
    Next
    sql_str = Join(arr, " union all " & Chr(10))
    Debug.Print sql_str
    Open ThisWorkbook.path & "SQL.txt" For Output As #1
    Print #1, sql_str
    Close #1
    Application.ScreenUpdating = True
    MsgBox "OK!"
End Sub


'选择文件夹
Function SelPath_Dialog()
    Dim Mypath As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show Then Mypath = .SelectedItems(1) Else Exit Function
    End With
    Mypath = Mypath & IIf(Right(Mypath, 1) = "", "", "")
    SelPath_Dialog = Mypath
End Function
'收集Excel地址
Sub allPath()
    Dim sPath As String
    Erase ArrPth
    n = 0
    sPath = SelPath_Dialog()
    If sPath = "" Then End
    Set Fso = CreateObject("scripting.filesystemobject")
    
    '是否处理子文件夹
    bfd = MsgBox("是否包括子文件夹", vbYesNo, "TS")
    If bfd = vbNo Then
        Set fd = Fso.getfolder(sPath)
        For Each file In fd.Files
            If UCase(Fso.GetExtensionName(file.path)) Like "XLS?" Then
                ReDim Preserve ArrPth(n)
                ArrPth(n) = file.path
                n = n + 1
            End If
        Next
    Else
        Call getFileList(sPath, bfd)
    End If
    
End Sub

'递归获取全路径
Sub getFileList(ByVal pth As String, ByVal bSubF As Boolean)
    Set Files = Fso.getfolder(pth).Files
    For Each file In Files
        If UCase(Fso.GetExtensionName(file.path)) Like "XLS?" Then
            ReDim Preserve ArrPth(n)
            ArrPth(n) = file.path
            n = n + 1
        End If
    Next
    Set subfds = Fso.getfolder(pth).SubFolders
    For Each sb In subfds
        Call getFileList(sb.path, True)
    Next
End Sub

---

VBA的使用方式，如有不会自行百度！

产生的SQL如何使用呢？

首先，数据选项卡-现有连接-更多-选择要合并的其中任意一个工作薄，选择导入，数据源右击-编辑查询-粘贴生成的SQL，代码即可！！

更多精彩，欢迎关注小编的个人公众号：Exxcel办公实战