Tips Cara mengimpor data dari Excel ke SQL Serve


Artikel ini mejunjukkan bagaimana untuk mengimpor data dari lembar kerja Microsoft Excel ke Microsoft SQL Server database dengan menggunakan berbagai metode.

Deskripsi dari teknik
Contoh dalam artikel ini mengimpor data Excel menggunakan:
  • SQL Server Data Transformation Services (DTS)
  • Microsoft SQL Server 2005 Integration Services (SSIS)
  • Server-server SQL Server yang saling berhubungan
  • Query SQL Server yang didistribusikan
  • ActiveX Data Objects (ADO) dan Mcrosoft OLE DB Provider untuk SQL Server
  • ADO dan Microsoft OLE DB provider untuk Jet 4.0

Persyaratan

Daftar berikut ini menguraikan fitur perangkat keras, perangkat lunak, infrastruktur jaringan, dan paket layanan yang diperlukan:
  • Instance untuk 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
  • Server yang saling berhubungan dan distribusi query
  • Pengembangan ADO dalam Visual Basic

Sample

Impor vs Append

Pernyataan SQL sampel yang digunakan dalam artikel ini mendemokan query Create Table yang mengimpor Excel data ke dalam tabel SQL Server baru dengan menggunakan perintah SELECT...INTO...FROM. Anda dapat mengkonversi pernyataan-pernyataan ini untuk menambahkan pertanyaan dengan menggunakan perintah INSERT INTO...SELECT...FROM sementara Anda teruskan mereferensikan objek sumber dan tujuan seperti ditunjukkan pada contoh kode ini.


Menggunakan DTS atau SSIS

Anda dapat menggunakan Wizard Impor SQL Server Data transformasi Layanan (DTS) atau SQL Server impor dan Ekspor Wizard untuk mengimpor Excel data ke tabel SQL Server. Ketika Anda menggunakan wizard dan memilih 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 range Excel.


Menggunakan Server terkait

Untuk menyederhanakan query, Anda dapat mengkonfigurasikan buku kerja Excel sebagai server terkait di SQL Server. Untuk informasi tambahan, klik nomor artikel di bawah ini untuk melihat artikel di dalam Basis 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 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 untuk mengkonfigurasi koneksi terus-menerus ke buku kerja Excel 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 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


Ketika Anda terhubung ke SQL Server di aplikasi ADO dengan menggunakan Microsoft OLE DB untuk SQL Server (SQLOLEDB), Anda dapat menggunakan sintaks "distributed query" yang sama dari bagian Menggunakan distribusi query untuk mengimpor Excel data ke SQL Server.

Contoh kode Visual Basic 6.0 berikut memerlukan Anda untuk menambahkan referensi proyek ke ActiveX Data Objects (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 menghubungkan ke tujuan Anda impor Excel-untuk-SQL. Anda juga dapat menggunakan OLE DB Provider untuk Jet 4.0 untuk terhubung ke sumber Excel.

Jet database engine dapat mereferensikan eksternal database dalam pernyataan SQL dengan menggunakan sintaksis khusus yang memiliki tiga format yang berbeda:
  • [Path lengkap ke Microsoft Access database].[Nama Daftar Tabel]
  • [Nama Islam TERPADU;Rangkaian sambungan Islam TERPADU].[Nama Daftar Tabel]
  • [ODBC;Koneksi ODBC String].[Nama Daftar Tabel]
Bagian ini menggunakan format ketiga untuk membuat koneksi ODBC ke database SQL Server tujuan. Anda dapat menggunakan ODBC Data Source Name (DSN) atau DSN-less connection 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 Jet Provider, mengimpor Excel data ke Microsoft Access database lain, indexed sequential access method (ISAM) ("desktop") database, atau ODBC database.

Pemecahan Masalah

  • Ingat bahwa nama objek Excel yang ditambahkan dengan tanda dolar ($) mewakili lembar kerja (misalnya, Sheet1$) dan bahwa nama objek polos mewakili range Excel.
  • Dalam beberapa keadaan, terutama ketika Anda menetapkan data sumber Excel dengan menggunakan nama tabel bukan SELECT query, kolom dalam tabel SQL Server tujuan akan tersusun dalam urutan abjad.Untuk informasi tambahan tentang masalah ini dengan Jet Provider, klik nomor artikel di bawah ini untuk melihat artikel di dalam Basis Pengetahuan Microsoft:
299484 PRB: Kolom diurutkan berdasarkan abjad ketika Anda menggunakan ADOX untuk mengambil kolom tabel akses
  • Ketika Jet Provider menentukan bahwa kolom Excel berisi campuran teks dan data numerik, Jet Provider memilih tipe data "mayoritas" dan mengembalikan 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 Basis Pengetahuan Microsoft:
194124 PRB: Nilai-nilai Excel kembali sebagai NULL menggunakan DAO OpenRecordset

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)