Mengimpor data dari Excel ke SQL Server atau Azure SQL Database

Berlaku untuk:SQL ServerAzure 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 mempelajari selengkapnya tentang solusi yang menarik minat Anda, ikuti tautan yang disediakan.

Daftar metode

Ada beberapa cara untuk mengimpor data dari Excel. Anda mungkin 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 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)
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.

Penting

Untuk mempelajari 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.

1. Di SQL Server Management Studio, sambungkan ke instans Mesin Database SQL Server.

2. Luaskan Database.

3. Klik kanan database.

4. Pilih Tugas.

5. Pilih untuk Mengimpor Data atau Mengekspor Data:

   

Ini meluncurkan wizard:

Untuk mempelajari lebih lanjut, tinjau:

• Memulai Wizard Impor dan Ekspor SQL Server
• Mulai menggunakan contoh sederhana Panduan Impor dan Ekspor ini

Integration Services (SSIS)

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

Untuk mempelajari lebih lanjut, tinjau:

• Sumber Excel
• Tujuan SQL Server

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

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

Catatan

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 Opsi 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 (Kueri terdistribusi masih didukung di SQL Server 2019 (15.x), tetapi dokumentasi untuk fitur ini belum diperbarui.)
• OPENROWSET
• Lihat OPENDATASOURCE

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:

• Buat Server yang Ditautkan
• OPENQUERY

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 - Simpan data Excel sebagai teks

Untuk menggunakan metode lain yang dijelaskan di halaman ini - pernyataan BULK INSERT, alat BCP, atau Azure Data Factory - pertama-tama Anda harus mengekspor data Excel Anda ke file 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 anda bisa menggunakan BULK INSERT untuk mengimpornya. SISIPAN MASSAL tidak dapat membaca file Excel secara langsung. Dengan perintah INSERT MASSAL, 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 SQL Database, lihat artikel berikut ini:

• Impor Data Massal dengan Menggunakan SISIPAN MASSAL atau OPENROWSET(MASSAL...)
• SISIPAN MASSAL

Alat BCP

BCP adalah program yang Anda jalankan 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. 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. Untuk contoh, lihat Contoh.

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

Untuk informasi selengkapnya tentang BCP, lihat artikel berikut ini:

• Mengimpor dan Mengekspor Data Massal dengan Menggunakan Utilitas bcp
• Utilitas bcp
• Menyiapkan Data untuk Ekspor atau Impor Massal

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.

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 menggunakan sumber dan sink Data Factory ini, lihat artikel berikut ini:

• Sistem file
• SQL Server
• Azure SQL Database

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

• Memindahkan data dengan menggunakan Aktivitas Salin
• Tutorial: Membuat alur dengan Aktivitas Salin menggunakan portal Azure

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

Ini menunjukkan bahwa Microsoft OLEDB belum dikonfigurasi dengan benar. Jalankan kode Transact-SQL berikut untuk mengatasi hal ini:

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

Ini terjadi ketika versi 32-bit dari 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.

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

Kedua 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.

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)".

Langkah berikutnya

• Mulai menggunakan contoh sederhana Panduan Impor dan Ekspor ini
• Mengimpor data dari Excel atau mengekspor data ke Excel dengan SQL Server Integration Services (SSIS)
• Utilitas bcp
• Memindahkan data dengan menggunakan Aktivitas Salin