无忧启动论坛

标题: vba记录一下几十万行excel数据秒导入oracle数据库 [打印本页]
作者: likeyouli    时间: 2025-8-29 11:33
标题: vba记录一下几十万行excel数据秒导入oracle数据库
本帖最后由 likeyouli 于 2025-12-12 22:36 编辑

说秒导入，有点过分了，估计得需要一两分钟...（oracle数据库中的表需要提前建好）
几十万行、26列excel数据，第一步，首先用vba生成txt文件：
    Sub ceshi1()
letters = "abcdefghijklmnopqrstuvwxyz"
For i = 1 To Len(letters)
        Dim currentLetter As String
        n = Mid(letters, i, 1)
      If i = Len(letters) Then xx = xx & n & 2: Exit For
xx = xx & n & 2 & "&" & """|""" & "&"                                                                                                                    'xx = xx & """""""""" & "&" & n & 2 & "&" & """""""""" & "&" & """|""" & "&"  '生成的单元格内容被双引号包裹，费劲千辛万苦测试出来
Next i
Range("aa2") = xx
End Sub
用的 | 作为分隔符，数据文件内容中不能有 | ，否则导入数据会出现错误，只要用一个数据文件没有的作为分隔符即可。
然后aa2单元格前边内容加=，即可生成公示，=A2&"|"&B2&"|"&C2&"|"&D2&"|"&E2&"|"&F2&"|"&G2&"|"&H2&"|"&I2&"|"&J2&"|"&K2&"|"&L2&"|"&M2&"|"&N2&"|"&O2&"|"&P2&"|"&Q2&"|"&R2&"|"&S2&"|"&T2&"|"&U2&"|"&V2&"|"&W2&"|"&X2&"|"&Y2&"|"&Z2  双击往下自动填充即可，生成的内容复制到utf-8编码的txt文件中。              --------生产的单元格内容被双引号包裹""""&A2&""""&"|"&""""&B2&""""&"|"&""""&C2&""""&"|"&""""&D2&""""&"|"&""""&E2&""""&"|"&""""&F2&""""&"|"&""""&G2&""""&"|"&""""&H2&""""&"|"&""""&I2&""""&"|"&""""&J2&""""&"|"&""""&K2&""""&"|"&""""&L2&""""&"|"&""""&M2&""""&"|"&""""&N2&""""&"|"&""""&O2&""""&"|"&""""&P2&""""&"|"&""""&Q2&""""&"|"&""""&R2&""""&"|"&""""&S2&""""&"|"&""""&T2&""""&"|"&""""&U2&""""&"|"&""""&V2&""""&"|"&""""&W2&""""&"|"&""""&X2&""""&"|"&""""&Y2&""""&"|"&""""Z2&""""
第二步：生成load.ctl文件，文件内容：
OPTIONS (SKIP=0, ERRORS=1000000, DIRECT=TRUE, PARALLEL=TRUE)
LOAD DATA
INFILE '/home/zhuan.txt'
APPEND INTO TABLE nihao
FIELDS TERMINATED BY "|"
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
    ryid CHAR,                          -- ryid varchar2(800)
    社会保障号码 CHAR,                   -- 社会保障号码 varchar2(800)
    姓名 CHAR,                          -- 姓名 varchar2(800)
    人群类别 CHAR,                       -- 人群类别 varchar2(800)
    医院编码 CHAR,                       -- 医院编码 varchar2(800)
    医院名称 CHAR,                       -- 医院名称 varchar2(800)
    住院流水号 CHAR,                     -- 住院流水号 varchar2(800)
    疾病名称 CHAR,                       -- 疾病名称 varchar2(800)
    出院诊断 CHAR,                       -- 出院诊断 varchar2(800)
    医院项目名称 CHAR,                   -- 医院项目名称 varchar2(800)
    单价 CHAR NULLIF 单价=BLANKS,        -- 单价 varchar2(800)
    数量 CHAR NULLIF 数量=BLANKS,        -- 数量 varchar2(800)
    总金额 CHAR NULLIF 总金额=BLANKS,    -- 总金额 varchar2(800)
    费用发生时间 DATE "YYYY-MM-DD",     -- 费用发生时间 date
    全额统筹 CHAR NULLIF 全额统筹=BLANKS, -- 全额统筹 varchar2(800)
    部分统筹 CHAR NULLIF 部分统筹=BLANKS, -- 部分统筹 varchar2(800)
    全额自负 CHAR NULLIF 全额自负=BLANKS, -- 全额自负 varchar2(800)
    部分自负 CHAR NULLIF 部分自负=BLANKS, -- 部分自负 varchar2(800)
    医疗项目编码 CHAR NULLIF 医疗项目编码=BLANKS, -- 医疗项目编码 varchar2(800)
    医疗项目名称 CHAR NULLIF 医疗项目名称=BLANKS, -- 医疗项目名称 varchar2(800)
    规格 CHAR NULLIF 规格=BLANKS,        -- 规格 varchar2(800)
    商品名 CHAR NULLIF 商品名=BLANKS,    -- 商品名 varchar2(800)
    统筹外金额 CHAR NULLIF 统筹外金额=BLANKS, -- 统筹外金额 varchar2(800)
    超过限价金额 CHAR NULLIF 超过限价金额=BLANKS, -- 超过限价金额 varchar2(800)
    剂型名 CHAR NULLIF 剂型名=BLANKS,    -- 剂型名 varchar2(800)
    计量单位 CHAR NULLIF 计量单位=BLANKS  -- 计量单位 varchar2(800)
)

第三步，把这两个文件拷贝到服务器：scp zhuan.txt root@192.168.1.131:/home
scp load.ctl root@192.168.1.131:/home
第四步：[root@192.168.1.131 ~]#sqlldr userid=数据库用户名/密码@orclcdb control=/home/load.ctl log=load.log bad=load.bad

SQL*Loader: Release 19.0.0.0.0 - Production on Fri Aug 29 10:58:36 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 493166.

Table NIHAO:
  493166 Rows successfully loaded.

Check the log file:
  load.log
for more information about the load.

其他相关：
[size=16.002px]检查bad文件（应该是空的）：
cat load.bad
wc -l load.bad  # 应该返回0

检查日志文件（确认无错误）：
cat load.log | grep -i error  # 应该没有输出





load.ctl.txt

2.21 KB, 阅读权限: 75, 下载次数: 1, 下载积分: 无忧币 -2

一键生成3个文件sql、ctl和数据文件最大100列.xlam.txt

41.22 KB, 下载次数: 0, 下载积分: 无忧币 -2

作者: likeyouli    时间: 2025-8-29 11:33
本帖最后由 likeyouli 于 2025-12-12 22:36 编辑

再次改进代码，因为当单元格内为日期时，用公式生成后会自动转为数字，导致日期无法分辨，所以必须用text(lie,"yyyy-mm-dd")这种格式

Sub 一键生成3个文件sql和ctl和数据文件导入oracle数据库_最大100列()

'版本更新：2025.12.12日周五：当excel中仅有1列时，生成的.sql文件也不报错了，但可能ctl和txt文件会报错。

   Dim oShell As Object, psCommand As String, i As Long, filepath3 As String
'''如果有oracle已建成的表有时间列，则将时间列改为： DATE "YYYY-MM-DD" NULLIF 费用发生时间=BLANKS
'''以下当单元格有回车或换行符时使用
''With Application
''        .ScreenUpdating = False
''        .Calculation = xlCalculationManual
''        .EnableEvents = False
''        .StatusBar = "正在去除单元格内回车符和换行符，请稍候..."
''    End With
''    ' 分列处理以避免内存问题
''    For i = 1 To Range("A1").CurrentRegion.Columns.Count
''    On Error GoTo cuowutiaoguo
''        With Columns(i).SpecialCells(xlCellTypeConstants) '当某一列全为公式时会报错，也就时这一列必须有一个单位格为xlCellTypeConstants
''            .Replace What:=Chr(10), Replacement:="", LookAt:=xlPart
''            .Replace What:=Chr(13), Replacement:="", LookAt:=xlPart
''        End With
''cuowutiaoguo:
''        DoEvents ' 防止界面卡死
''    On Error GoTo 0 ' 关闭错误捕获
''    Next i
''    With Application
''        .ScreenUpdating = True
''        .Calculation = xlCalculationAutomatic
''        .EnableEvents = True
''        .StatusBar = False
''    End With
filepath1 = "C:\shengcheng.sql"
filepath2 = "C:\shengcheng.ctl"
filepath3 = "C:\shengcheng.txt"
TableName = InputBox("请输入Oracle表名:", "表名输入", "你的表名")
If Not TableName Like "[a-zA-Z一-龢]*" Then MsgBox "表名不是以字母或汉字开头,请从新开始": Exit Sub

riqilie = InputBox("请输入日期的列名（格式为m;n等字母，用符号分开，必须从a→z顺序，可以是aa、ab）:", "列名输入", "0")
If riqilie = "" Then MsgBox "您已取消,代码将终止。": Exit Sub
  Set regex = CreateObject("VBScript.RegExp")
    With regex
        .Global = True
        .Pattern = "[a-zA-Z]+"  ' 匹配一个或多个数字
    End With
'Set matches = regex.Execute(riqilie)

'For Each riqilie1 In matches
'   If riqilie1 = "x" Then
'    MsgBox 2
'   End If
'Next riqilie1

For i = 1 To 100
      If Cells(1, i + 1) = "" Then colTypes = colTypes & """""""""" & Cells(1, i) & """""""""" & " varchar2(800)": Exit For
    colTypes = colTypes & """""""""" & Cells(1, i) & """""""""" & " varchar2(800)," & vbCrLf
Next i
strText = "set echo on;" & vbCrLf & "CREATE TABLE " & TableName & vbCrLf & "(" & colTypes & ");" & vbCrLf & "commit;" & vbCrLf

    Set oShell = CreateObject("WScript.Shell")
    psCommand = "powershell -Command " & """$bytes = [System.Text.Encoding]::UTF8.GetBytes('" & strText & "'); " & _
                "$stream = [System.IO.File]::Create('" & filepath1 & "'); " & "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"""
    oShell.Run psCommand, 1, True
    Set oShell = Nothing
'以上生成仅有create table的sql文件，无insert into语句。
'以下将日期列增加text公式
riqijia = 0
For i = 1 To 100

     ' On Error Resume Next
     If Not regex.test(riqilie) Then GoTo tiaoguo
     If riqijia = regex.Execute(riqilie).Count Then riqijia = regex.Execute(riqilie).Count - 1
      riqilie1 = regex.Execute(riqilie)(riqijia)

    '  On Error GoTo 0
tiaoguo:
      zimu = GetExcelColumnName(i)
      If Cells(1, i + 1) = "" And UCase(zimu) = UCase(riqilie1) Then
      xx = xx & """'""" & "&" & "text(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """');""": Exit For
      Else
        If Cells(1, i + 1) = "" And i = 1 Then xx = xx & """INSERT INTO """ & " &" & """" & TableName & """" & "&" & """ VALUES(""" & "&" & """'""" & "&" & "CLEAN(" & zimu & 2 & ")&" & """');""": Exit For
        If Cells(1, i + 1) = "" Then xx = xx & """'""" & "&" & "CLEAN(" & zimu & 2 & ")&" & """');""": Exit For

      End If
     If i = 1 Then
        If UCase(zimu) = UCase(riqilie1) Then
         xx = xx & """INSERT INTO """ & " &" & """" & TableName & """" & "&" & """ VALUES(""" & "&" & """'""" & "&" & "text(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """'""" & "&" & """,""" & "&"
          riqijia = riqijia + 1
        Else
      xx = xx & """INSERT INTO """ & " &" & """" & TableName & """" & "&" & """ VALUES(""" & "&" & """'""" & "&" & "CLEAN(" & zimu & 2 & ")&" & """'""" & "&" & """,""" & "&"
        End If
       Else
            If UCase(zimu) = UCase(riqilie1) Then
            xx = xx & """'""" & "&" & "text(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """'""" & "&" & """,""" & "&"
                 riqijia = riqijia + 1
            Else
            xx = xx & """'""" & "&" & "CLEAN(" & zimu & 2 & ")&" & """'""" & "&" & """,""" & "&"
            End If
      End If
Next i
Cells(2, i + 1).NumberFormatLocal = "G/通用格式"
Cells(2, i + 1) = "=" & xx
h = Range("a1").CurrentRegion.Rows.Count
If h < 5000 Then
Cells(2, i + 1).AutoFill Range(Cells(2, i + 1), Cells(h, i + 1))
Else
h = 50
Cells(2, i + 1).AutoFill Range(Cells(2, i + 1), Cells(h, i + 1))
End If
Range(Cells(2, i + 1), Cells(h, i + 1)).Copy
Set oShell = CreateObject("WScript.Shell")
    psCommand = "powershell -Command " & "echo 正粘贴insert into语句，请稍等...; " & _
    "Get-Clipboard -TextFormatType UnicodeText | Out-File -Encoding UTF8 -Append '" & filepath1 & "'"""
oShell.Run psCommand, vbNormalFocus, True
Set oShell = Nothing
'以上为插入insert into语句，用的append，因为开始生成的为utf-8编码，append不改变编码

   Set oShell = CreateObject("WScript.Shell")
psCommand = "powershell -ExecutionPolicy Bypass -Command " & "echo 正往sql文件里和末尾插入commit; " & "$i = 0; " & _
                "$sb = [System.Text.StringBuilder]::new(); " & "Get-Content " & filepath1 & " -Encoding UTF8 | ForEach-Object { " & _
                "    [void]$sb.AppendLine($_); " & "  $i++; " & "    if ($i % 1000 -eq 0) { [void]$sb.AppendLine('commit;') } " & "}; " & _
                "[void]$sb.AppendLine('commit;'); " & "$bytes = [System.Text.Encoding]::UTF8.GetBytes($sb.ToString()); " & _
                "$stream = [System.IO.File]::Create('c:\output.sql'); " & "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"
    oShell.Run psCommand, vbNormalFocus, True
    Set oShell = Nothing
'以上每隔1000行和末尾插入commit；
xx = ""
For i = 1 To 100
      If Cells(1, i + 1) = "" Then xx = xx & """" & Cells(1, i) & """" & " CHAR NULLIF " & """" & Cells(1, i) & """" & "=BLANKS)": Exit For
      xx = xx & """" & Cells(1, i) & """" & " CHAR NULLIF " & """" & Cells(1, i) & """" & "=BLANKS," & vbCrLf
Next i
xx1 = "OPTIONS (SKIP=0, ERRORS=10, DIRECT=TRUE, PARALLEL=TRUE)" & vbCrLf & "LOAD DATA" & vbCrLf & _
      "INFILE '/home/shengcheng.txt'" & vbCrLf & "APPEND INTO TABLE " & TableName & vbCrLf & "FIELDS TERMINATED BY ""|||!|||""" & _
       vbCrLf & "TRAILING NULLCOLS" & vbCrLf & "(" & xx
    ' 将文本放入剪贴板
    With New DataObject
        .SetText xx1
        .PutInClipboard
    End With
   Set oShell = CreateObject("WScript.Shell")
psCommand = "powershell -Command " & """$clipboardContent = Get-Clipboard -Raw; " & _
            "$bytes = [System.Text.Encoding]::UTF8.GetBytes($clipboardContent); " & _
            "$stream = [System.IO.File]::Create('" & filepath2 & "'); " & _
            "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"""
oShell.Run psCommand, 1, True
Set oShell = Nothing
'以上生成的为ctl文件，以下生成数据文件
xx = ""
riqijia = 0
For i = 1 To 100
      zimu = GetExcelColumnName(i)

      If Not regex.test(riqilie) Then GoTo tiaoguo1
     If riqijia = regex.Execute(riqilie).Count Then riqijia = regex.Execute(riqilie).Count - 1
      riqilie1 = regex.Execute(riqilie)(riqijia)

    '  On Error GoTo 0

     '  On Error Resume Next
      riqilie1 = regex.Execute(riqilie)(riqijia)
  '    On Error GoTo 0
tiaoguo1:
      If Cells(1, i + 1) = "" And UCase(zimu) = UCase(riqilie1) Then
       xx = xx & "text(" & zimu & 2 & ",""yyyy-mm-dd"")": Exit For
      Else
       If Cells(1, i + 1) = "" Then xx = xx & zimu & 2: Exit For
      End If
      If UCase(zimu) = UCase(riqilie1) Then
      riqijia = riqijia + 1
      xx = xx & "text(" & zimu & 2 & ",""yyyy-mm-dd"")" & "&" & """|||!|||""" & "&"
      Else
     xx = xx & zimu & 2 & "&" & """|||!|||""" & "&"   '生成的单元格内容被双引号包裹，费劲千辛万苦测试出来
     End If
Next i
Cells(2, i + 1) = "=" & xx

h = Range("a1").CurrentRegion.Rows.Count
Cells(2, i + 1).AutoFill Range(Cells(2, i + 1), Cells(h, i + 1))
Range(Cells(2, i + 1), Cells(h, i + 1)).Copy
Set oShell = CreateObject("WScript.Shell")
psCommand = "powershell -Command " & """echo 正生成ctl对应的大数据文件，数据量较大请稍等...; " & "$clipboardContent = Get-Clipboard -Raw; " & _
            "$bytes = [System.Text.Encoding]::UTF8.GetBytes($clipboardContent); " & _
            "$stream = [System.IO.File]::Create('" & filepath3 & "'); " & _
            "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"""
oShell.Run psCommand, 1, True
Set oShell = Nothing
'CreateObject("WScript.Shell").PopUp "文件已生成到: " & filepath1 & "和" & filepath2 & filepath3, 0, "提示", 64
MsgBox "文件已生成到: " & filepath1 & "和" & filepath2 & filepath3 & "，生成带有insert into的.sql文件行数应该与Excel一致，" & _
"因为用了clean函数；但是生成的用于sqlldr命令的.txt文件没有用clean函数,注意检查行数是否一致！  代码运行完毕！"
'''''以上为生成与ctl配套的数据文件
''
'''以下是利用这个命令,ps中输入(Get-Content 'c:\output.sql').Length会获取到行数
'''psCommand = "powershell -command ""(Get-Content '" & filepath3 & "').Length"""
'''result = CreateObject("WScript.Shell").exec(psCommand).StdOut.ReadAll
'''  Set regex = CreateObject("VBScript.RegExp")
'''    With regex
'''        .Global = True
'''        .Pattern = "\d+"  ' 匹配一个或多个数字
'''    End With
'''    ' Set matches = regex.Execute(result)
'''   result1 = regex.Execute(result)(0).Value * 1 '这里必须注意，如果不*1，则用鼠标指向这个变量的时候会看到有双引号（用msgbox弹出看不到，但影响下边的判定是否相等）
''' result1 = GetFileLineCountDotNet(filepath3) * 1
' result1 = GetFileLineCountSwitch(filepath3) * 1
'If h - 1 <> result1 Then
'    MsgBox "excel单元格内有回车或换行符，请重新操作：" & Chr(13) & "excel有" & h - 1 & "行，而生成的" & filepath3 & "有" & result1 & "行"
'Else
'    MsgBox "恭喜，生成的行数与excle文件的行数相等，均为" & result1 & "行，可放心导入Oracle数据库！！"
'End If
End Sub

Function GetExcelColumnName(colNumber As Long) As String
    Dim dividend As Long
    Dim remainder As Long
    Dim columnName As String
    dividend = colNumber
    columnName = ""
    Do While dividend > 0
        remainder = (dividend - 1) Mod 26
        columnName = Chr(65 + remainder) & columnName ' 65是"A"的ASCII码
        dividend = (dividend - remainder - 1) \ 26
    Loop
    GetExcelColumnName = columnName
End Function


Function GetFileLineCountSwitch(filePath As String) As Long
    On Error GoTo ErrorHandler
    Dim shell As Object, exec As Object, psCommand As String, result As String
    Set shell = CreateObject("WScript.Shell")

    ' 使用 switch 语句，逐行处理，内存效率高
    psCommand = "powershell -command """ & "$count = 0; " & _
                "switch -File '" & filePath & "' { default { $count++ } }; " & _
                "Write-Output $count" & """"
    Set exec = shell.exec(psCommand)
    result = Trim(exec.StdOut.ReadAll)

    If IsNumeric(result) Then
        GetFileLineCountSwitch = CLng(result)
    Else
        GetFileLineCountSwitch = -1
    End If
    Exit Function
ErrorHandler:
    GetFileLineCountSwitch = -1
End Function

Function GetFileLineCountDotNet(filePath As String) As Long
    On Error GoTo ErrorHandler
    Dim shell As Object, exec As Object, psCommand As String, result As String
    Set shell = CreateObject("WScript.Shell")

    ' 使用 .NET StreamReader，逐行读取，内存效率最高
    psCommand = "powershell -command """ & "$count = 0; " & _
                "$reader = New-Object System.IO.StreamReader('" & filePath & "'); " & _
                "while ($reader.ReadLine() -ne $null) { $count++ }; " & _
                "$reader.Close(); " & "$reader.Dispose(); " & "Write-Output $count" & """"

    Set exec = shell.exec(psCommand)
    result = Trim(exec.StdOut.ReadAll)
    If IsNumeric(result) Then
        GetFileLineCountDotNet = CLng(result)
    Else
        GetFileLineCountDotNet = -1
    End If
    Exit Function
ErrorHandler:
    GetFileLineCountDotNet = -1
End Function

Sub 测试耗时时间()
Dim filepath3 As String
filepath3 = "c:\shengcheng12.txt"

' (Get-Content 'c:\shengcheng.txt' | Measure-Object).Count
'$count = 0;switch -File 'c:\output.sql' { default { $count++ } };echo $count

startTime = Timer
result1 = GetFileLineCountSwitch(filepath3)
MsgBox "处理完成！耗时: " & Format(Timer - startTime, "0.00") & " 秒,共有行数:" & result1 & "方法:调用函数的switch"


startTime = Timer
psCommand = "powershell -command ""$count = 0;switch -File '" & filepath3 & "' { default { $count++ } };echo $count"""
result1 = CreateObject("WScript.Shell").exec(psCommand).StdOut.ReadAll
    MsgBox "处理完成！耗时: " & Format(Timer - startTime, "0.00") & " 秒,共有行数:" & result1 & "方法:直接switch"


  startTime = Timer
psCommand = "powershell -command ""(Get-Content '" & filepath3 & "' | Measure-Object).Count"""
result = CreateObject("WScript.Shell").exec(psCommand).StdOut.ReadAll
    MsgBox "处理完成！耗时: " & Format(Timer - startTime, "0.00") & " 秒,共有行数:" & result1 & "方法:Measure-Object"


startTime = Timer
result1 = GetFileLineCountDotNet(filepath3)
MsgBox "处理完成！耗时: " & Format(Timer - startTime, "0.00") & " 秒,共有行数:" & result1

  startTime = Timer
psCommand = "powershell -command ""(Get-Content '" & filepath3 & "').Length"""
result = CreateObject("WScript.Shell").exec(psCommand).StdOut.ReadAll
    MsgBox "处理完成！耗时: " & Format(Timer - startTime, "0.00") & " 秒,共有行数:" & result1

startTime = Timer
psCommand = "powershell -command ""(Get-Content '" & filepath3 & "').Count"""
result = CreateObject("WScript.Shell").exec(psCommand).StdOut.ReadAll
    MsgBox "处理完成！耗时: " & Format(Timer - startTime, "0.00") & " 秒,共有行数:" & result1

End Sub








一键生成3个文件最大100列sql、ctl控制、数据.txt

14.72 KB, 阅读权限: 55, 下载次数: 0, 下载积分: 无忧币 -2

一键生成3个文件最大100列sql、ctl控制、数据.txt

14.72 KB, 下载次数: 0, 下载积分: 无忧币 -2

一键生成3个文件sql、ctl和数据文件最大100列.xlam.txt

41.22 KB, 下载次数: 0, 下载积分: 无忧币 -2

作者: likeyouli    时间: 2025-8-29 11:36
本帖最后由 likeyouli 于 2025-8-29 17:25 编辑

[root@192.168.1.131 ~]#sqlldr userid=用户名/密码@orclcdb control=/home/shengcheng.ctl log=load.log bad=load.bad

SQL*Loader: Release 19.0.0.0.0 - Production on Fri Aug 29 14:46:43 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 493166.

Table NIHAO:
  493166 Rows successfully loaded.

Check the log file:
  load.log
for more information about the load.
作者: lbw2007    时间: 2025-8-29 12:28
本帖最后由 lbw2007 于 2025-8-29 12:30 编辑

打开plsql或sqlplus，用ODBC导入。少走歪魔邪道。
更何况有各种第三方方便的同步工具如DataX，都比刀耕火种要强很多
作者: likeyouli    时间: 2025-8-29 12:57
lbw2007 发表于 2025-8-29 12:28
打开plsql或sqlplus，用ODBC导入。少走歪魔邪道。
更何况有各种第三方方便的同步工具如DataX，都比刀耕火 ...

怎么导入？我不会。好几十Mb的excel文件，你测试过吗？速度快不快？
作者: lbw2007    时间: 2025-8-29 14:39
本帖最后由 lbw2007 于 2025-8-29 14:45 编辑
likeyouli 发表于 2025-8-29 12:57
怎么导入？我不会。好几十Mb的excel文件，你测试过吗？速度快不快？

我可能没法手把手教你。网上有plsql进行ODBC导入的教程，AI应该也会，你可以搜一下。
和服务器性能有关系。每秒1500条应该不困难

仔细看了看，你的操作对权限依赖较强。真实生产环境是很少有机会直连服务器传文件，以及获取root权限的。
作者: lucky323su    时间: 2025-8-30 07:19
谢谢分享。
作者: likeyouli    时间: 2025-9-2 08:23
本帖最后由 likeyouli 于 2025-9-2 08:24 编辑

说明：1，生成的两个文件均为UTF-8编码，测试了多种，只有这种方式可以生成这个编码；
        2，不能在vba中用这种方式写入文件：
Dim oShell As Object, psCommand As String    Set oShell = CreateObject("WScript.Shell")   
psCommand = "powershell -Command " & """$bytes = [System.Text.Encoding]::UTF8.GetBytes('" & strText & "'); " &    "$stream = [System.IO.File]::Create('" & filepath1 & "'); " & "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"""   
oShell.Run psCommand, 1, True   
Set oShell = Nothing
如果包含特殊字符（如引号、换行符等），这些字符在 PowerShell 命令中需要正确转义，特别是当 strText 包含双引号、单引号或美元符号等时，会导致 PowerShell 命令语法错误，所以必须用剪贴板。剪贴板的方式之所以有效，是因为它完全避免了字符串在VBA和PowerShell之间的直接传递和转义问题。
3，想起来再说...

Sub 生成ctl和数据文件导入oracle数据库()
'如果有oracle已建成的表有时间列，则将时间列改为： DATE "YYYY-MM-DD" NULLIF 费用发生时间=BLANKS
filepath1 = "C:\shengcheng.ctl"
filePath = "C:\shengcheng.txt"
For i = 1 To 26
      If Cells(1, i + 1) = "" Then xx = xx & Cells(1, i) & " CHAR NULLIF " & Cells(1, i) & "=BLANKS)": Exit For
      xx = xx & Cells(1, i) & " CHAR NULLIF " & Cells(1, i) & "=BLANKS," & vbCrLf
Next i
xx1 = "OPTIONS (SKIP=0, ERRORS=10, DIRECT=TRUE, PARALLEL=TRUE)" & vbCrLf & "LOAD DATA" & vbCrLf & _
      "INFILE '/home/shengcheng.txt'" & vbCrLf & "APPEND INTO TABLE nihao" & vbCrLf & "FIELDS TERMINATED BY ""|""" & _
       vbCrLf & "OPTIONALLY ENCLOSED BY '""'" & vbCrLf & "TRAILING NULLCOLS" & vbCrLf & "(" & xx
    ' 将文本放入剪贴板
    With New DataObject
        .SetText xx1
        .PutInClipboard
    End With
   Set oShell = CreateObject("WScript.Shell")
psCommand = "powershell -Command " & """$clipboardContent = Get-Clipboard -Raw; " & _
            "$bytes = [System.Text.Encoding]::UTF8.GetBytes($clipboardContent); " & _
            "$stream = [System.IO.File]::Create('" & filepath1 & "'); " & _
            "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"""
oShell.Run psCommand, 1, True
Set oShell = Nothing
xx = ""
letters = "abcdefghijklmnopqrstuvwxyz"
For i = 1 To 26
        n = Mid(letters, i, 1)
      If Cells(1, i + 1) = "" Then xx = xx & """""""""" & "&" & n & 2 & "&" & """""""""": Exit For
     xx = xx & """""""""" & "&" & n & 2 & "&" & """""""""" & "&" & """|""" & "&"  '生成的单元格内容被双引号包裹，费劲千辛万苦测试出来
Next i
Cells(2, i + 1) = "=" & xx
h = 20
'h = Range("a1").CurrentRegion.Rows.Count
Cells(2, i + 1).AutoFill Range(Cells(2, i + 1), Cells(h, i + 1))
Range(Cells(2, i + 1), Cells(h, i)).Copy
Set oShell = CreateObject("WScript.Shell")
psCommand = "powershell -Command " & """$clipboardContent = Get-Clipboard -Raw; " & _
            "$bytes = [System.Text.Encoding]::UTF8.GetBytes($clipboardContent); " & _
            "$stream = [System.IO.File]::Create('" & filePath & "'); " & _
            "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"""
oShell.Run psCommand, 1, True
Set oShell = Nothing
    MsgBox "文件已生成到: " & filePath & "和" & filepath1
End Sub








作者: likeyouli    时间: 2025-9-3 09:34

一键生成三个文件
Sub 一键生成3个文件sql和ctl和数据文件导入oracle数据库()
''如果有oracle已建成的表有时间列，则将时间列改为： DATE "YYYY-MM-DD" NULLIF 费用发生时间=BLANKS
''以下当单元格有回车或换行符时使用
'With Application
'        .ScreenUpdating = False
'        .Calculation = xlCalculationManual
'        .EnableEvents = False
'        .StatusBar = "正在去除单元格内回车符和换行符，请稍候..."
'    End With
'    ' 分列处理以避免内存问题
'    For i = 1 To Range("A1").CurrentRegion.Columns.Count
'        With Columns(i).SpecialCells(xlCellTypeConstants) '当某一列全为公式时会报错，也就时这一列必须有一个单位格为xlCellTypeConstants
'            .Replace What:=Chr(10), Replacement:="", LookAt:=xlPart
'            .Replace What:=Chr(13), Replacement:="", LookAt:=xlPart
'        End With
'        DoEvents ' 防止界面卡死
'    Next i
'    With Application
'        .ScreenUpdating = True
'        .Calculation = xlCalculationAutomatic
'        .EnableEvents = True
'        .StatusBar = False
'    End With
filepath1 = "C:\shengcheng.sql"
filepath2 = "C:\shengcheng.ctl"
filepath3 = "C:\shengcheng.txt"
TableName = InputBox("请输入Oracle表名:", "表名输入", "YOUR_TABLE_NAME")
If Not TableName Like "[a-zA-Z]*" Then MsgBox "表名不是以字母开头,请从新开始": Exit Sub
'因为后边用26个字母生成的公式，所以这里为最大写了26，再多只能手动。
For i = 1 To 26
      If Cells(1, i + 1) = "" Then colTypes = colTypes & Cells(1, i) & " varchar2(800)": Exit For
    colTypes = colTypes & Cells(1, i) & " varchar2(800)," & vbCrLf
Next i
strText = "set echo on;" & vbCrLf & "CREATE TABLE " & TableName & vbCrLf & "(" & colTypes & ");" & vbCrLf & "commit;" & vbCrLf
   Dim oShell As Object, psCommand As String
    Set oShell = CreateObject("WScript.Shell")
    psCommand = "powershell -Command " & """$bytes = [System.Text.Encoding]::UTF8.GetBytes('" & strText & "'); " & _
                "$stream = [System.IO.File]::Create('" & filepath1 & "'); " & "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"""
    oShell.Run psCommand, 1, True
    Set oShell = Nothing
'以上生成create table的sql文件，无insert into语句。

letters = "abcdefghijklmnopqrstuvwxyz"
For i = 1 To 26
      If Cells(1, i + 1) = "" Then xx = xx & """'""" & "&" & Mid(letters, i, 1) & 2 & "&" & """');""": Exit For
     If i = 1 Then
      xx = xx & """INSERT INTO """ & " &" & """" & TableName & """" & "&" & """ VALUES(""" & "&" & """'""" & "&" & Mid(letters, i, 1) & 2 & "&" & """'""" & "&" & """,""" & "&"   '生成的单元格内容被双引号包裹，费劲千辛万苦测试出来
       Else
       xx = xx & """'""" & "&" & Mid(letters, i, 1) & 2 & "&" & """'""" & "&" & """,""" & "&"
      End If
Next i
Cells(2, i + 1) = "=" & xx
h = 5
'h = Range("a1").CurrentRegion.Rows.Count
Cells(2, i + 1).AutoFill Range(Cells(2, i + 1), Cells(h, i + 1))
Range(Cells(2, i + 1), Cells(h, i + 1)).Copy
Set oShell = CreateObject("WScript.Shell")
psCommand = "powershell -Command ""Get-Clipboard -TextFormatType UnicodeText | Out-File -Encoding UTF8 -Append '" & filepath1 & "'"""
oShell.Run psCommand, vbNormalFocus, True
Set oShell = Nothing
'以上为粘贴insert into语句，用的append，因为开始生成的为utf-8编码，append不改变编码
   Set oShell = CreateObject("WScript.Shell")
psCommand = "powershell -ExecutionPolicy Bypass -Command " & "Add-Type -AssemblyName System.Text; " & "$i = 0; " & _
                "$sb = [System.Text.StringBuilder]::new(); " & "Get-Content " & filepath1 & " -Encoding UTF8 | ForEach-Object { " & _
                "    [void]$sb.AppendLine($_); " & "  $i++; " & "    if ($i % 1000 -eq 0) { [void]$sb.AppendLine('commit;') } " & "}; " & _
                "[void]$sb.AppendLine('commit;'); " & "$bytes = [System.Text.Encoding]::UTF8.GetBytes($sb.ToString()); " & _
                "$stream = [System.IO.File]::Create('c:\output.sql'); " & "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"
    oShell.Run psCommand, vbNormalFocus, True
    Set oShell = Nothing
'以上插入commit,大数据量时使用；
xx = ""
For i = 1 To 26
      If Cells(1, i + 1) = "" Then xx = xx & Cells(1, i) & " CHAR NULLIF " & Cells(1, i) & "=BLANKS)": Exit For
      xx = xx & Cells(1, i) & " CHAR NULLIF " & Cells(1, i) & "=BLANKS," & vbCrLf
Next i
xx1 = "OPTIONS (SKIP=0, ERRORS=10, DIRECT=TRUE, PARALLEL=TRUE)" & vbCrLf & "LOAD DATA" & vbCrLf & _
      "INFILE '/home/shengcheng.txt'" & vbCrLf & "APPEND INTO TABLE " & TableName & vbCrLf & "FIELDS TERMINATED BY ""|||""" & _
       vbCrLf & "TRAILING NULLCOLS" & vbCrLf & "(" & xx
    ' 将文本放入剪贴板
    With New DataObject
        .SetText xx1
        .PutInClipboard
    End With
   Set oShell = CreateObject("WScript.Shell")
psCommand = "powershell -Command " & """$clipboardContent = Get-Clipboard -Raw; " & _
            "$bytes = [System.Text.Encoding]::UTF8.GetBytes($clipboardContent); " & _
            "$stream = [System.IO.File]::Create('" & filepath2 & "'); " & _
            "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"""
oShell.Run psCommand, 1, True
Set oShell = Nothing
'以上生成的为ctl文件
xx = ""
letters = "abcdefghijklmnopqrstuvwxyz"
For i = 1 To 26
        n = Mid(letters, i, 1)
      If Cells(1, i + 1) = "" Then xx = xx & n & 2: Exit For
     xx = xx & n & 2 & "&" & """|||""" & "&"   '生成的单元格内容被双引号包裹，费劲千辛万苦测试出来
Next i
Cells(2, i + 1) = "=" & xx
'h = 20
h = Range("a1").CurrentRegion.Rows.Count
Cells(2, i + 1).AutoFill Range(Cells(2, i + 1), Cells(h, i + 1))
Range(Cells(2, i + 1), Cells(h, i)).Copy
Set oShell = CreateObject("WScript.Shell")
psCommand = "powershell -Command " & """$clipboardContent = Get-Clipboard -Raw; " & _
            "$bytes = [System.Text.Encoding]::UTF8.GetBytes($clipboardContent); " & _
            "$stream = [System.IO.File]::Create('" & filepath3 & "'); " & _
            "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"""
oShell.Run psCommand, 1, True
Set oShell = Nothing
    MsgBox "文件已生成到: " & filepath1 & "和" & filepath2 & filepath3
End Sub


作者: likeyouli    时间: 2025-9-5 18:09
Sub 一键生成3个文件sql和ctl和数据文件导入oracle数据库_最大100列()
   Dim oShell As Object, psCommand As String, i As Long, filepath3 As String
'''如果有oracle已建成的表有时间列，则将时间列改为： DATE "YYYY-MM-DD" NULLIF 费用发生时间=BLANKS
'''以下当单元格有回车或换行符时使用
''With Application
''        .ScreenUpdating = False
''        .Calculation = xlCalculationManual
''        .EnableEvents = False
''        .StatusBar = "正在去除单元格内回车符和换行符，请稍候..."
''    End With
''    ' 分列处理以避免内存问题
''    For i = 1 To Range("A1").CurrentRegion.Columns.Count
''    On Error GoTo cuowutiaoguo
''        With Columns(i).SpecialCells(xlCellTypeConstants) '当某一列全为公式时会报错，也就时这一列必须有一个单位格为xlCellTypeConstants
''            .Replace What:=Chr(10), Replacement:="", LookAt:=xlPart
''            .Replace What:=Chr(13), Replacement:="", LookAt:=xlPart
''        End With
''cuowutiaoguo:
''        DoEvents ' 防止界面卡死
''    On Error GoTo 0 ' 关闭错误捕获
''    Next i
''    With Application
''        .ScreenUpdating = True
''        .Calculation = xlCalculationAutomatic
''        .EnableEvents = True
''        .StatusBar = False
''    End With
filepath1 = "C:\shengcheng.sql"
filepath2 = "C:\shengcheng.ctl"
filepath3 = "C:\shengcheng.txt"
TableName = InputBox("请输入Oracle表名:", "表名输入", "你的表名")
If Not TableName Like "[a-zA-Z一-龢]*" Then MsgBox "表名不是以字母或汉字开头,请从新开始": Exit Sub
For i = 1 To 100
      If Cells(1, i + 1) = "" Then colTypes = colTypes & Cells(1, i) & " varchar2(800)": Exit For
    colTypes = colTypes & Cells(1, i) & " varchar2(800)," & vbCrLf
Next i
strText = "set echo on;" & vbCrLf & "CREATE TABLE " & TableName & vbCrLf & "(" & colTypes & ");" & vbCrLf & "commit;" & vbCrLf

    Set oShell = CreateObject("WScript.Shell")
    psCommand = "powershell -Command " & """$bytes = [System.Text.Encoding]::UTF8.GetBytes('" & strText & "'); " & _
                "$stream = [System.IO.File]::Create('" & filepath1 & "'); " & "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"""
    oShell.Run psCommand, 1, True
    Set oShell = Nothing
'以上生成仅有create table的sql文件，无insert into语句。

For i = 1 To 100
      zimu = GetExcelColumnName(i)
      If Cells(1, i + 1) = "" Then xx = xx & """'""" & "&" & zimu & 2 & "&" & """');""": Exit For
     If i = 1 Then
      xx = xx & """INSERT INTO """ & " &" & """" & TableName & """" & "&" & """ VALUES(""" & "&" & """'""" & "&" & zimu & 2 & "&" & """'""" & "&" & """,""" & "&"
       Else
       xx = xx & """'""" & "&" & zimu & 2 & "&" & """'""" & "&" & """,""" & "&"
      End If
Next i
Cells(2, i + 1) = "=" & xx
h = 5
'h = Range("a1").CurrentRegion.Rows.Count
Cells(2, i + 1).AutoFill Range(Cells(2, i + 1), Cells(h, i + 1))
Range(Cells(2, i + 1), Cells(h, i + 1)).Copy
Set oShell = CreateObject("WScript.Shell")
    psCommand = "powershell -Command " & "echo 正粘贴insert into语句，请稍等...; " & _
    "Get-Clipboard -TextFormatType UnicodeText | Out-File -Encoding UTF8 -Append '" & filepath1 & "'"""
oShell.Run psCommand, vbNormalFocus, True
Set oShell = Nothing
'以上为粘贴insert into语句，用的append，因为开始生成的为utf-8编码，append不改变编码
   Set oShell = CreateObject("WScript.Shell")
psCommand = "powershell -ExecutionPolicy Bypass -Command " & "echo 正往sql文件里和末尾插入commit; " & "$i = 0; " & _
                "$sb = [System.Text.StringBuilder]::new(); " & "Get-Content " & filepath1 & " -Encoding UTF8 | ForEach-Object { " & _
                "    [void]$sb.AppendLine($_); " & "  $i++; " & "    if ($i % 1000 -eq 0) { [void]$sb.AppendLine('commit;') } " & "}; " & _
                "[void]$sb.AppendLine('commit;'); " & "$bytes = [System.Text.Encoding]::UTF8.GetBytes($sb.ToString()); " & _
                "$stream = [System.IO.File]::Create('c:\output.sql'); " & "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"
    oShell.Run psCommand, vbNormalFocus, True
    Set oShell = Nothing
'以上每隔1000行和末尾插入commit；
xx = ""
For i = 1 To 100
      If Cells(1, i + 1) = "" Then xx = xx & Cells(1, i) & " CHAR NULLIF " & Cells(1, i) & "=BLANKS)": Exit For
      xx = xx & Cells(1, i) & " CHAR NULLIF " & Cells(1, i) & "=BLANKS," & vbCrLf
Next i
xx1 = "OPTIONS (SKIP=0, ERRORS=10, DIRECT=TRUE, PARALLEL=TRUE)" & vbCrLf & "LOAD DATA" & vbCrLf & _
      "INFILE '/home/shengcheng.txt'" & vbCrLf & "APPEND INTO TABLE " & TableName & vbCrLf & "FIELDS TERMINATED BY ""|||""" & _
       vbCrLf & "TRAILING NULLCOLS" & vbCrLf & "(" & xx
    ' 将文本放入剪贴板
    With New DataObject
        .SetText xx1
        .PutInClipboard
    End With
   Set oShell = CreateObject("WScript.Shell")
psCommand = "powershell -Command " & """$clipboardContent = Get-Clipboard -Raw; " & _
            "$bytes = [System.Text.Encoding]::UTF8.GetBytes($clipboardContent); " & _
            "$stream = [System.IO.File]::Create('" & filepath2 & "'); " & _
            "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"""
oShell.Run psCommand, 1, True
Set oShell = Nothing
'以上生成的为ctl文件，先复制到剪贴板再生成到ctl文件，原因直接写入文件会报错，因为含有特殊符号，必须经剪贴板中转；以下为生成数据文件...
xx = ""
For i = 1 To 100
      zimu = GetExcelColumnName(i)
      If Cells(1, i + 1) = "" Then xx = xx & zimu & 2: Exit For
     xx = xx & zimu & 2 & "&" & """|||""" & "&"   '生成的单元格内容被双引号包裹，费劲千辛万苦测试出来
Next i
Cells(2, i + 1) = "=" & xx
'h = 20
h = Range("a1").CurrentRegion.Rows.Count
Cells(2, i + 1).AutoFill Range(Cells(2, i + 1), Cells(h, i + 1))
Range(Cells(2, i + 1), Cells(h, i + 1)).Copy
Set oShell = CreateObject("WScript.Shell")
psCommand = "powershell -Command " & """echo 正生成ctl对应的大数据文件，数据量较大请稍等...; " & "$clipboardContent = Get-Clipboard -Raw; " & _
            "$bytes = [System.Text.Encoding]::UTF8.GetBytes($clipboardContent); " & _
            "$stream = [System.IO.File]::Create('" & filepath3 & "'); " & _
            "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"""
oShell.Run psCommand, 1, True
Set oShell = Nothing
MsgBox "文件已生成到: " & filepath1 & "和" & filepath2 & filepath3
'''以上为生成与ctl配套的数据文件

''psCommand = "powershell -command ""(Get-Content '" & filepath3 & "').Length"""
''result = CreateObject("WScript.Shell").exec(psCommand).StdOut.ReadAll
''  Set regex = CreateObject("VBScript.RegExp")
''    With regex
''        .Global = True
''        .Pattern = "\d+"  ' 匹配一个或多个数字
''    End With
''    ' Set matches = regex.Execute(result)
''   result1 = regex.Execute(result)(0).Value * 1 '这里必须注意，如果不*1，则用鼠标指向这个变量的时候会看到有双引号（用msgbox弹出看不到，但影响下边的判定是否相等）
' result1 = GetFileLineCountDotNet(filepath3) * 1
  result1 = GetFileLineCountSwitch(filepath3) * 1
If h - 1 <> result1 Then
    MsgBox "excel单元格内有回车或换行符，请重新操作：" & Chr(13) & "excel有" & h - 1 & "行，而生成的" & filepath3 & "有" & result1 & "行"
Else
    MsgBox "恭喜，生成的行数与excle文件的行数相等，均为" & result1 & "行，可放心导入Oracle数据库！！"
End If
End Sub

Function GetExcelColumnName(colNumber As Long) As String
    Dim dividend As Long
    Dim remainder As Long
    Dim columnName As String
    dividend = colNumber
    columnName = ""
    Do While dividend > 0
        remainder = (dividend - 1) Mod 26
        columnName = Chr(65 + remainder) & columnName ' 65是"A"的ASCII码
        dividend = (dividend - remainder - 1) \ 26
    Loop
    GetExcelColumnName = columnName
End Function


Function GetFileLineCountSwitch(filepath As String) As Long
    On Error GoTo ErrorHandler
    Dim shell As Object, exec As Object, psCommand As String, result As String
    Set shell = CreateObject("WScript.Shell")

    ' 使用 switch 语句，逐行处理，内存效率高
    psCommand = "powershell -command """ & "$count = 0; " & _
                "switch -File '" & filepath & "' { default { $count++ } }; " & _
                "Write-Output $count" & """"
    Set exec = shell.exec(psCommand)
    result = Trim(exec.StdOut.ReadAll)

    If IsNumeric(result) Then
        GetFileLineCountSwitch = CLng(result)
    Else
        GetFileLineCountSwitch = -1
    End If
    Exit Function
ErrorHandler:
    GetFileLineCountSwitch = -1
End Function

Function GetFileLineCountDotNet(filepath As String) As Long
    On Error GoTo ErrorHandler
    Dim shell As Object, exec As Object, psCommand As String, result As String
    Set shell = CreateObject("WScript.Shell")

    ' 使用 .NET StreamReader，逐行读取，内存效率最高
    psCommand = "powershell -command """ & "$count = 0; " & _
                "$reader = New-Object System.IO.StreamReader('" & filepath & "'); " & _
                "while ($reader.ReadLine() -ne $null) { $count++ }; " & _
                "$reader.Close(); " & "$reader.Dispose(); " & "Write-Output $count" & """"

    Set exec = shell.exec(psCommand)
    result = Trim(exec.StdOut.ReadAll)
    If IsNumeric(result) Then
        GetFileLineCountDotNet = CLng(result)
    Else
        GetFileLineCountDotNet = -1
    End If
    Exit Function
ErrorHandler:
    GetFileLineCountDotNet = -1
End Function

作者: imbutton    时间: 3 天前
收藏，学习一下



欢迎光临 无忧启动论坛 (http://bbs.wuyou.net/) Powered by Discuz! X3.3