sqlldr 批量导入Excel 的变通处理方式

一、使用 VBA 批量将 Excel 另存为 CSV

Sub SaveToCsv() '批量将一个目录下的 excel 另存为 csv

    Dim fDir As String, _
        wB As Workbook, _
        wBName As String, _
        wS As Worksheet, _
        fPath As String, _
        sPath As String, _
        suffix1 As String, _
        suffix2 As String, _
        csvName As String
                

    'fPath = "D:\ExcelFiles\test\"
    'sPath = "D:\ExcelFiles\test\csv\"
 
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show Then fPath = .SelectedItems(1) Else Exit Sub
    End With
    
    If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
    
    sPath = fPath & "转换成csv格式\"

    Dim fol
    fol = Dir(sPath, vbDirectory)
    If fol = "" Then
        MkDir sPath
    End If

    fDir = Dir(fPath)
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Do While (fDir <> "")
    
            suffix1 = LCase(Right(fDir, 4))
            suffix2 = LCase(Right(fDir, 5))
            If suffix1 = ".xls" Or suffix2 = ".xlsx" Then
            
                On Error Resume Next

                Set wB = Workbooks.Open(fPath & fDir)
 
                wBName = LCase(wB.Name)
                wBName = Replace(wBName, suffix2, "")
                wBName = Replace(wBName, suffix1, "")
                
                For Each wS In wB.Sheets
                    csvName = wBName & "_" & wS.Name & ".csv"
                    wS.SaveAs sPath & csvName, xlCSV
                    Debug.Print "完成转换:" & wBName & "_" & wS.Name & ".csv"
                    
                Next wS

                wB.Close False

                Set wB = Nothing

            End If

        fDir = Dir

        On Error GoTo 0
    Loop
    
    MsgBox "批量处理完成"
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

二、使用 BAT 批量获取 CSV 文件名称

@echo off & setlocal EnableDelayedExpansion
for /f "delims=" %%i in ('"dir /a/s/b/on *.xls*"') do (
set file=%%~fi
set file=!file:/=/!
echo  infile '!file!' >> excel_file_names.txt
)

三、使用 Sqlldr 导入 Oracle

创建控制文件 import_data.ctl 

load data
infile 'D:\csv\haikou-1_1 (1).csv' 
infile 'D:\csv\haikou-1_1 (2).csv' 
…… 多个来自转换的 csv
truncate into table SQLLDR_DNB_ZYSJ
fields terminated by "," optionally enclosed by ' ' TRAILING NULLCOLS
(
xh,gdj, gddw, tq, xl, yhbh, yhmc, yddz, yhlb, cjzd, zcbh, jldbh, sblb, sblx, sbmc, ccbh, sjrq, jdry, tyrq, dhpc, tynx, ccrq, sbxh, eddy, eddl, zqd, sccj, cs, mylx, jlfs, jlwz, btl, txdz, jlddz, txmklx, dbbb_cj, mylx_cj, txmklx_cj,
xzsj "sysdate"
)

执行控制文件 import_data.ctl 

sqlldr userid=SCOTT/123456@ORCL control=D:\sqllder_test\import_data.ctl bad=D:\sqllder_test\ldr_dnb4.bad log=D:\sqllder_test\ldr_dnb4.log skip=1 errors=9999 rows=10000 direct=true streamsize=10485760 date_cache=5000