Impor data dari excel ke sql server menggunakan query

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

Saya 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

Jadi, 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)

Hal 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

Keunikan 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

Secara 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)

Dalam 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

Aktifkan 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

“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;_

Jika penyedia tidak diinstal, itu harus diunduh dan diinstal. https. // www. microsoft. com/en-us/download/details. aspx?id=13255

Unduh file x64

Ada 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

Bagaimana saya bisa menggunakan data Excel dalam kueri SQL?

Bagaimana cara mengimpor data dalam kueri SQL?

Bagaimana cara mengimpor lembar Excel di SQL Server 2012 menggunakan kueri?