通过 VBA 将 Excel 中的数据添加到 Mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3104091/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Adding data from Excel to Mysql via VBA
提问by griseldas
UPDATED QUESTION...SEE BELOW
更新的问题......见下文
I have an excel sheet which accesses a MySQL db as a backend....
我有一个 excel 表，它访问 MySQL 数据库作为后端....
I insert new records into MySql the following way, but I am not sure if this is the best way.
我通过以下方式将新记录插入到 MySql 中，但我不确定这是否是最好的方式。
For rowline = 1 To 50
strSQL = myquerystring (INSERT 5 columns per excel row)
rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
Next rowline
Basically the query string goes through each row in excel sheet (from 1 to 50) and the data on certain cells are added to the sql query and then inserted with rs.Open.... (each row has some 5 columns which are inserted as a record)
基本上查询字符串通过excel表中的每一行（从1到50）并将某些单元格上的数据添加到sql查询中，然后用rs.Open插入......（每行有大约5列插入作为记录）
Everything runs well, however I just want to know if there is a faster way (just one INSERT query), inserting all 50 (and 5 columns on each), from row 1 to 50, all at once.
一切运行良好，但是我只想知道是否有更快的方法（只有一个 INSERT 查询），从第 1 行到第 50 行一次性插入所有 50 个（每个 5 列）。
At the moment it is doing 50 individual INSERT queries, so I am trying to reduce that to 1 but I don't know if it is possible.
目前它正在执行 50 个单独的 INSERT 查询，所以我试图将其减少到 1，但我不知道是否可能。
NEW INFORMATION:
新讯息：
Hi, following your advice and links (thanks!) and some Googling, I ended up with the following code... It works fine, HOWEVER to INSERT 100 rows it takes approx 15 seconds....this is far too much. I am hoping I can get some code/ideas on how to Execute the query once, (inserting all 100 rows in one hit). Please note that I am a beginner on this so if you can stir me into some samples on how it should be done it will be much appreciated.
嗨，按照你的建议和链接（谢谢！）和一些谷歌搜索，我最终得到了以下代码......它工作正常，但是插入 100 行需要大约 15 秒......这太多了。我希望我能得到一些关于如何执行一次查询的代码/想法（一次插入所有 100 行）。请注意，我是这方面的初学者，因此如果您能将我搅入一些有关如何完成的示例，将不胜感激。
Public Sub INSERT_to_MySQL()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strSQL As String
app_enable_false
On Error GoTo no_DB_connection_error
resume_after_connecting:
Set cmd = New ADODB.Command
cmd.ActiveConnection = oConn
' LOOP and INSERT the data
' 100 rows take approx 15 seconds to INSERT
For rowcursor= 1 To 100
the_table = "`global`.`filesaved` "
strSQL = "INSERT INTO " & the_table & " (Client_Name, OriginCity, DestinationCity, ValidFrom, ValidTo, Quote_Number, Cost1, Cost2, Cost3) "
strSQL = strSQL & " VALUES ('" & esc(Range("BB" & rowcursor)) & "','" & esc(Range("BC" & rowcursor)) & "','" & esc(Range("BD" & rowcursor)) & "','" & Format(Range("BE" & rowcursor), "yyyy-mm-dd") & "','" & Format(Range("BF" & rowcursor), "yyyy-mm-dd")
strSQL = strSQL & "','" & esc(Range("BH" & rowcursor)) & "','" & esc(Range("BJ" & rowcursor)) & "','" & esc(Range("BK" & rowcursor)) & "','" & esc(Range("BJ" & rowcursor)) & "')"
cmd.CommandText = strSQL
cmd.Execute
Next rowcursor
app_enable_true
Exit Sub
no_DB_connection_error:
ConnectDB
GoTo resume_after_connecting
End Sub
回答by griseldas
Find below a process which works, in case someone else has in future same problem. Might not be the best solution but 100 records are saved all at once in less than a second, which is what I was after. Also there is only one INSERT query done (instead of 100 different ones for each row.
在下面找到一个有效的过程，以防其他人将来遇到同样的问题。可能不是最好的解决方案，但在不到一秒钟的时间内一次保存了 100 条记录，这正是我所追求的。此外，只完成了一个 INSERT 查询（而不是每行 100 个不同的查询。
Thanks to all for your guidance.
感谢大家的指导。
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst_recordset As ADODB.Recordset
Dim strSQL As String
Dim strSQL2 as string
On Error GoTo no_DB_connection_error
resume_after_connecting:
Set cmd = New ADODB.Command
cmd.ActiveConnection = oConn
' LOOP and INSERT the data
lastrow = Range("BB65536").End(xlUp).Row
the_table = "`global`.`filesaved` "
strSQL = "INSERT INTO `global`.`filesaved` " & " (Client_Name, OriginCity, DestCity, ValidFrom, ValidTo, Quote_Number, Cost1, Cost2, Cost3) VALUES "
strSQL2 = ""
For excel_row = 1 To lastrow
strSQL2 = strSQL2 & " ('" & cells(excel_row,1) & "','" & cells(excel_row,2) & "','" & cells(excel_row,3) & "','" & cells(excel_row,4) & "','" & cells(excel_row,5) & "','" & cells(excel_row,6) & "','" & cells(excel_row,7) & "','" & cells(excel_row,8) & "','" & cells(excel_row,9) & "') ,"
next excel_row
strSQL = strSQL & strSQL2
Mid(strSQL, Len(strSQL), 1) = ";" ' gets rid of the last comma
cmd.CommandText = strSQL
cmd.Execute
回答by Ranjit Kumar
This code worked for me
这段代码对我有用
Sub insertdata()
Dim year As String
Set rs = CreateObject("ADODB.Recordset")
Database_Name = Range("b3").Value ' Name of database
User_ID = Range("b4").Value 'id user or username
Password = Range("b5").Value 'Password
Server_Name =Range("b2").Value
' Query for fetching Maximum Date
' LOOP and INSERT the data
lastrow = Range("BB65536").End(xlUp).Row
the_table = "`admin`.`test` "
strSQL = "INSERT INTO `drawing`.`test` " & " (tests,test2) VALUES "
strSQL2 = ""
For excel_row = 1 To 100
strSQL2 = strSQL2 & " ('" & Cells(excel_row, 1) & "','" & Cells(excel_row, 2) & "') ,"
Next excel_row
strSQL = strSQL & strSQL2
Mid(strSQL, Len(strSQL), 1) = ";" '
' ADODB connection
Set Cn = CreateObject("ADODB.Connection") 'NEW STATEMENT
Cn.Open "Driver={MySQL ODBC 5.1 Driver};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"
rs.Open strSQL, Cn, adOpenStatic
Dim myArray()
End Sub
回答by Raj More
If I run a statement and do not expect a reply back, I usually go for the Commandobject in VBA.
如果我运行一个语句并且不期望得到回复，我通常会Command在 VBA 中查找该对象。
回答by Lance Roberts
I would move the open command outside the loop, then use the Execute method to do the inserts in the loop. You don't want to do an open on the database every time.
我会将 open 命令移到循环外，然后使用 Execute 方法在循环中执行插入操作。您不想每次都打开数据库。
Here's a good page of ADO methods.
这是ADO 方法的一个很好的页面。
Here's a question on Batch Inserting(and Updating).
这是一个关于批量插入（和更新）的问题。
EDIT:Looking at Remou's link in his comment, I realized that you probably don't even need the open. Just use the Execute method for the Inserts.
编辑：查看 Remou 在他的评论中的链接，我意识到您可能甚至不需要 open。只需将 Execute 方法用于插入。
