????? ??????? ???????? ?? Excel ??? ???? SQL

?????? ????????? ?????? ?????????
???? ???????: 321686 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???? | ?? ????

?? ??? ??????

??????

???? ??? ??????? ???? ????? ????? ??????? ?????? ?? ????? ??? Microsoft Excel ??? ????? ?????? Microsoft SQL Server ???????? ?????? ??????.

??? ?? ???????

??????? ?? ??? ??????? ??????? ?????? Excel ????????:
  • ????? ????? ???????? SQL Server (DTS)
  • Microsoft SQL Server 2005 ????? ????? (SSIS)
  • ?????? SQL Server ?????
  • ????????? SQL Server ???????
  • ?????? ?????? ActiveX (ADO) ????? Microsoft OLE DB ? SQL Server
  • ADO ????? Microsoft OLE DB ? Jet 4.0

???????

???? ??????? ??????? ?? ??????? ?????? ??? ???????? ?????? ???????? ?????? ???? ??????? ????????:
  • ???? ???????? ?? Microsoft SQL Server 7.0 ?? Microsoft SQL Server 2000 ?? Microsoft SQL Server 2005
  • Microsoft Visual Basic 6.0 ??????? ADO ???? ?????? Visual Basic
????? ????? ?? ??? ??????? ??? ??? ????? ????????? ???????:
  • ????? ????? ????????
  • ?????? ???????? ?????????? ???????
  • ????? ADO ?? Visual Basic

?????

??????? ????? ?????

?????? SQL ????? ????????? ?? ??? ??????? ??? ????????? "????? ????" ??????? ?????? Excel ??? ???? SQL Server ???? ???????? ???? SELECT...??...?? ???? ??????. ????? ????? ??? ???????? ??? ??????????? ????????? ???????? INSERT INTO...?????...?? ???? ?????? ????? ?????? ???? ???????? ?????? ??????? ??? ?? ???? ?? ????? ????????? ???????? ???.

??????? DTS ?? SSIS

????? ??????? ????? ??????? SQL Server ???????? ????? ????? (DTS) ?? ??????? ???? SQL ?????? ??????? ???????? ?????? Excel ??? ????? ???? SQL. ??? ????? ??????? ?????? ????? ?????? ?? Excel? ???? ?? ????? ???????? Excel ???? ??? ??????? ?????? ??????? ($) ???? ????? ????? (??? ???? ??????? Sheet1$)? ??? ????? ???????? ???? ??? ????? ??????? ???? Excel ?????? ?????.

??????? ???? ?????

?????? ???????????? ????? ????? ???? Excel ????? ????? ?? SQL Server.?????? ??? ??????? ??????? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ? Microsoft":
306397 HOWTO: ??????? Excel ?? SQL Server ?????? ???????? ?????????? ???????
????????? ???????? ??????? ???????? ???????? ?? ???? ????? ??????? ??? ???? ????? Excel "?????????" ??? ???? SQL Server ???? ???? XLImport1:
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
				
????? ???? ????? ????????? ?? ?????? ?????? ?????? ???? ???????? OPENQUERY ??? ???:
SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
    'SELECT * FROM [Customers$]')
				

??????? ????????? ???????

??? ?? ??? ?????? ????? ????? ??? ???? Excel ????? ?????? ????? ??????? ???????? ???? ???? ???????? OPENDATASOURCE ?? ?????? OPENROWSET. ????? ????????? ???????? ??????? ???? ??????? ???????? ?? ???? ??? Excel ??????? ??? ????? ???? SQL ????:
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
				

??????? ADO ? SQLOLEDB

????? ???? ?????? ????? SQL ?? ????? ADO ???????? Microsoft OLE DB ????? SQL (SQLOLEDB)? ????? ??????? ??? ???? ?????? "??????? ????" ?? ??????? ????????? ??????? ???? ???????? ?????? Excel ??? SQL Server.

????? ????? ????????? ???????? Visual Basic 6.0 ?????? ????? ???? ????? ??? ?????? ?????? ActiveX (ADO). ????? ????????? ???????? ??? ???? ???? ????? ??????? OPENDATASOURCE ? OPENROWSET ??? ????? SQLOLEDB.
    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _
        "Initial Catalog=<database>;User ID=<user>;Password=<password>"

    'Import by using OPENDATASOURCE.
    strSQL = "SELECT * INTO XLImport6 FROM " & _
        "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
        "'Data Source=C:\test\xltest.xls;" & _
        "Extended Properties=Excel 8.0')...[Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'Import by using OPENROWSET and object name.
    strSQL = "SELECT * INTO XLImport7 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "[Customers$])"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'Import by using OPENROWSET and SELECT query.
    strSQL = "SELECT * INTO XLImport8 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "'SELECT * FROM [Customers$]')"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    Set cn = Nothing
				

