Bagaimana cara mengimpor data dari Excel ke SQL Server

Terjemahan Artikel Terjemahan Artikel
ID Artikel: 321686 - Melihat produk di mana artikel ini berlaku.
Perbesar semua | Perkecil semua

Pada Halaman ini

Ringkasan

Artikel langkah-langkah ini menunjukkan bagaimana untuk mengimpor data dari worksheet Microsoft Excel ke dalam database Microsoft SQL Server dengan menggunakan berbagai metode.

Deskripsi dari teknik

Sampel dalam artikel ini mengimpor Excel data dengan menggunakan:
  • SQL Server Data Transformation Services (DTS)
  • Microsoft SQL Server 2005 integrasi layanan (SSIS)
  • SQL Server terkait server
  • Query SQL Server didistribusikan
  • Obyek Data ActiveX (ADO) dan penyedia Microsoft OLE DB untuk SQL Server
  • ADO dan penyedia Microsoft OLE DB untuk Jet 4.0

Persyaratan

Daftar berikut menguraikan fitur peranti penangkap keras, peranti penangkap lunak, infrastruktur jaringan, dan Service Pack yang diperlukan:
  • Tersedia misalnya Microsoft SQL Server 7.0 atau Microsoft SQL Server 2000 atau Microsoft SQL Server 2005
  • Microsoft Visual Basic 6.0 untuk sampel ADO yang menggunakan Visual Basic
Bagian dari artikel ini mengasumsikan bahwa Anda sudah familiar dengan berikut topik:
  • Data Transformation Services
  • Terkait server dan didistribusikan pertanyaan
  • ADO pengembangan dalam Visual Basic

Sampel

Impor vs Append

Pernyataan SQL sampel yang digunakan dalam artikel ini menunjukkan kueri Create Table yang mengimpor Excel data ke dalam Daftar Tabel SQL Server baru dengan menggunakan pilih...KE...DARI sintaks. Anda dapat mengkonversi pernyataan-pernyataan ini untuk menambahkan pertanyaan dengan menggunakan INSERT INTO...PILIH...DARI sintaks sementara Anda terus referensi objek sumber dan tujuan seperti ditunjukkan pada contoh kode ini.

Menggunakan DTS atau SSIS

Anda dapat menggunakan Wizard Impor SQL Server Data Transformation Services (DTS) atau SQL Server impor dan Ekspor Wizard untuk mengimpor Excel data ke Daftar Tabel SQL Server. Ketika Anda melangkah melalui wizard dan memilih Daftar Tabel sumber Excel, ingat bahwa nama objek Excel yang ditambahkan dengan tanda dolar ($) mewakili lembar kerja (misalnya, Sheet1$), dan bahwa nama objek polos tanpa tanda dolar mewakili Excel bernama rentang.

Menggunakan Server terkait

Untuk menyederhanakan pertanyaan, Anda dapat mengkonfigurasi buku kerja Excel sebagai server terkait di SQL Server.Untuk informasi tambahan, klik nomor artikel di bawah ini untuk melihat artikel di dalam Pangkalan Pengetahuan Microsoft:
306397 HOWTO: Menggunakan Excel dengan SQL Server terkait server dan didistribusikan pertanyaan
Kode berikut akan mengimpor data dari worksheet pelanggan di Excel terkait server "EXCELLINK" ke dalam Daftar Tabel SQL Server baru bernama XLImport1:
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
				
Anda juga dapat menjalankan query terhadap sumber secara passthrough dengan menggunakan OPENQUERY sebagai berikut:
SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
    'SELECT * FROM [Customers$]')
				

Menggunakan query didistribusikan

Jika Anda tidak ingin mengkonfigurasi koneksi terus-menerus ke Excel workbook sebagai terkait server, Anda dapat mengimpor data untuk tujuan tertentu dengan menggunakan OPENDATASOURCE atau fungsi OPENROWSET. Contoh kode berikut juga mengimpor data dari worksheet Excel pelanggan ke Daftar Tabel SQL Server baru:
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$]')
				

Menggunakan ADO dan SQLOLEDB

Bila Anda terhubung ke SQL Server dalam aplikasi ADO menggunakan Microsoft OLE DB untuk SQL Server (SQLOLEDB), Anda dapat menggunakan sintaks "didistribusikan permintaan" yang sama dari Menggunakan query didistribusikan Bagian untuk mengimpor Excel data ke SQL Server.

Contoh kode Visual Basic 6.0 berikut memerlukan Anda untuk menambahkan proyek referensi ke objek data ActiveX (ADO). Sampel kode ini juga menunjukkan bagaimana menggunakan OPENDATASOURCE dan OPENROWSET melalui koneksi 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
				

Menggunakan ADO dan penyedia Jet

Sampel dalam bagian sebelumnya menggunakan ADO dengan penyedia SQLOLEDB untuk tautan langsung ke nomor tujuan Anda impor Excel-untuk-SQL. Anda juga dapat menggunakan OLE DB penyedia untuk Jet 4.0 untuk terhubung ke sumber Excel.

