Mengimpor data dari Excel ke SQL Server atau Azure SQL Database

Artikel
07/23/2024

Berlaku untuk: SQL Server Azure SQL Database

Ada beberapa cara untuk mengimpor data dari file Excel ke SQL Server atau ke Azure SQL Database. Beberapa metode memungkinkan Anda mengimpor data dalam satu langkah langsung dari file Excel; metode lain mengharuskan Anda mengekspor data Excel sebagai teks (file CSV) sebelum Anda bisa mengimpornya.

Artikel ini merangkum metode yang sering digunakan dan menyediakan tautan untuk informasi yang lebih rinci. Deskripsi lengkap tentang alat dan layanan kompleks seperti SSIS atau Azure Data Factory berada di luar cakupan artikel ini. Untuk informasi selengkapnya tentang solusi yang menarik minat Anda, ikuti tautan yang disediakan.

Daftar metode

Ada beberapa cara untuk mengimpor data dari Excel. Anda perlu menginstal SQL Server Management Studio (SSMS) untuk menggunakan beberapa alat ini.

Anda bisa menggunakan alat berikut untuk mengimpor data dari Excel:

Ekspor ke teks terlebih dahulu (SQL Server dan Azure SQL Database)	Langsung dari Excel (SQL Server lokal saja)
Wizard Impor File Datar	Wizard Impor dan Ekspor SQL Server
Pernyataan BULK INSERT	SQL Server Integration Services (SSIS)
Alat salin massal (bcp)	Fungsi OPENROWSET
Wizard Salin (Azure Data Factory)
Azure Data Factory

Jika Anda ingin mengimpor beberapa lembar kerja dari buku kerja Excel, Anda biasanya harus menjalankan salah satu alat ini sekali untuk setiap lembar.

Untuk informasi selengkapnya, lihat batasan dan masalah yang diketahui untuk memuat data ke atau dari file Excel.

Wizard Impor dan Ekspor

Impor data langsung dari file Excel dengan menggunakan Wizard Impor dan Ekspor SQL Server. Anda juga dapat menyimpan pengaturan sebagai paket SQL Server Integration Services (SSIS) yang dapat Anda sesuaikan dan gunakan kembali nanti.

Di SQL Server Management Studio, sambungkan ke instans Mesin Database SQL Server.
Luaskan Database.
Klik kanan database.
Pilih Tugas.
Pilih untuk Mengimpor Data atau Mengekspor Data:

Ini meluncurkan wizard:

Cuplikan layar Sambungkan ke sumber data Excel.

Untuk informasi lebih lanjut, baca artikel berikut:

Integration Services (SSIS)

Jika Anda terbiasa dengan SQL Server Integration Services (SSIS) dan tidak ingin menjalankan Wizard Impor dan Ekspor SQL Server, Anda bisa membuat paket SSIS yang menggunakan Sumber Excel dan Tujuan SQL Server dalam aliran data sebagai gantinya.

Untuk informasi lebih lanjut, baca artikel berikut:

Untuk mulai mempelajari cara membuat paket SSIS, lihat tutorial Cara Membuat Paket ETL.

Cuplikan layar Komponen dalam aliran data.

OPENROWSET dan server tertaut

Penting

Di Azure SQL Database, Anda tidak dapat mengimpor langsung dari Excel. Anda harus terlebih dahulu mengekspor data ke file teks (CSV).

Penyedia ACE (sebelumnya penyedia Jet) yang tersambung ke sumber data Excel ditujukan untuk penggunaan sisi klien interaktif. Jika Anda menggunakan penyedia ACE di SQL Server, terutama dalam proses atau proses otomatis yang berjalan secara paralel, Anda mungkin melihat hasil yang tidak terduga.

Kueri terdistribusi

Impor data langsung ke SQL Server dari file Excel dengan menggunakan Transact-SQL OPENROWSET atau OPENDATASOURCE fungsi. Penggunaan ini disebut kueri terdistribusi.

Penting

Di Azure SQL Database, Anda tidak dapat mengimpor langsung dari Excel. Anda harus terlebih dahulu mengekspor data ke file teks (CSV).

