Cch nh?p d? li?u t? Excel vo SQL Server

D?ch tiu ? D?ch tiu ?
ID c?a bi: 321686 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

? Trang ny

Tm t?t

Bi vi?t t?ng b?c ny gi?i thch cch nh?p d? li?u t? trang tnh Microsoft Excel vo c s? d? li?u Microsoft SQL Server b?ng cch s? d?ng nhi?u phng php.

M ta Phng php

Cc m?u trong bi vi?t ny nh?p d? li?u Excel b?ng cch s? d?ng:
  • Dich vu Chuyn i D liu (DTS) SQL Server
  • Dich vu Tich hp Microsoft SQL Server 2005 (SSIS)
  • May chu lin kt SQL Server
  • Truy v?n phn ph?i SQL Server
  • ?i t?ng D? li?u ActiveX (ADO) v Microsoft OLE DB Provider cho SQL Server
  • ADO v Microsoft OLE DB Provider cho Jet 4.0

Yu cu

Danh sach bn di a ra phn cng, phn mm, ha tng mang va goi dich vu c khuyn nghi ma ban cn:
  • Phin b?n c s?n c?a Microsoft SQL Server 7.0 ho?c Microsoft SQL Server 2000 ho?c Microsoft SQL Server 2005
  • Microsoft Visual Basic 6.0 cho cc m?u ADO s? d?ng Visual Basic
Cc ph?n trong bai vit nay gia inh rng ban a quen vi cac chu sau:
  • D?ch v? Chuy?n ?i D? li?u
  • Cac may chu c lin kt va cac truy vn c phn tan
  • Pht tri?n ADO trong Visual Basic

M?u

Nh?p v Thm

L?nh SQL m?u ?c s? d?ng trong bi vi?t ny gi?i thch truy v?n T?o B?ng nh?p d? li?u Excel vo b?ng SQL Server m?i b?ng cch s? d?ng c php CH?N...VO...T?. B?n c th? chuy?n cc l?nh ny thnh truy v?n Thm b?ng cch s? d?ng c php CHN VO...CH?N...T? khi b?n ti?p t?c tham chi?u ?i t?ng ngu?n v ch nh ?c hi?n th? trong cc v d? v? m? sau.

S? d?ng DTS ho?c SSIS

B?n c th? s? d?ng Thu?t s? Nh?p c?a Dich vu Chuyn i D liu (DTS) SQL Server ho?c Thu?t s? Nh?p v Xu?t SQL Server ? nh?p d? li?u Excel vo b?ng SQL Server. Khi b?n xem thu?t s? v ch?n b?ng ngu?n Excel, h?y nh? r?ng tn ?i t?ng Excel ?c n?i b?ng k? hi?u la ($) ?i di?n cho trang tnh (v d?: Sheet1 $) v tn ?i t?ng thu?n m khng c k? hi?u la ?i di?n cho d?i ?c ?t tn trong Excel.

S? d?ng My ch? Lin k?t

? n gi?n truy v?n, b?n c th? c?u h?nh s? lm vi?c Excel lm my ch? ?c lin k?t trong SQL Server. bit thm thng tin, hay bm vao s bai vit bn di xem bai vit trong C s Kin thc Microsoft:
306397 CCH TH?C HI?N: S? d?ng Excel v?i My ch? Lin k?t SQL Server v Truy v?n Phn ph?i
M? sau nh?p d? li?u t? trang tnh Khch hng trn my ch? lin k?t Excel "EXCELLINK" vo b?ng SQL Server m?i c tn XLImport1:
CH?N * INTO XLImport1 T? EXCELLINK...[Customers$]
				
B?n c?ng c th? th?c thi truy v?n d?a trn ngu?n theo cch thng qua b?ng cch s? d?ng OPENQUERY nh sau:
CH?N * INTO XLImport2 T? OPENQUERY(EXCELLINK,
    'CH?N * T? [Customers$]')
				

S? d?ng Truy v?n Phn ph?i

N?u b?n khng mu?n c?u h?nh k?t n?i ?n ?nh v?i s? lm vi?c Excel lm my ch? lin k?t, b?n c th? nh?p d? li?u cho m?c ch c? th? b?ng cch s? d?ng ch?c nng OPENDATASOURCE ho?c OPENROWSET. V d? v? m? sau c?ng nh?p d? li?u t? trang tnh Khch hng c?a Excel vo b?ng SQL Server m?i:
CH?N * VO XLImport3 T? OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

CH?N * VO XLImport4 T? OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

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

S? d?ng ADO v SQLOLEDB

Khi b?n ?c k?t n?i v?i SQL Server trong ?ng d?ng ADO b?ng cch s? d?ng Microsoft OLE DB cho SQL Server (SQLOLEDB), b?n c th? s? d?ng cng m?t c php "truy v?n phn ph?i" t? ph?n S? d?ng Truy v?n Phn ph?i ? nh?p d? li?u Excel vo SQL Server.

V d? v? m? Visual Basic 6.0 sau y yu c?u b?n thm tham chi?u ?i t?ng vo ?i t?ng D? li?u ActiveX (ADO). V d? v? m? ny c?ng gi?i thch cch s? d?ng OPENDATASOURCE v OPENROWSET qua k?t n?i SQLOLEDB.
    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    ?t cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _
        "Initial Catalog=<database>;User ID=<user>;Password=<password>"

    'Nh?p b?ng cch s? d?ng 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

    'Nh?p b?ng cch s? d?ng OPENROWSET v tn ?i t?ng.
    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

    'Nh?p b?ng cch s? d?ng truy v?n OPENROWSET v SELECT.
    strSQL = "SELECT * INTO XLImport8 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "'CH?N * T? [Customers$]')"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    ?t cn = Khng
				

