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 informasi selengkapnya tentang solusi yang menarik minat Anda, ikuti tautan yang disediakan.

Daftar metode

Ada beberapa cara untuk mengimpor data dari Excel. Instal versi terbaru 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 Flat	Impor dan Ekspor Wizard SQL Server
BULK INSERT statement	SQL Server Integration Services (SSIS)
Alat penyalinan dalam jumlah besar (bcp)	Fungsi OPENROWSET
Panduan Penyalinan (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.

Panduan 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 pada database.

4. Pilih Tugas.

5. Pilih untuk Mengimpor Data atau Mengekspor Data:

   

Ini meluncurkan wizard:

Untuk informasi lebih lanjut, baca artikel berikut:

• Mulai 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, 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:

• Sumber Excel
• Tujuan pada SQL Server

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

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

Contoh berikut menggunakan penyedia JET. Penyedia ACE yang disertakan dengan Office yang tersambung ke sumber data Excel ditujukan untuk penggunaan sisi klien interaktif, yang dapat menyebabkan hasil yang tidak terduga saat digunakan secara non-interaktif.

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.

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

EXECUTE 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.JET.OLEDB.4.0',
    'Excel 8.0; Database=C:\Temp\Data.xls', [Sheet1$]);
GO

Berikut adalah contoh yang sama dengan OPENDATASOURCE.

USE ImportFromExcel;
GO

SELECT * INTO Data_dq
FROM OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
    'Data Source=C:\Temp\Data.xls;Extended Properties=Excel 8.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¹
• OPENROWSET (T-SQL)
• OPENDATASOURCE (Transact-SQL)

¹ 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 AS INT;
DECLARE @server AS NVARCHAR (128);
DECLARE @srvproduct AS NVARCHAR (128);
DECLARE @provider AS NVARCHAR (128);
DECLARE @datasrc AS NVARCHAR (4000);
DECLARE @location AS NVARCHAR (4000);
DECLARE @provstr AS NVARCHAR (4000);
DECLARE @catalog AS NVARCHAR (128);

-- Set parameter values
SET @server = 'EXCELLINK';
SET @srvproduct = 'Excel';
SET @provider = 'Microsoft.JET.OLEDB.4.0';
SET @datasrc = 'C:\Temp\Data.xls';
SET @provstr = 'Excel 8.0';

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

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

• Membuat server tertaut (SQL Server Database Engine)
• OPENQUERY (Transact-SQL)

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.

Kiat

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 Flat File

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 Wizard Impor File Datar ke SQL.

Perintah IMPOR 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:

• Gunakan BULK INSERT atau OPENROWSET(BULK...) untuk mengimpor data ke SQL Server
• SISIPAN MASSAL (Transact-SQL)

Alat salin massal (bcp)

Alat bcp dijalankan dari command prompt. 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:

• Mengimpor dan mengekspor data massal menggunakan bcp (SQL Server)
• Utilitas bcp
• Menyiapkan data untuk ekspor atau impor massal

Panduan Penyalinan (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:

• Panduan Salin Data Factory
• Tutorial: Membuat pipeline dengan Copy Activity menggunakan Copy Wizard 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 berkas
• 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

Konten terkait

• 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