Sebelum Anda bisa menjalankan kueri terdistribusi, Anda harus mengaktifkan Ad Hoc Distributed Queries opsi konfigurasi server, seperti yang diperlihatkan dalam contoh berikut. Untuk informasi selengkapnya, lihat Konfigurasi server: Kueri Terdistribusi Ad Hoc.

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Sampel kode berikut menggunakan OPENROWSET untuk mengimpor data dari lembar kerja Excel Sheet1 ke dalam tabel database baru.

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]);
GO

Berikut adalah contoh yang sama dengan OPENDATASOURCE.

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0')...[Sheet1$];
GO

Untuk menambahkan data yang diimpor ke tabel yang sudah ada alih-alih membuat tabel baru, gunakan INSERT INTO ... SELECT ... FROM ... sintaks alih-alih SELECT ... INTO ... FROM ... sintaks yang digunakan dalam contoh sebelumnya.

Untuk mengkueri data Excel tanpa mengimpornya, cukup gunakan sintaks standar SELECT ... FROM ... .

Untuk informasi selengkapnya tentang kueri terdistribusi, lihat artikel berikut ini:

¹ Kueri terdistribusi masih didukung di SQL Server, tetapi dokumentasi untuk fitur ini tidak diperbarui.

Server yang ditautkan

Anda juga dapat mengonfigurasi koneksi persisten dari SQL Server ke file Excel sebagai server tertaut. Contoh berikut mengimpor data dari Data lembar kerja di server EXCELLINK tertaut Excel yang sudah ada ke dalam tabel database SQL Server baru bernama Data_ls.

USE ImportFromExcel;
GO
SELECT * INTO Data_ls FROM EXCELLINK...[Data$];
GO

Anda dapat membuat server tertaut dari SQL Server Management Studio (SSMS), atau dengan menjalankan prosedur sp_addlinkedservertersimpan sistem , seperti yang ditunjukkan dalam contoh berikut.

DECLARE @RC INT;
DECLARE @server NVARCHAR(128);
DECLARE @srvproduct NVARCHAR(128);
DECLARE @provider NVARCHAR(128);
DECLARE @datasrc NVARCHAR(4000);
DECLARE @location NVARCHAR(4000);
DECLARE @provstr NVARCHAR(4000);
DECLARE @catalog NVARCHAR(128);

-- Set parameter values
SET @server = 'EXCELLINK';
SET @srvproduct = 'Excel';
SET @provider = 'Microsoft.ACE.OLEDB.12.0';
SET @datasrc = 'C:\Temp\Data.xlsx';
SET @provstr = 'Excel 12.0';

EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server,
    @srvproduct,
    @provider,
    @datasrc,
    @location,
    @provstr,
    @catalog;

Untuk informasi selengkapnya tentang server tertaut, lihat artikel berikut ini:

Untuk contoh dan info selengkapnya tentang server tertaut dan kueri terdistribusi, lihat artikel berikut ini:

Cara menggunakan Excel dengan server tertaut SQL Server dan kueri terdistribusi

Prasyarat

Untuk menggunakan metode lain yang dijelaskan di halaman ini ( BULK INSERT pernyataan, alat bcp , atau Azure Data Factory), pertama-tama Anda harus mengekspor data Excel Anda ke file teks.

Menyimpan data Excel sebagai teks

Di Excel, pilih File | Simpan Sebagai lalu pilih Teks (Dibatasi tab) (*.txt) atau CSV (Dibatasi Koma) (*.csv) sebagai jenis file tujuan.

Jika Anda ingin mengekspor beberapa lembar kerja dari buku kerja, pilih setiap lembar, lalu ulangi prosedur ini. Perintah Simpan sebagai hanya mengekspor lembar aktif.

Tip

Untuk hasil terbaik dengan alat impor data, simpan lembar yang hanya berisi header kolom dan baris data. Jika data yang disimpan berisi judul halaman, baris kosong, catatan, dan sebagainya, Anda mungkin melihat hasil yang tidak terduga nanti saat mengimpor data.

Wizard Impor File Datar

