Biasanya orang lebih banyak menyimpan datanya dalam aplikasi seperti Microsoft Excel, karena memang banyak orang yang telah terbiasa dengan aplikasi tersebut. Masalah akan timbul ketika data yang sudah terlanjur diketikkan tersebut ingin dimasukkan langsung ke database.. nggak ada kerjaan banget, kalau harus mengetik ulang ke database apalagi jika datanya kelewat banyak..
Bagi yang mengerti database memang ada cara yang lebih mudah yaitu dengan menggunakan fasilitas Import and Export Data atau dengan pernyataan SQL. Untuk programmer yang ingin menambahkan fasilitas import data dalam aplikasi yang dikembangkannya, agar setiap user bisa memasukkan sendiri data dari file teks atau Excel, tentu saja pilihannya adalah pernyataan SQL.
Saya mengasumsikan kamu memakai database SQL Server 2000, yang biasanya pada saat installasi telah membuatkan beberapa contoh database. Dalam artikel ini saya memakai database Northwind, dan data file akan dimasukkan dalam tabel Customers.. Sebelumnya download dulu file excel Sample Data Excel, sedang contoh file teks bisa diperoleh dengan mengkonversi file excel tersebut menjadi file Text (tab delimited)..
Cara mengkonversinya, buka file excel tersebut kemudian klik menu File -> Save as…, dan ikut langkahnya seperti gambar berikut ini
Pada saat kamu menekan tombol Save, akan muncul dua pesan.. tekan tombol OK pada pesan pertama dan tekan tombol Yes pada pesan kedua. Seperti gambar di atas, Saya menyimpan file text tadi di path ‘F:\Private\project for blog\’.. Seharusnya sekarang di folder tersebut ada file sampledata.txt. Selanjutnya tutup kembali aplikasi Microsoft Excel, jika muncul pesan konfirmasi untuk menyimpan, sebaiknya tekan tombol No agar keadaan file excel tetap seperti sedia kala..
Pernyataan SQL untuk memasukkan data dari file tergantung pada jenis file-nya. Dalam artikel ini saya menggunakan dua macam pernyataan yaitu BULK INSERT dan INSERT INTO..
1. Memasukkan data dari file teks dengan pernyataan BULK INSERT
Secara ringkas pernyataan SQL untuk memasukkan data dari file sampledata.txt seperti gambar di bawah ini
Saya memakai syntax tersebut dalam database SQL Server 2000, untuk database lain mungkin syntax-nya berbeda. Pemilik (owner) tabel biasanya dbo, walaupun tidak tertutup kemungkinan ada tabel yang owner-nya adalah user yang membuat tabel tersebut. Variabel DATAFILETYPE dipilih char karena file data terdiri atas karakter, variabel ini mempunyai empat macam nilai
| Nilai | Keterangan |
| char | Meng-copy data dari file yang terdiri atas karakter |
| native | Meng-copy dengan menggunakan tipe data database |
| widechar | Meng-copy data dari file yang terdiri atas karakter Unicode |
| widenative | Sama seperti native, kecuali untuk data bertipe char, varchar dan teks |
Variabel FIELDTERMINATOR, menentukan pemisah antar nilai kolom dari data dalam file. Biasanya file teks hasil konversi dari file excel dipisahkan dengan tab, untuk membuktikannya buka file sampledata.txt dengan Notepad.. Dan karakter pengganti tab dalam pernyataan SQL adalah \t. Jika pemisah nilai kolom dari data seperti di bawah ini maka nilai variabel FIELDTERMINATOR adalah ; (karakter titik koma)
CustomerID;CompanyName;ContactName;ContactTitle;Address SPKOM;Spirit Komunika;Nugi;Sales Manager;Jl Dr. Muwardi Raya No.29-30 Jakarta MAPRI;PT Malta Printindo;Angoro;Sales Manager;Jl Kedoya Raya No.2 A Kedoya Selatan GRPRI;PT Gramedia Printing Group;Bayu;Sales Manager;Jl Palmerah Selatan 22-28 Jakarta
Tabel berikut ini menjelaskan daftar karakter pemisah kolom atau baris dalam pernyataan SQL
| Nilai | Keterangan |
| Tab | \t |
| Newline character | \n |
| Carriage return | \r |
| Backslash | \\ |
| Tanpa pemisah | \0 |
| Karakter tertentu | (*, A, t, l, dst..) |
| Gabungan dari beberapa karakter | (**\t**, end, !!!!!!!!!!, \t–\n, dst..) |
Variabel FIRSTROW, menentukan baris dari data dalam file yang nantinya akan di-copy sebagai baris pertama, nilainya tergantung pada file data. Dalam file excel (sampledata.xls) maupun file teks hasil konversi (sampledata.txt), baris pertama menunjukkan judul kolom dan yang dianggap sebagai data adalah baris kedua maka nilai variabel FIRSTROW sama dengan 2.
Variabel ROWTERMINATOR, menentukan pemisah antar baris data. Batas akhir dari baris data dalam file sampledata.txt adalah adanya baris data yang baru (newline). Dalam pernyataan SQL, lihat tabel di atas, simbol karakter newline ditulis \n.
Jika pada saat kamu mengeksekusi pernyataan di atas, baik dalam aplikasi eksekutor SQL (misalnya SQL Query Analyzer milik SQL Server 2000) atau dalam aplikasi yang dibuat melalui bahasa pemrograman visual (misalnya Borland Delphi), muncul pesan ‘Bulk Insert: Unexpected end-of-file (EOF) encountered in data file‘ artinya bagian akhir dari file data banyak mengandung baris-baris kosong, lihat gambar di bawah
Jika pada setiap baris dalam file data terdapat kolom kosong, lihat gambar di bawah, maka nilai yang dimasukkan dalam field/kolom terakhir database ada karakter yang tidak dikenal, biasanya kotak.
Kamu bisa menghapusnya langsung dalam file teks.. Untuk baris, tekan tombol keyboard Del sampai kursor keyboard berada tepat di bawah baris terakhir. Sedang untuk kolom, tekan tombol keyboard Del sampai kursor keyboard berada tepat di samping kolom terakhir dari setiap baris.. Tapi jika datanya mempunyai banyak baris, cara ini tentu saja tidak efisien, bisa-bisa malah menghapus datanya.. Selain cara itu kamu bisa mengeditnya melalui file sumbernya, file excel (sampledata.xls). . ikuti langkahnya seperti gambar di bawah ini
Lakukan hal yang sama pada kolom terakhir, setelah itu konversi lagi file excel tersebut menjadi file teks seperti langkah-langkah di atas..
2. Memasukkan data dari file Excel dengan pernyataan INSERT INTO
Syntax pernyataan SQL untuk memasukkan data dari file Excel bisa menggunakan pernyataan INSERT INTO, perhatikan gambar di bawah ini
Pernyataan INSERT INTO fungsinya jelas untuk memasukkan data ke tabel database. Metode OPENROWSET untuk mengakses data remote dari data source OLE DB, berikut syntax lengkapnya
OPENROWSET ( 'provider_name',
{ 'datasource' ; 'user_id' ; 'password' | 'provider_string' },
{ [ catalog. ] [ schema. ] object | 'query' } )
Variabel provider_name, menunjukan nama provider OLE DB yang dikenal dalam registry komputer. Beberapa provider yang biasa dikenal untuk SQL Server 2000 :
- SQLOLEDB, provider Microsoft OLE DB untuk SQL Server
- MSDASQL, provider OLE DB untuk ODBC SQL Server
- Microsoft.Jet.OLEDB.4.0, provider Microsoft OLE DB yang umum digunakan untuk produk-produk microsoft
Variabel yang kedua kamu bisa memilih kombinasi datasource ; user_id ; password atau provider_string tergantung pada sumber datanya. Variabel datasource bisa diisi dengan nama database, user_id dan password fungsinya jelas user dan password-nya. Dalam contoh seperti gambar di atas, saya memilih variabel provider_string karena sumber datanya adalah file Excel.
Sedang varibel yang ketiga kamu juga bisa memilih kombinasi [ catalog. ] [ schema. ] object atau pernyataan Query. Contoh penulisan kombinasi [ catalog. ] [ schema. ] object, seperti syntax di bawah ini
Northwind.dbo.Customers
Karena sumber datanya dari file maka variabel ketiga lebih cocok dengan Query.. Kamu bisa membatasi data dalam sheet file Excel dengan menyertakan nama kolom setelah nama sheet. Penulisan Query di bawah ini hanya akan memasukkan data dari kolom A sampai kolom I dan hanya sampai pada baris ke-3
'SELECT * FROM [Sheet1$A1:I3]'
Artinya kolom Phone dan Fax tidak dimasukkan dalam database dan hanya dua record yang di-copy ke database.
Jika pada saat kamu mengeksekusi pernyataan di atas, muncul pesan ‘Cannot insert the value NULL into column ‘CustomerID’, table ‘Northwind.dbo.Customers’; column does not allow nulls. INSERT fails‘ artinya bagian akhir dari file data banyak mengandung baris-baris kosong, untuk memastikannya kamu bisa melihat isi filenya terlebih dahulu dengan pernyataan berikut
Kamu bisa mengatasinya dengan mengedit file sumbernya, file Excel (sampledata.xls). . ikuti langkahnya seperti gambar di bawah ini
Atau dengan mengubah pernyataan SQL-nya menjadi
INSERT INTO Northwind.dbo.[Customers] SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=F:\Private\project for blog\sampledata.xls', 'SELECT * FROM [Sheet1$A1:K4]')
Kedua Syntax pernyataan SQL di atas (BULK INSERT dan INSERT INTO) bisa di-kode-kan dalam bahasa pemrograman Borland Delphi, download Copy Data Source Code ver.1.1 kemudian ekstrak di komputer kamu dan buka project Copy.dpr.. Dalam source code tersebut saya menggunakan beberapa komponen yang merupakan komponen tambahan, TPDJButton dan TCheckDBGrid. Jika kamu belum punya komponen tersebut download, TPDJButton Component dan TCheckDBGrid Component kemudian instal dalam program Delphi kamu.. perhatikan kode pada event OnShow dari form fmCopyFile
procedure TfmCopyFile.FormShow(Sender: TObject);
var
sConn: String;
begin
sConn:= 'Provider=SQLOLEDB.1;'+
'Integrated Security=SSPI;'+
'Persist Security Info=False;'+
'Initial Catalog=Northwind;'+
'Data Source=SIS03S';
adocCopyFile.ConnectionString:= sConn;
adocCopyFile.Connected:= True;
if adocCopyFile.Connected then
qCustomers.Open;
end;
Sebelum menjalankannya edit isi variabel sConn, terutama pada karakter Data Source=SIS03S dengan nama server database kamu (saya menggunakan server database bernama SIS03S).
Nuwun..
Daftar download untuk artikel ini:
1. Sample Data Excel - 15.5 kB
2. Copy Data Source Code ver.1.1 - 5.31 kB