S? d?ng ADO v Jet Provider

V d? trong ph?n tr?c s? d?ng ADO v?i Nh cung c?p d?ch v? SQLOLEDB ? k?t n?i v?i ch trong d? li?u nh?p t? Excel ?n SQL c?a b?n. B?n c?ng c th? s? d?ng OLE DB Provider cho Jet 4.0 ? k?t n?i v?i ngu?n Excel.

Cng c? c s? d? li?u Jet c th? tham chi?u c s? d? li?u m? r?ng trong l?nh SQL b?ng cch s? d?ng c php ?c bi?t c 3 ?nh d?ng khc nhau:
  • [?ng d?n ?y ? ?n c s? d? li?u Microsoft Access].[Tn B?ng]
  • [Tn ISAM;Chu?i K?t n?i ISAM].[Tn B?ng]
  • [ODBC;Chu?i K?t n?i ODBC].[Tn B?ng]
Ph?n ny s? d?ng ?nh d?ng th? 3 ? th?c hi?n k?t n?i ODBC ?n c s? d? li?u SQL Server ch. B?n c th? s? d?ng Tn Ngu?n D? li?u ODBC (DSN) ho?c chu?i k?t n?i khng c DSN:
DSN:
    [odbc;DSN=<DSN name>;UID=<user>;PWD=<password>]

t DSN hn:
   [odbc;Driver={SQL Server};Server=<server>;Database=<database>;
       UID=<user>;PWD=<password>]
				
V d? v? m? Visual Basic 6.0 sau y yu c?u b?n thm tham chi?u ?i t?ng vo ADO. V d? v? m? ny gi?i thch cch nh?p d? li?u Excel vo SQL Server qua k?t n?i ADO b?ng cch s? d?ng Jet 4.0 Provider.
    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    ?t cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\test\xltestt.xls;" & _
        "Extended Properties=Excel 8.0"
    
    ' Nh?p b?ng cch s? d?ng 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
    ?t cn = Khng
				
B?n c?ng c th? s? d?ng c php ny m Jet Provider h? tr?, ? nh?p d? li?u Excel vo c s? d? li?u Microsoft Access khc, c s? d? li?u phng php truy c?p tu?n t? ?c nh ch? m?c (ISAM) ("my tnh ? bn") ho?c c s? d? li?u ODBC.

G ri

  • H?y nh? r?ng tn ?i t?ng Excel ?c ghi cng v?i k? hi?u la ($) ?i di?n cho trang tnh (v d?: Sheet1$) v ?i t?ng thu?n ?i di?n cho d?i ?c ?t tn trong Excel.
  • Trong m?t s? tr?ng h?p, ?c bi?t khi b?n ch? ?nh d? li?u ngu?n Excel b?ng cch s? d?ng tn b?ng thay v? truy v?n CH?N, cc c?t trong b?ng SQL Server ch ?c s?p x?p l?i theo th? t? trong b?ng ch? ci. bit thm thng tin v cach khc phuc s c ny v?i Jet Provider, bm s bai vit bn d?i xem bai vit trong C s Kin thc Microsoft:
    299484 PRB: Cc c?t ?c s?p x?p theo b?ng ch? ci khi b?n s? d?ng ADOX ? truy xu?t c?t trong b?ng truy c?p
  • Khi Jet Provider xc ?nh r?ng c?t Excel ch?a vn b?n h?n h?p v d? li?u s?, Jet Provider ch?n lo?i d? li?u "a s?" v tr? v? gi tr? khng ph h?p d?i d?ng TR?NG. bit thm thng tin v cach khc phuc s c nay, hay bm vao s bai vit di y xem bai vit trong C s Kin thc Microsoft:
    194124 PRB: Gi tr? Excel ?c tr? v? d?i d?ng TR?NG b?ng cch s? d?ng DAO OpenRecordset

Tham kh?o

bit thm thng tin v cach s? d?ng Excel lm ngun d liu, bm s bai vit d?i y xem bai vit trong C s Kin thc Microsoft:
257819 CCH TH?C HI?N: S? d?ng ADO v?i D? li?u Excel t? Visual Basic ho?c VBA
bit thm thng tin v cach chuy?n d? li?u vo Excel, bm s bai vit sau y xem bai vit trong C s Kin thc Microsoft:
295646 CCH TH?C HI?N: Chuy?n D? li?u t? Ngu?n D? li?u ADO vo Excel v?i ADO
247412 THNG TIN: Cc phng php Chuy?n D? li?u sang Excel t? Visual Basic
246335 CCH TH?C HI?N: Chuy?n D? li?u t? ADO Recordset sang Excel b?ng tnh nng T? ?ng
319951 CACH THC: Chuy?n D? li?u sang Excel b?ng cch s? d?ng D?ch v? Chuy?n D? li?u SQL Server
306125 CACH THC: Nh?p D? li?u t? SQL Server vo Microsoft Excel

Thu?c tnh

ID c?a bi: 321686 - L?n xem xt sau cng: 02 Thang Mi Hai 2013 - Xem xt l?i: 4.0
p d?ng
  • 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
T? kha:
kbhowtomaster kbjet KB321686

Cung cp Phan hi

 

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