Jet mesin database dapat referensi eksternal database dalam statement SQL dengan menggunakan sintaksis khusus yang memiliki tiga format yang berbeda:
  • [Path lengkap untuk database Microsoft Access].[Nama Daftar Tabel]
  • [Nama Islam Terpadu;Islam Terpadu koneksi String].[Nama Daftar Tabel]
  • [ODBC;Koneksi ODBC String].[Nama Daftar Tabel]
Bagian ini menggunakan format ketiga untuk membuat koneksi ODBC ke tujuan SQL Server database. Anda dapat menggunakan ODBC nama data sumber (DSN) atau DSN-kurang koneksi string:
DSN:
    [odbc;DSN=<DSN name>;UID=<user>;PWD=<password>]

DSN-less:
   [odbc;Driver={SQL Server};Server=<server>;Database=<database>;
       UID=<user>;PWD=<password>]
				
Contoh kode Visual Basic 6.0 berikut memerlukan Anda untuk menambahkan referensi proyek ADO. Sampel kode ini menunjukkan bagaimana untuk mengimpor Excel data ke SQL Server melalui koneksi ADO menggunakan Jet 4.0 penyedia.
    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
				
Anda juga dapat menggunakan sintaks ini, yang mendukung penyedia Jet, untuk mengimpor Excel data ke Microsoft Access database lain, metode (Islam Terpadu) diindeks berurutan akses database ("desktop"), atau database ODBC.

Pemecahan masalah

  • Ingat bahwa nama objek Excel yang ditambahkan dengan tanda dolar ($) mewakili lembar kerja (misalnya, Sheet1$) dan bahwa nama objek polos mewakili Excel bernama rentang.
  • Dalam beberapa keadaan, terutama ketika Anda menetapkan Excel data sumber dengan menggunakan nama Daftar Tabel bukan query SELECT, kolom dalam Daftar Tabel SQL Server tujuan rearranged dalam urutan menurun abjad.Untuk informasi tambahan tentang masalah ini dengan penyedia Jet, klik nomor artikel di bawah ini untuk melihat artikel di dalam Pangkalan Pengetahuan Microsoft:
    299484 PRB: Kolom diurutkan berdasarkan abjad ketika Anda menggunakan ADOX untuk mengambil Kolom Bertumpuk akses
  • Ketika penyedia Jet menentukan bahwa Excel kolom berisi campuran teks dan numeric data, penyedia Jet memilih tipe data "sebagian" dan kembali nilai-nilai yang cocok sebagai NULLs.Untuk informasi tambahan tentang cara mengatasi masalah ini, klik nomor artikel di bawah ini untuk melihat artikel di dalam Pangkalan Pengetahuan Microsoft:
    194124 PRB: Nilai-nilai Excel kembali sebagai NULL menggunakan DAO OpenRecordset

Referensi

Untuk informasi tambahan tentang cara menggunakan Excel sebagai data sumber, klik nomor artikel di bawah ini untuk melihat artikel di dalam Pangkalan Pengetahuan Microsoft:
257819 HOWTO: Menggunakan ADO dengan Excel Data dari Visual Basic atau VBA
Untuk informasi tambahan tentang bagaimana untuk mentransfer data ke Excel, klik nomor artikel di bawah ini untuk melihat artikel di dalam Pangkalan Pengetahuan Microsoft:
295646 HOWTO: Transfer Data dari data sumber ADO ke Excel dengan ADO
247412 INFO: Metode untuk mentransfer Data ke Excel dari Visual Basic
246335 HOWTO: Transfer Data dari ADO Recordset ke Excel dengan otomatisasi
319951 CARA: Mentransfer Data ke Excel dengan menggunakan SQL Server Data Transformation Services
306125 CARA: Mengimpor Data dari SQL Server ke Microsoft Excel

Properti

ID Artikel: 321686 - Kajian Terakhir: 28 April 2013 - Revisi: 11.0
Berlaku bagi:
  • 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
Kata kunci: 
kbhowtomaster kbjet kbmt KB321686 KbMtid
Penerjemahan Mesin
PENTING: Artikel ini diterjemahkan oleh perangkat lunak penerjemahan mesin Microsoft, dan mungkin telah diedit oleh Masyarakat Microsoft melalui teknologi CTF dan bukan oleh seorang penerjemah profesional. Microsoft menawarkan baik artikel yang diterjemahkan oleh manusia maupun artikel hasil editan terjemahan oleh mesin/komunitas, sehingga Anda dapat mengakses semua artikel di Sentra Pengetahuan yang kami miliki dalam berbagai bahasa. Namun artikel hasil editan mesin atau bahkan komunitas tidak selalu sempurna. Artikel ini dapat mengandung kesalahan dalam hal kosa kata, sintaksis atau tatabahasa, sangat mirip dengan penutur asing yang membuat kekeliruan ketika berbicara dalam bahasa Anda. Microsoft tidak bertanggung jawab atas ketidakakuratan, kesalahan atau kerugian apa pun akibat dari kekeliruan dalam penerjemahan isi atau penggunaannya oleh pelanggan kami. Microsoft juga akan senantiasa memperbarui perangkat lunak penerjemahan mesin dan alat untuk menyempurnakan Editan Hasil Penerjemahan Mesin.
Klik disini untuk melihat versi Inggris dari artikel ini: 321686

Berikan Masukan

 

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