将数据从Excel传输到SQL Server
我有一个Excel电子表格，其中包含我需要放入SQL Server数据库的所有数据。 我相当新的ASP.NET，并从来没有必须从Excel导出到SQL Server之前。
我的Excel电子表格看起来像这样
交易标题 - > ArtID - > BusinessName - > AdStyleCode - >地址 - >郊区
在SQL Server中，我创建了一个名为“Listings”的表格，它以这种格式
intListingID - > intCategoryID - > BusinessName - ArtID - > intAdCode - >地址 - >郊区
从Excel导出数据然后将其导入SQLServer 2005的最佳方式是什么？
谢谢...
您可以使用SSIS轻松完成此任务，您可以参考这两个链接获取完整的详细信息。
[编辑]
如果你有Express，那么你可以尝试下面的命令来设置链接服务器并获取数据
EXEC sp_addlinkedserver ExcelData,'Jet 4.0','Microsoft.Jet.OLEDB.4.0','C:MyData.xls', NULL, 'Excel 5.0;'
GO
然后你可以选择数据到你的表格中
INSERT INTO Listings ...
SELECT column1 AS intListingID, <put all columns here> FROM ExcelData...Data
GO
对于其他选项，请检查此链接
我试图通过VBA将数据从Excel导出到SQL服务器，如下所示：
Const myDB As String = "tenant"
Const myServer As String = "MPAADM"
Const myDB As String = "new"
Const myServer As String = "arjun"
Sub ExportTableToSQL()
Dim cn As ADODB.Connection
Dim cnSQL As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Dim sqlTable As String
Dim acell As Range
Dim t1 As Date
Dim t2 As Date
Dim column As String
On Error Resume Next
path = Sheets("Sheet2").Range("B1").Value
Kill path & z_tempaa.xls"
Kill path & "z_tempaa.xls"
On Error GoTo 0
On Error GoTo 10
column = Cells(1, 1).Value
sqlTable = InputBox("Insert SQL table name")
Application.Wait (Now + TimeValue("0:00:2"))
t1 = Now
Debug.Print t1
If sqlTable = "" Then Exit Sub
Application.ScreenUpdating = False
Set acell = ActiveCell
If IsEmpty(ActiveCell) Then
MsgBox "Select a cell inside a table you want to export to SQL"
Exit Sub
End If
ActiveCell.CurrentRegion.Select
Selection.Copy
Call NewWorkbook
Cells(1, 1).Select
ActiveSheet.Paste
Set cn = New ADODB.Connection
Set cnSQL = New ADODB.Connection
With cnSQL
.ConnectionString = "provider=sqloledb;Data Source=" & myServer & ";Initial Catalog=" & myDB & ";Trusted_Connection=Yes"
' .ConnectionString = "provider=sqloledb;Data Source=" & myServer & ";Initial Catalog=" & myDB & ";Uid=jayantuser;Pwd=Input@123"
.Open
End With
ActiveWorkbook.SaveAs path & "z_tempaa.xls"
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & path & "z_tempaa.xls;" & _
"Extended Properties=Excel 12.0"
strSQL = "drop table " & sqlTable
Debug.Print strSQL
On Error Resume Next
cnSQL.Execute strSQL, lngRecsAff, adExecuteNoRecords
Err.Clear
On Error GoTo 10
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
"Server=" & myServer & ";Database=" & myDB & _
";trusted_connection=yes]." & sqlTable & _
" FROM [sheet1$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
cn.Close
Set cn = Nothing
ActiveWorkbook.Close False
On Error Resume Next
Kill path & "z_tempaa.xlx"
On Error GoTo 0
t2 = Now
Debug.Print t2
MsgBox sqlTable & " table was successfully imported into SQL Server" & vbNewLine & "Transfered record number: " & lngRecsAff _
& vbNewLine & "Time:" & Int((t2 - t1) * 86400) & "s"
If MsgBox("Convert data type to bigint?", vbYesNo) = vbYes Then
strSQL = "ALTER TABLE " & sqlTable & " ALTER COLUMN " & column & " bigint"
cnSQL.Execute strSQL, lngRecsAff, adExecuteNoRecords
End If
Application.ScreenUpdating = True
acell.Select
Exit Sub
10: MsgBox Err.Description
End Sub `Sub NewWorkbook()
Application.DefaultSaveFormat = xlOpenXMLWorkbook
Workbooks.Add
End Sub`
`Sub Quit()
Application.Quit
End Sub`