请教赵老师，关于excel数据写入SQL数据库的问题

feiren228 · 发表于 2015-4-23 08:56

本帖最后由 feiren228 于 2015-4-24 11:28 编辑

关于excel数据写入SQL数据库的问题
8楼代码完美解决！

Sub ado1()
Dim cnn As Object, rs As Object
Dim CnStr As String, sql As String
Set cnn = CreateObject("Adodb.Connection")
Set rs = CreateObject("adodb.recordset")
Dim FileNamw$, DbIp$, DbName$, DbUser$, DbPw$
'以上5个字符串变量分别表示文件路径和文件名、数据库地址、数据库名、数据操作员用户名、操作员密码
'Filename = App.Path & "\'数据库名'"
DbIp = "."
DbName = "test"
DbUser = "test"
DbPw = "test"
'CnStr = "PROVIDER=MSDataShape;Data PROVIDER=MSDASQL;uid=" & DbUser & ";pwd=" & DbPw & ";DRIVER=SQL Server;DATABASE=" & DbName & ";WSID=GQSOFT;SERVER=" & DbIp
CnStr = "Provider=SQLOLEDB;Data Source=" & DbIp & ";DATABASE=" & DbName & ";UID=" & DbUser & ";pwd=" & DbPw
cnn.Open CnStr
sql = "insert into tf_farmer_draft select * from [Excel 8.0;Database=" & ThisWorkbook.FullName & ";].[Sheet1$]"
rs.Open sql, cnn, 1, 3
'Set rs = cnn.Execute(sql)
rs.addnew
rs.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

复制代码

请教赵老师，sql语句到底怎么写，老是出错，谢谢了。
附件：

excel2SQL11.rar (17.38 KB, 下载次数: 17)

feiren228 · 发表于 2015-4-23 09:17

看来要用循环连接value

feiren228 · 发表于 2015-4-23 09:58

本帖最后由 feiren228 于 2015-4-23 09:59 编辑

Sub ado() 'by feiren228
Dim arr, i&, j&, str$
Dim cnn As Object, rs As Object
Dim CnStr As String, sql As String
Set cnn = CreateObject("Adodb.Connection")
Set rs = CreateObject("adodb.recordset")
Dim FileNamw$, DbIp$, DbName$, DbUser$, DbPw$
'以上5个字符串变量分别表示文件路径和文件名、数据库地址、数据库名、数据操作员用户名、操作员密码
'Filename = App.Path & "\'数据库名'"
DbIp = "."
DbName = "cell"
DbUser = "cell"
DbPw = "cell"
'CnStr = "PROVIDER=MSDataShape;Data PROVIDER=MSDASQL;uid=" & DbUser & ";pwd=" & DbPw & ";DRIVER=SQL Server;DATABASE=" & DbName & ";WSID=GQSOFT;SERVER=" & DbIp
CnStr = "Provider=SQLOLEDB;Data Source=" & DbIp & ";DATABASE=" & DbName & ";UID=" & DbUser & ";pwd=" & DbPw
cnn.Open CnStr
arr = Sheet1.UsedRange
For i = 2 To UBound(arr)
str = "values ("
For j = 1 To UBound(arr, 2)
If IsDate(arr(i, j)) Then
str = str & "'" & arr(i, j) & "'" & ","
Else
str = str & arr(i, j) & ","
End If
Next j
str = Left(str, Len(str) - 1) & ")"
sql = "insert into tf_farmer_draft " & str
Debug.Print sql
cnn.Execute sql
Next i
MsgBox "数据添加成功！"
Set rs = Nothing
Set cnn = Nothing
End Sub
'这样还是不行，出错。

复制代码

wcymiss · 发表于 2015-4-23 10:24

试试这句sql：

sql = "insert into tf_farmer_draft select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & ThisWorkbook.FullName & "',[Sheet1$])"

openrowset 是sqlserver访问其他数据库的一个接口。
参考：https://msdn.microsoft.com/zh-cn/library/ms190312.aspx

feiren228 · 发表于 2015-4-23 10:28

这样也不行，不知道到底错在那里，表的字段顺序和数据库一样（直接从数据库导出来的），请老师指点。

