从Excel传输数据到SQL Server

Question

我有一个包含所有需要导入SQL Server数据库的数据的Excel电子表格。我对ASP.NET比较陌生，以前从未将数据从Excel导入到SQL Server。

我的Excel电子表格看起来像这样：

交易标题->ArtID->商家名称->广告样式代码->地址->郊区

在SQL Server中，我创建了一个名为"Listings"的表，格式如下：

intListingID->intCategoryID->商家名称->ArtID->intAdCode->地址->郊区

最好的方法是什么，可以将数据从Excel导出，然后导入到SQLServer 2005中呢？

谢谢...

- Jason

Answer 1

您可以使用SSIS轻松完成此操作，您可以参考这两个链接获取完整的详细信息。

1. 链接1
2. 链接2

[编辑]

如果您使用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

如果需要其他选项，请查看此链接

。涉及到IT技术相关内容。

- Binoj Antony

---

非常感谢Binoj提供的链接。希望这可以解决我的问题。 - Jason

在阅读了这些帖子之后，我是否需要完整版的SQL Server才能实现这个功能？我只有SQL Server Express。 - Jason

Link1和Link2现在已经重定向到相关网站的主页。可能需要链接到archive.org上的页面。 - Nick T

链接1和链接2仍然是无效的链接。 - BrOSs

Answer 2

我尝试通过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_temp\aa.xls"
    Kill path & "\z_temp\aa.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_temp\aa.xls"

    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & path & "\z_temp\aa.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_temp\aa.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`                                                                                             

- arjun marwaha