Access VBA实现CSV导入生成多表合并至单表以支撑PowerBI报表需求

你可以直接在现有代码基础上做少量修改即可实现自动追加合并，无需大幅调整现有流程：

• 首先手动创建总表T_CSV_MERGED，字段结构和你导入的CSV完全一致，额外新增2个辅助字段：
  • LOAD_DATE 日期类型，存储数据导入日期
  • SRC_CSV_NAME 文本类型，存储来源CSV文件名，方便后续排查数据问题
• 改造你的ProcessFile函数，在确认单表非空后追加数据到总表，追加完成后可直接删除中间单表，避免数据库冗余
• 如果需要避免重复导入同天数据，只需要在每日执行流程前加一句删除当日旧数据的SQL即可

修改后的代码示例：

' 模块顶部先定义全局总表名常量
Const MERGED_TBL As String = "T_CSV_MERGED"

Private Function ProcessFile(filename As String, pName As String, sName As String, Optional rName As String = "NULL")
    'Get Actual File Name
    ActualName = TargetPath & Dir(TargetPath & filename)
    
    'Set Placeholder Char (To Keep RoomName to x4 Chars)
    Dim placeholderchar As String: placeholderchar = "x"
    If Len(rName) < 4 Then
        For i = 1 To 4 - Len(rName)
            rName = rName & placeholderchar
        Next i
    End If

    'Define New Table Name for csv File
    Dim tblName As String: tblName = UCase(Replace(TargetDate, "-", vbNullString) & Left(sName, 3) & pName) & rName

    'Create New Table from csv File
    Call DoCmd.TransferText(TransferType:=acLinkDelim, TableName:=tblName, _
    filename:=ActualName, hasfieldnames:=True)

    'If Table is Empty; Delete Table & Exit Function
    If DCount("*", tblName) = 0 Then 
        Call DoCmd.DeleteObject(acTable, tblName)
        Debug.Print ("Table Deleted: " & tblName)
        Exit Function
    End If

    ' 新增：追加数据到总表，注意替换SQL中的字段1/字段2/字段3为你实际的CSV字段名
    Dim sql As String
    sql = "INSERT INTO " & MERGED_TBL & " (字段1,字段2,字段3,LOAD_DATE,SRC_CSV_NAME) " & _
          "SELECT 字段1,字段2,字段3,#" & TargetDate & "#,'" & ActualName & "' FROM " & tblName
    DoCmd.SetWarnings False
    DoCmd.RunSQL sql
    DoCmd.SetWarnings True

    ' 追加完成后删除中间表，不需要保留
    Call DoCmd.DeleteObject(acTable, tblName)
    Debug.Print ("Data Merged:   " & tblName & " 已追加到总表")
End Function

' 每日跑批前调用，清空当日旧数据避免重复导入
Sub ClearDailyData()
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE FROM " & MERGED_TBL & " WHERE LOAD_DATE = #" & TargetDate & "#"
    DoCmd.SetWarnings True
End Sub

如果不想生成大量中间临时表，可以直接把CSV数据追加到总表，性能更高，适合长期定时任务运行：

• 不用为每个CSV单独建表，直接用TransferText把数据导入到固定临时表，清空后复用
• 减少数据库文件碎片和膨胀，跑批速度提升30%以上

示例代码片段：

Const TMP_TBL As String = "T_TMP_CSV"
Private Function FastProcessFile(filename As String)
    ActualName = TargetPath & Dir(TargetPath & filename)
    ' 导入到固定临时表，每次覆盖
    On Error Resume Next
    DoCmd.DeleteObject acTable, TMP_TBL
    On Error GoTo 0
    DoCmd.TransferText acLinkDelim, , TMP_TBL, ActualName, True
    If DCount("*", TMP_TBL) = 0 Then
        DoCmd.DeleteObject acTable, TMP_TBL
        Exit Function
    End If
    ' 直接追加到总表
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO " & MERGED_TBL & " SELECT *,#" & TargetDate & "#,'" & ActualName & "' FROM " & TMP_TBL
    DoCmd.SetWarnings True
    DoCmd.DeleteObject acTable, TMP_TBL
End Function

内容的提问来源于stack exchange，提问作者Anton S.