??????? ADO ????? Jet

?????? ??????? ?? ?????? ?????? ADO ???????? ???? SQLOLEDB ??????? ??? ???? ????????? Excel SQL. ????? ???? ??????? ???? OLE DB ? Jet 4.0 ??????? ????? Excel.

???? ????? ????? ?????? Jet ??????? ????? ???????? ???????? ?? ?????? SQL ???????? ???? ???? ??? ????? ??? ????? ????? ??????:
  • [?????? ?????? ?????? ?????? Microsoft Access].[??? ??????]
  • [??? ISAM;????? ??????? ISAM].[??? ??????]
  • [ODBC;????? ????? ODBC].[??? ??????]
?????? ??? ?????? ????? ?????? ?????? ????? ODBC ??? ????? ?????? SQL Server ??????. ????? ??????? ??? ???? ?????? ODBC (DSN) ?? ????? ????? dsn:
DSN:
    [odbc;DSN=<DSN name>;UID=<user>;PWD=<password>]

DSN-less:
   [odbc;Driver={SQL Server};Server=<server>;Database=<database>;
       UID=<user>;PWD=<password>]
				
????? ????? ????????? ???????? Visual Basic 6.0 ?????? ????? ???? ????? ??? ADO. ????? ????????? ???????? ??? ???? ????? ??????? ?????? Excel ??? SQL Server ??? ????? ADO ???????? ???? Jet 4.0.
    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\test\xltestt.xls;" & _
        "Extended Properties=Excel 8.0"
    
    'Import by using Jet Provider.
    strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
        "Server=<server>;Database=<database>;" & _
        "UID=<user>;PWD=<password>].XLImport9 " & _
        "FROM [Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
        
    cn.Close
    Set cn = Nothing
				
????? ???? ??????? ???? ?????? ??? ???? ???? ???? Jet ???????? ?????? Excel ??? ????? ?????? Microsoft Access ???? ?? ????? ???????? ("??? ??????") (ISAM) ????? ?????? ???????? ??????? ?? ????? ?????? ODBC.

??????? ??????? ????????

  • ???? ?? ????? ???????? Excel ???? ??? ??????? ?????? ??????? ($) ???? ????? ????? (??? ???? ??????? Sheet1$) ??? ????? ???????? ???? ???? Excel ?????? ?????.
  • ?? ??? ???????? ???? ??? ????? ?????? ???? Excel ???????? ??? ?????? ????? ?? ??????? ?????? ??? ??????? ??????? ?? ???? SQL Server ?????? ?? ????? ?????.?????? ??? ??????? ?????? ??? ??? ??????? ???????? ???? Jet? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ? Microsoft":
    299484 PRB: ??????? ???????? ??????? ??? ??????? ADOX ???????? ????? ?? ???? Access
  • ????? ???? ???? Jet ?? ???? Excel ????? ??? ?????? ????? ????? ???????? ???? ??? ???????? "????????" ???? Jet ?????? ??? ??? ???????? ?????? ???????.?????? ??? ??????? ?????? ??? ????? ?? ??? ???????? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ? Microsoft":
    194124 PRB: ????? NULL ???????? DAO OpenRecordset ??? Excel

?????

?????? ??? ??????? ?????? ??? ????? ??????? Excel ????? ??????? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ? Microsoft":
257819 ?????: ??????? ADO ?? ?????? Excel ?? Visual Basic ?? VBA
?????? ??? ??????? ?????? ??? ????? ??? ???????? ??? Excel? ???? ??? ????? ???????? ??????? ?????? ?? "????? ??????? ? Microsoft":
295646 HOWTO: ??? ???????? ?? ???? ???????? ADO ??? Excel ???????? ADO
247412 INFO: ?????? ???? ???????? ??? Excel ?? Visual Basic
246335 HOWTO: ??? ???????? ?? ?????? ????? ADO ??? Excel ???????? ??????? ????????
319951 ?????: ??? ???????? ??? Excel ???????? ????? ????? ???????? SQL Server
306125 ?????: ??????? ???????? ?? ???? SQL ??? Microsoft Excel

???????

???? ???????: 321686 - ????? ??? ??????: 28/????? ??????/1434 - ??????: 5.0
????? ???
  • Microsoft Excel 2000 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
????? ??????: 
kbhowtomaster kbjet kbmt KB321686 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????321686

????? ???????

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com