1. 首页
  2. 办公

vbaexcel导入sqlserv

本文主要为您介绍vbaexcel导入sqlserv,内容包括Excel怎样用VBA将数据导入到SQL数据库,用VBA实现从EXCEL读取数据然后插入SQL数据库怎么实现搜狗,OfficeExcel用VBA插入数据到SqlServer2005。sub test()Set cnn = CreateObject("ADODB.Connection") Set rs = Create

vbaexcel导入sqlserv

1. Excel 怎样用VBA将数据导入到SQL数据库

sub test()

Set cnn = CreateObject("ADODB.Connection")

Set rs = CreateObject("Adodb.Recordset")strCn= "Provider=sqloledb;Server=R9HDET7;Database=dbname;Uid=username;Pwd=password"

cnn.Open strCn

SQL = ""

cnn.Open strCn '与数据库建立连接,如果成功,返回连接对象cn

rs.Open SQL , cn '执行strSQL所含的SQL命令,结果保存在rs记录集对象中

cnn.Close

Set cnn = Nothing

end sub请参考

连接数据库后,通过写SQL语句实现增删改查

2. 用VBA 实现 从EXCEL 读取数据 然后插入SQL 数据库 怎么实现

'sql完整例子

Sub testSql()

'定义连接对象

Dim cnn As New ADODB.Connection

Dim rs As New ADODB.Recordset

'定义连接字符串

Dim conStr As String

Dim sqlstr As String

'连接字符串-以下是连接MSSQL数据库

conStr = "Provider=sqloledb; " _

& "Server=192.168.1.121; " _

& "Database=DATABASENAME;Uid=admin;Pwd=admin;"

cnn.Open conStr

sqlstr = "SELECT * from tablename"

rs.Open sqlstr, cnn

Range("a2").CopyFromRecordset rs

rs.Close

cnn.Close

End Sub

3. 用VBA将EXCEL内容一次性导入SQL

用VBA将EXCEL内容一次性导入SQL'工具->引用->Microsoft ActiveX Date Object 2.0Public Sub SaveData()Dim Cnn As ADODB.ConnectionDim SQL As StringSet Cnn = New ADODB.Connection'建立于数据库的链接'这里根据你的实际值修改ConnectionString = "Driver=SQL Server;Server=服务器名称;Database=数据库;Uid=账号;Pwd=密码;"With Cnn.Provider = "SQLOLEDB".ConnectionString = "Driver=SQL Server;Server=mxb\sqlexpress;Database=test;Uid=sa;Pwd=xiaoma;".OpenEnd With'保存数据r = Range("A65534").End(xlUp).RowFor i = 1 To r'拼sqlSQL = "insert into T values('" & Cells(i, 1) & "','" & Cells(i, 2) & "'," & Cells(i, 3) & ")"Cnn.Execute SQLNextCnn.CloseSet Cnn = NothingMsgBox "保存成功"End Sub上面是通过VBA,插入数据到数据库,下面是从SQL查询Excel,然后直接insert into到数据库,也可以用数据库导入向导--查询excel2007select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')。

[Sheet1$]--查询excel2003select * from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="D:\2003.xls";Extended properties=Excel 5.0')。[Sheet1$]。

4. 使用VB将excel导入sql

EXCEL文件要设置固定格式,还要设置命名范围,然后才可以导入

大致思路如下:

'// 设置打开 EXCEL 文件的连接字符串

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=excel文件名;Extended Properties=Excel 8.0"

'// 以记录集的形式打开 Excel 文件,adoConn 为 ADODB.Connection 对象

adoConn.Open strConn

'// 将数据插入到指定的表中(以ODBC的方式打开SQL数据库)

strSQL = "INSERT INTO [odbc;Driver={SQL Server};Server=服务器IP;Database=数据库;UID=用户名;PWD=密码].SQL中的表名 SELECT EXCEL中的字段 FROM EXCEL工作表名"

'// 执行导入语句

adoConn.Execute strSQL, , adExecuteNoRecords

5. VBA execl将数据导入数据库

Sub 把Excel数据插入数据库中()

'*******************************************

