Microsoft SQL Server memungkinkan Anda mengimpor data dari file Excel ke database menggunakan bahasa T-SQL bawaan dalam kueri SQL. Hari ini saya akan memberi tahu Anda secara rinci bagaimana hal itu dilakukan, kondisi apa yang harus dipenuhi untuk membuat operasi ini berhasil, memberi tahu Anda tentang fitur impor untuk kasus konfigurasi SQL server yang paling umum dan memberikan prosedur spesifik dan contoh praktis
IMPOR DATA DARI EXCEL KE MICROSOFT SQL SERVERSaya akan mulai dengan mengatakan bahwa Anda dapat mengimpor data dari Excel ke Microsoft SQL Server menggunakan "Kueri Terdistribusi" dan "Server Tertaut". Anda mungkin sudah mengetahui hal ini, karena saya telah menulisnya lebih dari sekali (referensi ke materi yang relevan diberikan di atas)
Anda dapat mengakses file Excel dan mengimpor data ke Microsoft SQL Server menggunakan instruksi T-SQL OPENDATASOURCE, OPENROWSET atau OPENQUERY
Namun, dalam artikel di atas saya telah melewatkan beberapa poin penting, salah satunya adalah bahwa semua konfigurasi SQL Server berbeda, karena banyak yang memiliki masalah dan kesalahan yang berbeda selama eksekusi kueri terdistribusi dan panggilan ke server terkait
Saya juga menjelaskan cara mendownload data dari Excel yang sekarang sudah ketinggalan zaman, jadi hari ini saya akan mencoba memberi Anda sedikit informasi tentang cara mengimpor data dari file Excel ke Microsoft SQL Server dalam bahasa T-SQL
pengantarJadi, seperti yang saya katakan, konfigurasi server SQL memainkan peran yang sangat penting di sini, khususnya versi server mana yang diinstal, x86 atau x64
Jika kita berbicara tentang versi terbaru Microsoft SQL Server 2016–2019, mereka hanya x64 dan diinstal pada Windows versi 64-bit
Atas dasar ini, saya akan membagi artikel menjadi beberapa bagian, di mana masing-masing saya akan memberi tahu Anda tentang fitur mengimpor data dari Excel untuk kasus konfigurasi yang paling umum dan memberi Anda urutan tindakan tertentu.
Untuk mengetahui dengan cepat versi SQL Server mana yang diinstal di komputer Anda, Anda dapat membuat kueri SQL sederhana
SELECT @@VERSION;
Akses ke file Excel dan, karenanya, impor data ke Microsoft SQL Server dilakukan oleh penyedia khusus (vendor). Untuk bekerja dengan Excel di Microsoft SQL Server biasanya digunakan
- Jet. OLEDB. 4. 0
- KARTU AS. OLEDB. 12. 0
Dalam semua contoh di bawah ini, saya akan mengirimkan kueri SELECT sederhana untuk memilih data dari file Excel untuk memeriksa akses ke data di file Excel. Untuk mengimpor data (mengunggah data ke database), Anda dapat menggunakan metode apa pun yang nyaman bagi Anda, mis. g. SELECT INTO atau INSERT INTO konstruksi
Selain itu, disarankan untuk menutup file Excel saat mengaksesnya dalam kueri terdistribusi, serta menentukan jalur ke file tanpa spasi (meskipun server SQL modern dapat bekerja dengan spasi)
IMPOR DATA DARI EXCEL 2003 (XLS FILE) KE MICROSOFT SQL SERVER X86Langkah 1 — Periksa Microsoft. Jet. OLEDB. 4. 0 penyedia di SQL ServerHal pertama yang perlu kita mulai adalah memeriksa apakah Microsoft. Jet. OLEDB. 4. 0 provider terdaftar di SQL Server, karena dalam hal ini kita perlu menggunakan provider tersebut. Ini dapat dilakukan dengan menggunakan instruksi SQL berikut
EXEC sp_enum_oledb_providers;_
Dataset yang dihasilkan harus berisi string dengan Microsoft. Jet. OLEDB. 4. 0. Jika tidak ada penyedia seperti itu, kemungkinan besar tidak ada Excel 2003 yang diinstal di sistem dan, karenanya, harus diinstal
Langkah 2 — Memberikan hak pengguna ke direktori sementaraKeunikan kueri terdistribusi dan bekerja dengan server Excel terkait di SQL Server versi x86 adalah bahwa terlepas dari nama akun mana yang mengirimkan kueri SQL ke Excel, akun ini harus memiliki hak untuk menulis ke direktori sementara akun tempat
Karena vendor OLE DB membuat file sementara selama kueri di direktori sementara SQL Server menggunakan kredensial pengguna yang menjalankan kueri
Jadi, jika layanan SQL Server berjalan atas nama layanan lokal atau jaringan, Anda perlu memberikan izin yang sesuai ke direktori sementara layanan ini kepada semua pengguna yang akan mengirim kueri terdistribusi dan menghubungi server Excel terkait (jika
Ini dapat dilakukan dengan menggunakan icacls utilitas baris perintah bawaan
Misalnya, untuk layanan lokal, perintahnya akan terlihat seperti ini
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)
Untuk layanan jaringan
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)
Di tempat UserName, berikan nama pengguna yang mengirimkan permintaan
Langkah 3 — Aktifkan kueri terdistribusi di SQL ServerSecara default, kemampuan untuk menggunakan kueri terdistribusi, khususnya fungsi OPENDATASOURCE dan OPENROWSET, dilarang di Microsoft SQL Server, sehingga fitur ini harus diaktifkan terlebih dahulu
Ini diaktifkan menggunakan prosedur tersimpan sistem sp_configure, yang bertanggung jawab atas parameter sistem server. Kita perlu mengatur parameter Ad Hoc Distributed Queries menjadi 1, untuk melakukan ini kita menjalankan instruksi SQL berikut
sp_configure 'show advanced options', 1;_
RECONFIGURE;
GO_
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO_
Di bawah ini saya akan memberikan beberapa pilihan untuk mengakses file Excel (TestExcel. xls)
OPENROWSET
EXEC sp_enum_oledb_providers;_0
EXEC sp_enum_oledb_providers;_1
EXEC sp_enum_oledb_providers;_2
EXEC sp_enum_oledb_providers;_3
EXEC sp_enum_oledb_providers;_4
EXEC sp_enum_oledb_providers;_5
SUMBER TERBUKA
EXEC sp_enum_oledb_providers;_6
EXEC sp_enum_oledb_providers;_7
EXEC sp_enum_oledb_providers;_8
EXEC sp_enum_oledb_providers;_9
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)0
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)1
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)2
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)_3
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)_4
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)5
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)6
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)7
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)_8
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)_9
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)0
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)1
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)2
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)_3
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)_4
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)5
Sama seperti pada contoh sebelumnya, pertama-tama kita periksa apakah kita memiliki ISP yang perlu kita instal, dalam hal ini kita memerlukan Microsoft. KARTU AS. OLEDB. 12. 0
EXEC sp_enum_oledb_providers;_
Jika tidak ada penyedia, itu harus diinstal. Berikut tautan ke unduhan ISP. https. // www. microsoft. com/en-us/download/details. aspx?id=13255
Pilih dan unduh file yang sesuai dengan arsitektur x86 (mis. e. atas nama tanpa x64)
Langkah 3 — Memberikan hak pengguna ke direktori sementaraDalam hal ini, kami juga memberikan hak atas direktori sementara layanan lokal atau jaringan kepada semua pengguna yang akan mengirim kueri SQL ke file Excel
Kami menggunakan icacls utilitas baris perintah yang sama
Untuk layanan lokal
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)
Untuk layanan jaringan
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)
Di tempat UserName, berikan nama pengguna yang mengirimkan permintaan
Langkah 4 — Aktifkan Kueri Terdistribusi di SQL ServerAktifkan kemampuan untuk menggunakan OPENDATASOURCE dan OPENROWSET di Microsoft SQL Server, saya ulangi bahwa fitur ini dinonaktifkan secara default
sp_configure 'show advanced options', 1;_
RECONFIGURE;
GO_
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO_
Dalam hal ini, Anda juga perlu mengonfigurasi penyedia Microsoft. KARTU AS. OLEDB. 12. 0. Untuk melakukan ini, aktifkan parameter penyedia berikut (tentukan 0 alih-alih 1 untuk menonaktifkan)
sp_configure 'show advanced options', 1;_5
GO_
sp_configure 'show advanced options', 1;_7
GO_
Jika parameter ini tidak disertakan, kemungkinan besar akan muncul kesalahan, kira-kira sebagai berikut
Langkah 6 — Jalankan kueri SQL, akses ke file Excel“Pesan 7399, level 16, status 1, baris 25
Penyedia OLE DB “Microsoft. KARTU AS. OLEDB. 12. 0" untuk server terkait "(null)" melaporkan kesalahan. Penyedia tidak memberikan data kesalahan
Pesan 7330, level 16, negara bagian 2, baris 25
Kami gagal mendapatkan string dari penyedia OLE DB “Microsoft. KARTU AS. OLEDB. 12. 0” untuk server terkait “(null)”
Contoh mengakses file Excel (TestExcel. xlsx)
OPENROWSET
EXEC sp_enum_oledb_providers;_0
EXEC sp_enum_oledb_providers;_1
RECONFIGURE;1
RECONFIGURE;2
RECONFIGURE;_3
EXEC sp_enum_oledb_providers;_4
EXEC sp_enum_oledb_providers;_5
SUMBER TERBUKA
RECONFIGURE;_6
RECONFIGURE;_7
RECONFIGURE;_8
Server Tertaut
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)0
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)1
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)2
GO_2
GO_3
GO_4
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)6
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)7
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)_8
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)_9
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)0
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)1
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)2
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)_3
sp_configure 'Ad Hoc Distributed Queries', 1;3
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)5
Dalam hal ini kami juga menggunakan Microsoft. KARTU AS. OLEDB. 12. 0 penyedia, periksa dulu apakah terdaftar di server
EXEC sp_enum_oledb_providers;_
Langkah 2 — Menginstal Microsoft. KARTU AS. OLEDB. 12. 0 (64-bit) penyediaJika penyedia tidak diinstal, itu harus diunduh dan diinstal. https. // www. microsoft. com/en-us/download/details. aspx?id=13255
Unduh file x64
Langkah 3 — Aktifkan kueri terdistribusi di SQL ServerAda juga kebutuhan untuk mengaktifkan kemampuan untuk menggunakan kueri terdistribusi (OPENDATASOURCE dan OPENROWSET) di Microsoft SQL Server x64, jadi aktifkan terlebih dahulu dengan mengikuti instruksi yang persis sama
sp_configure 'show advanced options', 1;_
RECONFIGURE;
GO_
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO_
Dalam hal ini, kemungkinan besar, konfigurasi penyedia tidak diperlukan, jadi pertama-tama coba jalankan kueri SQL (lihat data di Excel), dan jika terjadi kesalahan (semua dengan pesan yang sama 7399 dan 7330), coba aktifkan
sp_configure 'show advanced options', 1;_5
GO_
sp_configure 'show advanced options', 1;_7
GO_
Di sini parameter yang sama digunakan dalam kueri SQL seperti pada contoh sebelumnya. Untuk kenyamanan, saya akan menggandakannya sekali lagi
Contoh mengakses file Excel (TestExcel. xlsx)
OPENROWSET
EXEC sp_enum_oledb_providers;_0
EXEC sp_enum_oledb_providers;_1
RECONFIGURE;1
RECONFIGURE;2
RECONFIGURE;_3
EXEC sp_enum_oledb_providers;_4
EXEC sp_enum_oledb_providers;_5
SUMBER TERBUKA
RECONFIGURE;_6
RECONFIGURE;_7
RECONFIGURE;_8
Server Tertaut
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)0
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)1
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)2
GO_2
GO_3
GO_4
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)6
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)7
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)_8
icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)_9
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)0
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)1
EXEC sp_enum_oledb_providers;_08
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)_3
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)_4
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)5
Terakhir, saya akan mengelompokkan tindakan yang akan dilakukan tergantung pada rilis SQL Server (x68 atau x64) dan versi file Excel (xls atau xlsx) ke dalam satu tabel untuk kenyamanan Anda