Impor data yang disimpan sebagai file teks dengan menelusuri halaman Wizard Impor File Datar.

Seperti yang dijelaskan sebelumnya di bagian Prasyarat , Anda harus mengekspor data Excel sebagai teks sebelum Anda bisa menggunakan Wizard Impor File Datar untuk mengimpornya.

Untuk informasi selengkapnya tentang Wizard Impor File Datar, lihat Mengimpor File Datar ke Wizard SQL.

Perintah SISIPKAN MASSAL

BULK INSERT adalah perintah Transact-SQL yang dapat Anda jalankan dari SQL Server Management Studio. Contoh berikut memuat data dari file yang Data.csv dibatasi koma ke dalam tabel database yang sudah ada.

Seperti yang dijelaskan sebelumnya di bagian Prasyarat , Anda harus mengekspor data Excel anda sebagai teks sebelum bisa anda gunakan BULK INSERT untuk mengimpornya. BULK INSERT tidak dapat membaca file Excel secara langsung. BULK INSERT Dengan perintah , Anda dapat mengimpor file CSV yang disimpan secara lokal atau di penyimpanan Azure Blob.

USE ImportFromExcel;
GO
BULK INSERT Data_bi FROM 'C:\Temp\data.csv'
   WITH (
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
);
GO

Untuk informasi dan contoh selengkapnya untuk SQL Server dan Azure SQL Database, lihat artikel berikut ini:

Alat penyalinan massal (bcp)

Alat bcp dijalankan dari prompt perintah. Contoh berikut memuat data dari file yang Data.csv dibatasi koma ke dalam tabel database yang Data_bcp ada.

Seperti yang dijelaskan sebelumnya di bagian Prasyarat , Anda harus mengekspor data Excel sebagai teks sebelum Anda bisa menggunakan bcp untuk mengimpornya. Alat bcp tidak dapat membaca file Excel secara langsung. Gunakan untuk mengimpor ke SQL Server atau SQL Database dari file pengujian (CSV) yang disimpan ke penyimpanan lokal.

Penting

Untuk file teks (CSV) yang disimpan di penyimpanan Azure Blob, gunakan BULK INSERT atau OPENROWSET. Misalnya, lihat Menggunakan BULK INSERT atau OPENROWSET(BULK...) untuk mengimpor data ke SQL Server.

bcp.exe ImportFromExcel..Data_bcp in "C:\Temp\data.csv" -T -c -t ,

Untuk informasi selengkapnya tentang bcp, lihat artikel berikut ini:

Wizard Salin (ADF)

Impor data yang disimpan sebagai file teks dengan menelusuri halaman Wizard Salin Azure Data Factory (ADF).

Seperti yang dijelaskan sebelumnya di bagian Prasyarat , Anda harus mengekspor data Excel sebagai teks sebelum Anda bisa menggunakan Azure Data Factory untuk mengimpornya. Data Factory tidak dapat membaca file Excel secara langsung.

Untuk informasi selengkapnya tentang Wizard Salin, lihat artikel berikut ini:

Wizard Salin Data Factory
Tutorial: Membuat alur dengan Aktivitas Salin menggunakan Wizard Salin Data Factory.

Azure Data Factory

Jika Anda terbiasa dengan Azure Data Factory dan tidak ingin menjalankan Wizard Salin, buat alur dengan aktivitas Salin yang menyalin dari file teks ke SQL Server atau ke Azure SQL Database.

Untuk informasi selengkapnya tentang menggunakan sumber dan sink Data Factory ini, lihat artikel berikut ini:

Untuk mulai mempelajari cara menyalin data dengan pabrik data Azure, lihat artikel berikut ini:

Kesalahan umum

Microsoft.ACE.OLEDB.12.0" belum terdaftar

Kesalahan ini terjadi karena penyedia OLEDB tidak diinstal. Instal dari Microsoft Access Database Engine 2016 Redistributable. Pastikan untuk menginstal versi 64-bit jika Windows dan SQL Server keduanya adalah 64-bit.

Kesalahan lengkapnya adalah:

Msg 7403, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

