ADO将百万行Excel数据快速上传到MSSQL或MySQL数据库中 仅需几十秒

opiona

DLL插件ExcelToSQL函数示例-20230703.rar (1.12 MB, 下载次数: 540)

2023-7-3 12:50 上传

点击文件名下载附件

   试用期到: 2024-07-01


在ADO中可以使用SQL直接上传数据到SQL Serve   或者MySQL
但是数据多一点(几百上千行) 就会很慢,可能要几十秒
百万行上传慢的会让你怀疑人生

用VB.net写的DLL，实现VBA使用：SqlBulkCopy   和  MySqlBulkLoader功能  
5个字段 上传至腾讯云数据库  十万行 需要3秒, 百万行 需要28秒
其实这还包括从Excel内获取数据的耗时,动作本身更快
DLL内还有其他VBA不擅长的功能: 二维码生成与识别, 发送邮件, FTP上传下载, 文本或文件加密解密等


使用须知:
请自行在首页设置数据库连接信息: IP,数据库名,用户名,密码登
注册DLL 运行此文件即可:  自动注册类库_32_64位_运行2次.bat
各个功能的示例代码见工作表各个按钮的指定宏

MySQL可以导入CSV 上传速度变的很快
但是需要权限  SET GLOBAL local_infile=1；//1表示开启，0表示关闭  




代码示例:

原始帖子此DLL 在400多楼,新开贴在第一楼 方便查找

五指山888

老师厉害，太牛了

沈默00

这个厉害，跟老师学习下

兰色的回忆

北极狐就是牛！

语虚何以言知

最近大佬很生猛啊,不停的搬运好的库到vba这边来,辛苦了

ww200611572

我现在需要把一张表格上传到sql server数据库中，表格有2万行，100列，每次上传要60~70s。大师能帮忙看一下，我的这段代码还有没有提速的空间，我使用了批处理，比逐条处理要快一些，但是感觉还是有点久。

1. Sub 不带表头测试ok()
   
2.     t = Now
   
3.     Const connString As String = "Provider=SQLOLEDB;Server=;user id=sa;password=123456;initial catalog=M_DATA;"
   
4.     tableName = "E_date"
   
5. 
   
6.     Set conn = CreateObject("ADODB.Connection")
   
7.     conn.Open connString
   
8.    
   
9.     AR_ATE = ActiveSheet.UsedRange
   
10.    
    
11.     ATE_Date = Format(Now, "YYYY/MM/DD")
    
12.     ATE_Prog = "234"
    
13.    
    
14.     strSQL = "INSERT INTO " & tableName & " VALUES ('" & ATE_Date & "','" & ATE_Prog & "',"
    
15. 
    
16.     conn.BeginTrans
    
17.    
    
18.     ' 批量插入数据
    
19.     batchSize = 1000 ' 每个批次的大小
    
20.     For i = 2 To UBound(AR_ATE) Step batchSize
    
21.         strSQL_batch = "" ' 用于存储当前批次的 SQL 语句
    
22.         For j = i To i + batchSize - 1
    
23.             If j > UBound(AR_ATE) Then Exit For ' 处理数据不足一个批次的情况
    
24.             strSQL_row = strSQL
    
25.             For k = 1 To UBound(AR_ATE, 2)
    
26.                 If IsError(AR_ATE(j, k)) Then AR_ATE(j, k) = ""
    
27.                 strSQL_row = strSQL_row & "'" & Replace(AR_ATE(j, k), "'", "''") & "', "
    
28.             Next
    
29.             strSQL_row = Left(strSQL_row, Len(strSQL_row) - 2) & ")"
    
30.             strSQL_batch = strSQL_batch & strSQL_row & ";" ' 将每行 SQL 语句添加到批次中
    
31.         Next
    
32.         conn.Execute strSQL_batch ' 执行当前批次的 SQL 语句
    
33.     Next
    
34.     ' 提交事务
    
35.     conn.CommitTrans
    
36.    
    
37.     conn.Close
    
38.     Set conn = Nothing
    
39.     MsgBox Format(Now - t, "hh:mm:ss")
    
40. End Sub

复制代码

opiona

ww200611572 发表于 2023-7-6 13:29
我现在需要把一张表格上传到sql server数据库中，表格有2万行，100列，每次上传要60~70s。大师能帮忙看一下 ...

请下载 我的附件
根据示例 任选一个代码改一下
做一个数组保存到数据库的

jygzcj

10万行excel快速输入到 acess 。

opiona

jygzcj 发表于 2023-8-27 19:51
10万行excel快速输入到 acess 。

一般保存方式  没有SQL之外的快速方法

1.     StrSQL = "INSERT INTO [测试表] "
   
2.     StrSQL = StrSQL & " SELECT   [日期],[订单号],[数量]"
   
3.     StrSQL = StrSQL & " FROM [Excel 12.0;Database=" & ThisWorkbook.FullName & "].[数据表$]"
   
4.     StrSQL = StrSQL & " WHERE NOT [订单号] IS NULL AND LEN([订单号])>0"

复制代码

jygzcj

opiona 发表于 2023-8-27 20:01
一般保存方式  没有SQL之外的快速方法

这个速度好快呀，网上有个select into 的方法不太会用