'时间:2010-06-28

'作者:bengdeng

'功能:把当前工作表的数据增加到在程序文件同一目录下进销存表数据库中

'注意:要在工具/引用中引用microsoft activex date objects x.x

' 其中x.x为版本号,可能会因为你安装的office的版本不同而不同,本例引用了2.5版

'发布:

'*******************************************

Dim conn As ADODB.Connection

Dim WN As String

Dim TableName As String

Dim sSql As String

Dim tStr As String

'数据库名,请自行修改,路径与当前工作簿在同一目录

WN = "进销存表.mdb"

'数据库的表名与当前工作表名一致

TableName = ActiveSheet.Name

Set conn = New ADODB.Connection

conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;" & _

"Extended Properties=Excel 8.0;" & _

"Data Source=" & ThisWorkbook.Path & "\" & ActiveWorkbook.Name

conn.Open

If conn.State = adStateOpen Then

sSql = "Insert Into [;DataBase=" & ActiveWorkbook.Path & "\" & WN & "]." & TableName & " Select * From [" & ActiveSheet.Name & "$]"

conn.Execute sSql

MsgBox "成功把数据插入到“" & TableName &; "”中!", , ""

conn.Close

End If

Set conn = Nothing

End Sub

6. excel如何导入sql server数据

最近在一个项目中需要用到Excel文件导入数据库的功能,本人很懒,所以到网上搜了一堆方法,但是通过对比,觉得一下三种是比较好用或者不是很常见的方法,希望对大家有所帮助。

