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