Sub ado() 'by feiren228
Dim arr, i&, j&, str$, mycol$
Dim cnn As Object, rs As Object
Dim CnStr As String, sql As String
Set cnn = CreateObject("Adodb.Connection")
Set rs = CreateObject("adodb.recordset")
Dim FileNamw$, DbIp$, DbName$, DbUser$, DbPw$
'以上5个字符串变量分别表示文件路径和文件名、数据库地址、数据库名、数据操作员用户名、操作员密码
'Filename = App.Path & "\'数据库名'"
DbIp = "."
DbName = "cell"
DbUser = "cell"
DbPw = "cell"
'CnStr = "PROVIDER=MSDataShape;Data PROVIDER=MSDASQL;uid=" & DbUser & ";pwd=" & DbPw & ";DRIVER=SQL Server;DATABASE=" & DbName & ";WSID=GQSOFT;SERVER=" & DbIp
CnStr = "Provider=SQLOLEDB;Data Source=" & DbIp & ";DATABASE=" & DbName & ";UID=" & DbUser & ";pwd=" & DbPw
cnn.Open CnStr
arr = Sheet1.UsedRange
For i = 2 To UBound(arr)
str = "values ("
mycol = "("
For j = 1 To UBound(arr, 2)
mycol = mycol & arr(1, j) & ","
str = str & "'" & arr(i, j) & "'" & ","
Next j
mycol = Left(mycol, Len(mycol) - 1) & ") "
str = Left(str, Len(str) - 1) & ")"
sql = "insert into tf_farmer_draft " & mycol & str
Debug.Print sql
cnn.Execute sql
Next i
MsgBox "数据添加成功！"
Set rs = Nothing
Set cnn = Nothing
End Sub

复制代码

feiren228 · 发表于 2015-4-23 10:58

还是谢谢你！

feiren228 · 发表于 2015-4-23 11:16

出现这个提示.

feiren228 · 发表于 2015-4-23 11:30

Sub ado() 'by feiren228
Dim arr, i&, j&, str$, mycol$
Dim cnn As Object, rs As Object
Dim CnStr As String, sql As String
Set cnn = CreateObject("Adodb.Connection")
Set rs = CreateObject("adodb.recordset")
Dim FileNamw$, DbIp$, DbName$, DbUser$, DbPw$
'以上5个字符串变量分别表示文件路径和文件名、数据库地址、数据库名、数据操作员用户名、操作员密码
'Filename = App.Path & "\'数据库名'"
DbIp = "."
DbName = "cell"
DbUser = "cell"
DbPw = "cell"
'CnStr = "PROVIDER=MSDataShape;Data PROVIDER=MSDASQL;uid=" & DbUser & ";pwd=" & DbPw & ";DRIVER=SQL Server;DATABASE=" & DbName & ";WSID=GQSOFT;SERVER=" & DbIp
CnStr = "Provider=SQLOLEDB;Data Source=" & DbIp & ";DATABASE=" & DbName & ";UID=" & DbUser & ";pwd=" & DbPw
cnn.Open CnStr
arr = Sheet1.UsedRange
For i = 2 To UBound(arr)
str = "values ("
mycol = "("
For j = 1 To UBound(arr, 2)
mycol = mycol & arr(1, j) & ","
If Len(arr(i, j)) = 0 Then
str = str & "null" & ","
' ElseIf IsNumeric(arr(i, j)) Then
' str = str & arr(i, j) & ","
Else
str = str & "'" & arr(i, j) & "'" & ","
End If
Next j
mycol = Left(mycol, Len(mycol) - 1) & ") "
str = Left(str, Len(str) - 1) & ")"
sql = "insert into tf_farmer_draft " & mycol & str
Debug.Print sql
cnn.Execute sql
Next i
MsgBox "数据添加成功！"
Set rs = Nothing
Set cnn = Nothing
End Sub

复制代码

feiren228 · 发表于 2015-4-23 11:58

wcymiss 发表于 2015-4-23 10:24
试试这句sql：

sql = "insert into tf_farmer_draft select * from OpenRowSet('Microsoft.Jet.OLEDB.4. ...

谢谢，下午我测试下。

feiren228 · 发表于 2015-4-23 12:02

8楼代码成功。

		自动登录	找回密码
密码			免费注册

[已解决] 请教赵老师，关于excel数据写入SQL数据库的问题

点评

点评