方案一: 通过OleDB方式获取Excel文件的数据,然后通过DataSet中转到SQL Server,这种方法的优点是非常的灵活,可以对Excel表中的各个单元格进行用户所需的操作。12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364 openFileDialog = new OpenFileDialog(); openFileDialog.Filter = "Excel files(*.xls)|*.xls"; if(openFileDialog.ShowDialog()==DialogResult.OK) { FileInfo fileInfo = new FileInfo(openFileDialog.FileName); string filePath = fileInfo.FullName; string connExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ filePath + ";Extended Properties=Excel 8.0"; try { OleDbConnection oleDbConnection = new OleDbConnection(connExcel); oleDbConnection.Open(); //获取excel表 DataTable dataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //获取sheet名,其中[0][1]。

[N]: 按名称排列的表单元素 string tableName = dataTable.Rows[0][2].ToString().Trim(); tableName = "["+ tableName.Replace("'","") + "]"; //利用SQL语句从Excel文件里获取数据 //string query = "SELECT classDate,classPlace,classTeacher,classTitle,classID FROM "+ tableName; string query = "SELECT 日期,开课城市,讲师,课程名称,持续时间 FROM "+ tableName; dataSet = new DataSet(); //OleDbCommand oleCommand = new OleDbCommand(query, oleDbConnection); //OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand); OleDbDataAdapter oleAdapter = new OleDbDataAdapter(query,connExcel); oleAdapter.Fill(dataSet,"gch_Class_Info"); //从excel文件获得数据后,插入记录到SQL Server的数据表 DataTable dataTable1 = new DataTable(); SqlDataAdapter sqlDA1 = new SqlDataAdapter(@"SELECT classID, classDate, classPlace, classTeacher, classTitle, durativeDate FROM gch_Class_Info",sqlConnection1); //SqlCommandBuilder sqlCB1 = new SqlCommandBuilder(sqlDA1); sqlDA1.Fill(dataTable1); foreach(DataRow dataRow indataSet.Tables["gch_Class_Info"].Rows) { DataRow dataRow1 = dataTable1.NewRow(); dataRow1["classDate"] = dataRow["日期"]; dataRow1["classPlace"] = dataRow["开课城市"]; dataRow1["classTeacher"] = dataRow["讲师"]; dataRow1["classTitle"] = dataRow["课程名称"]; dataRow1["durativeDate"] = dataRow["持续时间"]; dataTable1.Rows.Add(dataRow1); } Console.WriteLine("新插入 "+ dataTable1.Rows.Count.ToString() + " 条记录"); sqlDA1.Update(dataTable1); oleDbConnection.Close(); } catch(Exception ex) { Console.WriteLine(ex.ToString()); } } 方案二: 直接通过SQL语句执行SQL Server的功能函数将Excel文件转换到SQL Server数据库。12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849 OpenFileDialog openFileDialog = new OpenFileDialog(); openFileDialog.Filter = "Excel files(*.xls)|*.xls"; SqlConnection sqlConnection1 = null; if(openFileDialog.ShowDialog()==DialogResult.OK) { string filePath = openFileDialog.FileName; sqlConnection1 = new SqlConnection(); sqlConnection1.ConnectionString = "server=(local);integrated security=SSPI;initial catalog=Library"; //import excel intoSQL Server 2000 /*string importSQL = "SELECT * into live41 FROM OpenDataSource"+ "('Microsoft.Jet.OLEDB.4.0','Data Source="+ "\""+ "E:\\022n.xls"+ "\""+ "; User ID=;Password=; Extended properties=Excel 5.0')。

[Sheet1$]";*/ //export SQL Server 2000intoexcel string exportSQL = @"EXEC master..xp_cmdshell 'bcp Library.dbo.live41 out "+ filePath + "-c -q -S"+ "\""+ "\""+ " -U"+ "\""+ "\""+ " -P"+ "\""+ "\""+ "\'"; try { sqlConnection1.Open(); //SqlCommand sqlCommand1 = new SqlCommand(); //sqlCommand1.Connection= sqlConnection1; //sqlCommand1.CommandText = importSQL; //sqlCommand1.ExecuteNonQuery(); //MessageBox.Show("import finish!"); SqlCommand sqlCommand2 = new SqlCommand(); sqlCommand2.Connection= sqlConnection1; sqlCommand2.CommandText = exportSQL; sqlCommand2.ExecuteNonQuery(); MessageBox.Show("export finish!"); } catch(Exception ex) { MessageBox.Show(ex.ToString()); } } if(sqlConnection1!=null) { sqlConnection1.Close(); sqlConnection1 = null; } 方案三: 通过到入Exc。

7. VB 把Excel中的内容批量导入SQL数据库

strSQL = "INSERT INTO Family (字段一,字段二,字段三,。) SELECT (字段一,字段二,字段三,。) FROM OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=Yes;database=" & CommonDialog1.FileName & " ;','select * from [Sheet1$] ')"

把数据库中的除了 image 的其他字段都列出来。应该就可以了

8. 如何使用vba 将excel指定行的数据导入到sql2008中

你在EXCEL中增加一个列名为ID,后在VBA中写以下代码,并引用Microsoft ActiveX Data Objects 2.8后执行

Public Sub 写入SQL2008()

Dim cnn As New ADODB.Connection

Dim SQL As String, mydata As String, mytable As String

Dim i%

mydata = "KKKK" '指定要修改的数据库

mytable = "aaaa" '指定数据表

'建立与指定SQL Server数据库的连接

cnn.ConnectionString = "Provider=SQLOLEDB;" _

& "User ID=sa;" _ 'sa改成你SQL账号(通常不用改)

& "Password =123;" _ '123改成你SQL2008密码

& "Data Source=sowin;" _ 'sowin改成你的SQL2008的电脑名,

'如sql2008在网络上,则sowin改成IP地址(如192.168.0.1)

& "Initial Catalog =" & mydata

cnn.Open

'查询全表某些字段的记录

for i = 2 to [a65536].end(xlup).row

SQL = "UPDATE " & mytable & " SET x=" & cells(i,1) & "," & _

"y=" & cells(i,2) & "," & _

"z=" & cells(i,3) & "," & _

"where id=11"

next i

'注意引号里面的空格

'数据库没有更新的数据则用INSERT INTO

'SQL = "INSERT INTO " & mytable & " COLUMNS(x,y,z)" & _

" VALUES('" & CELLS(i,1) & "','" & cells(i,2) & "','" & cells(i,3) &"')"

cnn.Execute(SQL)

cnn.Close

Set cnn = Nothing

End Sub

本文来自投稿,不代表本站立场,如若转载,请注明出处。