Tidak dapat membuat instans penyedia OLE DB "Microsoft.ACE.OLEDB.12.0" untuk server tertaut "(null)"

Kesalahan ini menunjukkan bahwa Microsoft OLEDB tidak dikonfigurasi dengan benar. Untuk mengatasi masalah ini, jalankan kode Transact-SQL berikut:

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1;
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1;

Kesalahan lengkapnya adalah:

Msg 7302, Level 16, State 1, Line 3
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Penyedia OLE DB 32-bit "Microsoft.ACE.OLEDB.12.0" tidak dapat dimuat dalam proses pada SQL Server 64-bit

Kesalahan ini terjadi ketika versi 32-bit penyedia OLD DB diinstal dengan SQL Server 64-bit. Untuk mengatasi masalah ini, hapus instalan versi 32-bit dan instal penyedia OLE DB versi 64-bit sebagai gantinya.

Kesalahan lengkapnya adalah:

Msg 7438, Level 16, State 1, Line 3
The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.

Penyedia OLE DB "Microsoft.ACE.OLEDB.12.0" untuk server tertaut "(null)" melaporkan kesalahan

Kesalahan ini biasanya menunjukkan masalah izin antara proses SQL Server dan file. Pastikan bahwa akun yang menjalankan layanan SQL Server memiliki izin akses penuh ke file. Sebaiknya jangan mencoba mengimpor file dari desktop.

Kesalahan lengkapnya adalah:

Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Tidak dapat menginisialisasi objek sumber data penyedia OLE DB "Microsoft.ACE.OLEDB.12.0" untuk server tertaut "(null)"

Kesalahan lengkapnya adalah:

Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Bagikan melalui

Mengimpor data dari Excel ke SQL Server atau Azure SQL Database

Daftar metode

Wizard Impor dan Ekspor

Integration Services (SSIS)

OPENROWSET dan server tertaut

Kueri terdistribusi

Server yang ditautkan

Prasyarat

Menyimpan data Excel sebagai teks

Wizard Impor File Datar

Perintah SISIPKAN MASSAL

Alat penyalinan massal (bcp)

Wizard Salin (ADF)

Azure Data Factory

Kesalahan umum

Microsoft.ACE.OLEDB.12.0" belum terdaftar

Tidak dapat membuat instans penyedia OLE DB "Microsoft.ACE.OLEDB.12.0" untuk server tertaut "(null)"

Penyedia OLE DB 32-bit "Microsoft.ACE.OLEDB.12.0" tidak dapat dimuat dalam proses pada SQL Server 64-bit

Penyedia OLE DB "Microsoft.ACE.OLEDB.12.0" untuk server tertaut "(null)" melaporkan kesalahan

Tidak dapat menginisialisasi objek sumber data penyedia OLE DB "Microsoft.ACE.OLEDB.12.0" untuk server tertaut "(null)"

Saran dan Komentar

Sumber Daya Tambahan:

Bagikan melalui

Mengimpor data dari Excel ke SQL Server atau Azure SQL Database

Daftar metode

Wizard Impor dan Ekspor

Integration Services (SSIS)

OPENROWSET dan server tertaut

Kueri terdistribusi

Server yang ditautkan

Prasyarat

Menyimpan data Excel sebagai teks

Wizard Impor File Datar

Perintah SISIPKAN MASSAL

Alat penyalinan massal (bcp)

Wizard Salin (ADF)

Azure Data Factory

Kesalahan umum

Microsoft.ACE.OLEDB.12.0" belum terdaftar

Tidak dapat membuat instans penyedia OLE DB "Microsoft.ACE.OLEDB.12.0" untuk server tertaut "(null)"

Penyedia OLE DB 32-bit "Microsoft.ACE.OLEDB.12.0" tidak dapat dimuat dalam proses pada SQL Server 64-bit

Penyedia OLE DB "Microsoft.ACE.OLEDB.12.0" untuk server tertaut "(null)" melaporkan kesalahan

Tidak dapat menginisialisasi objek sumber data penyedia OLE DB "Microsoft.ACE.OLEDB.12.0" untuk server tertaut "(null)"

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan: